CODEWITHSUNDEEP
sqlsql-servert-sql
Springray
Springray

Reputation: 99

Optimizing a SQL query on SQL Server

I have a SQL query on SQL Server that needs to be optimized. It takes almost 16 minutes to run it.

SELECT DISTINCT DeptNo,
                BrandDesc,
                MfgDesc,
                CountOfActiveSKUs =
                    COALESCE((SELECT COUNT(*)
                                FROM dbo.SKU k
                               WHERE k.Status = 'A'
                                 AND k.BrandDesc = s.BrandDesc
                                 AND k.MfgDesc = s.MfgDesc
                                 AND k.DeptNo = s.DeptNo),
                             0)
           FROM dbo.SKU s
       ORDER BY BrandDesc

I came up with the following, but it doesn't seems it grabs records that have CountOfActiveSKUs = 0.

SELECT DISTINCT DeptNo,
                BrandDesc,
                MfgDesc,
                CountOfActiveSKUs = COALESCE(Count(*), 0)
           FROM dbo.SKU
          WHERE Status = 'A'
       GROUP BY BrandDesc, MfgDesc, DeptNo
       ORDER BY BrandDesc

Any help is appreciated, Thank you!

Upvotes: 0

Views: 75

Answers (3)

Ram
Ram

Reputation: 514

Try this

SELECT s.DeptNo, s.BrandDesc, s.MfgDesc, count(k.Status) as CountOfActiveSKUs
FROM  dbo.SKU s LEFT OUTER JOIN
dbo.SKU k ON 
k.Status = 'A' AND k.BrandDesc = s.BrandDesc
AND k.MfgDesc = s.MfgDesc AND k.DeptNo = s.DeptNo
GROUP BY s.DeptNo, s.BrandDesc, s.MfgDesc
ORDER BY BrandDesc

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1271003

I would write this as:

SELECT DeptNo, BrandDesc, MfgDesc,
       SUM(CASE WHEN status = 'A' THEN 1 ELSE 0 END) OVER (PARTITION BY BrandDesc, MfgDesc, DeptNo) as CountOfActiveSKUs
FROM dbo.SKU s
ORDER BY BrandDesc;

I would leave the SELECT DISTINCT out, unless it is absolutely necessary. If it is necessary, then use an explicit GROUP BY:

SELECT DeptNo, BrandDesc, MfgDesc,
       SUM(CASE WHEN status = 'A' THEN 1 ELSE 0 END) as CountOfActiveSKUs
FROM dbo.SKU s
GROUP BY DeptNo, BrandDesc, MfgDesc
ORDER BY BrandDesc;

Upvotes: 3

F.Igor
F.Igor

Reputation: 4410

Don't filter the status, count the SKUs with status='A' using a conditional SUM.

SELECT DISTINCT DeptNo,
                BrandDesc,
                MfgDesc,
                SUM(CASE WHEN status = 'A' THEN 1 ELSE 0 END) as CountOfActiveSKUs
           FROM dbo.SKU
       GROUP BY BrandDesc, MfgDesc, DeptNo
       ORDER BY BrandDesc

Upvotes: 1

Related Questions