March 24, 2010 at 6:22 am
Dear Firends,
I need some help with tuning the below query:
SELECT
assetdesc,
runname,
runstatus,
signedoffsignedOff,
SUM(totalcount) totalcount,
SUM(passcount + warncount + failcount) complcount,
SUM(passcount) passcount,
SUM(warncount) warncount,
SUM(failcount) failcount,
SUM(retestcount) retestcount,
SUM(incompletecount) missedcount,
SUM(cancelcount) cancelcount,
SUM(manualcount)manualcount,
CASE WHEN (SUM(warncount)+SUM(failcount)+SUM(cancelcount)+SUM(incompletecount)) = 0 THEN
100
ELSE
100 - (CAST(SUM(warncount)+SUM(failcount)+SUM(cancelcount)+SUM(incompletecount) AS FLOAT) /SUM(totalcount))*100
END RFT,
runId,
SUM(voidcount)voidcount
FROM
(
SELECT
csa.checksheettitle,
csa.checksheetid,
1 totalcount,
CASE result WHEN 'PASS' THEN 1 ELSE 0 END passcount,
CASE result WHEN 'FAIL' THEN 1 ELSE 0 END failcount,
CASE result WHEN 'WARN' THEN 1 ELSE 0 END warncount,
CASE result WHEN 'INCOMPLETE' THEN 1 ELSE 0 END incompletecount,
CASE result WHEN 'CANCELLED' THEN 1 ELSE 0 END cancelcount,
CASE ISNULL(retestfrom,0) WHEN 0 THEN 0 ELSE 1 END retestcount,
CASE WHEN result IS NULL THEN 1 ELSE 0 END duecount,
CASE WHEN duetime = audittime THEN 1 ELSE 0 END manualCount,
pr.runname runname,
pra.assetDesc assetdesc,
prh.status runstatus,
pr.runId runId,
CASE result WHEN 'VOID' THEN 1 ELSE 0 END voidcount,
pr.signedoff
FROM
checksheetaudit csa
JOIN productionRun pr ON csa.productionrunid = pr.runid
JOIN productionRunHistory prh ON (pr.runid = prh.runid and prh.vto = '9999-01-01 00:00:00.000')
JOIN
(SELECT runid, min(assetDesc) assetDesc
FROM productionAudit
WHERE vFrom >= '2010-03-13 00:00:00' AND vfrom<=getdate() AND assetId IN
(SELECT DISTINCT assetId
FROM cellGroupCell cgc JOIN cellGroup cg ON cgc.cellGroupId = cg.cellGroupId
WHERE cg.cellGroupname LIKE '%')
AND runId IS NOT NULL
GROUP BY runId
UNION
SELECT runid, min(assetDesc) assetDesc
FROM productionActivityAudit
WHERE vFrom >= '2010-03-13 00:00:00' AND vfrom<=getdate() AND assetId IN
(SELECT DISTINCT assetId
FROM cellGroupCell cgc JOIN cellGroup cg ON cgc.cellGroupId = cg.cellGroupId
WHERE cg.cellGroupname LIKE '%')
AND runId IS NOT NULL
GROUP BY runId) AS pra ON pra.runId = csa.productionRunId
WHERE csa.audittime IS NOT NULL
) t1
GROUP BY assetdesc, runId, runName, runstatus, signedoff
ORDER BY assetdesc, runname
I am also attaching the execution plan. Please let me know if you need any more information.
Many thanks in advance.
March 24, 2010 at 6:38 am
Table and index definitions please
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 24, 2010 at 7:23 am
PLease find attached table and index definations.
March 25, 2010 at 7:06 am
Looking at it, you're getting almost nothing but clustered index scans. That means it's reading every single row on the table. Things in the queries that are causing this include:
CellGroupName LIKE '%'
That's a wild card search that can only scan the table. You might as well not include that in the WHERE clause because you're not going to see any difference between that and a query without a WHERE clause. You need to define what it is that you're looking for there.
When you are getting an index seek you're also getting a bookmark lookup. The seek against 'missing_index...' would eliminate the lookup if you added assetDesc as an include in that index.
ix_ProductionRunHistory_Asset2d_VToStatus is getting a scan with the value '9999 01 etc.' so I suspect that is probably not selective enough a value within that index. You might want to consider finding a different set of values to index on that table or you need to consider a different approach within the query.
I think you might also have some issues with statistics. pkCellGroupCell has 67000+ rows being scanned, but the estimate was for 1 row.
There's a lot more there, but that can get you started.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
March 25, 2010 at 9:53 am
Grant Fritchey (3/25/2010)
Looking at it, you're getting almost nothing but clustered index scans. That means it's reading every single row on the table. Things in the queries that are causing this include:
CellGroupName LIKE '%'That's a wild card search that can only scan the table. You might as well not include that in the WHERE clause because you're not going to see any difference between that and a query without a WHERE clause.
:sick: Haven't seen that for years :sick:
It's a daft way to exclude NULLs. Replace with CellGroupName IS NOT NULL, or discard completely if the column is defined as NOT NULL.
Yuk.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
March 25, 2010 at 10:49 am
Grant, You are a genius. The query has come down to 6 secs with your recommendations.
Thank you.
March 25, 2010 at 11:20 am
ss-457805 (3/25/2010)
Grant, You are a genius. The query has come down to 6 secs with your recommendations.Thank you.
I'm certainly not a genius. That was just some of the easy stuff that I spotted in the execution plan. I'm sure there's more that can be done. I'm glad some of it was helpful. What about the statistics? Did you update those?
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
March 25, 2010 at 4:46 pm
Updated the statistics for the tables Cellgroupcell, productionaudit. Added the column assetdesc to the indexes u mentioned.
March 26, 2010 at 8:12 am
ss-457805 (3/25/2010)
Grant, You are a genius. The query has come down to 6 secs with your recommendations.Thank you.
Oy vey - there goes Grant's ego!! 😀
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
March 26, 2010 at 8:15 am
TheSQLGuru (3/26/2010)
ss-457805 (3/25/2010)
Grant, You are a genius. The query has come down to 6 secs with your recommendations.Thank you.
Oy vey - there goes Grant's ego!! 😀
No, no. My ego only inflates when someone says Super-Genius. Just like my pal Wile. E.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply