Resource consuming query

Question

I have found below CPU consuming query with a query cost of 1540. I have tried creating appropriate indexes which reduced the query cost to 1234 but still, the query is expensive.

Index Created :

CREATE NONCLUSTERED drop  INDEX [IX-AAD_RESP_NOW-AAD_DATE]
ON [dbo].[MST_AR_AS_ON_DATE] ([AAD_RESP_NOW],[AAD_DATE])
INCLUDE ([AAD_LOC_CODE],[AAD_00TO30],[AAD_31TO60],[AAD_61TO90],[AAD_91TO120],[AAD_121TO150],[AAD_151TO180],[AAD_180PLUS])
GO

Query:

SELECT CAST(YEAR(A.AAD_DATE) AS VARCHAR(4))+' - '+RIGHT('00'+CAST(MONTH(A.AAD_DATE) AS VARCHAR(2)),2), 
'Insurance', SUM(A.AAD_00TO30+A.AAD_31TO60+A.AAD_61TO90+A.AAD_91TO120+A.AAD_121TO150+A.AAD_151TO180+A.AAD_180PLUS) 
FROM MST_AR_AS_ON_DATE A 
WHERE A.AAD_RESP_NOW <= 2 AND A.AAD_DATE >= (SELECT MAX(B.AAD_DATE) FROM MST_AR_AS_ON_DATE B WHERE MONTH(B.AAD_DATE) = MONTH(A.AAD_DATE) 
AND YEAR(B.AAD_DATE) = YEAR(A.AAD_DATE)) AND A.AAD_DATE >= '2019-01-01' AND A.AAD_DATE <= '2022-05-18 23:59:59' AND (AAD_RENDOC_ID = -1 OR -1 = -1 ) 
AND (AAD_LOC_CODE LIKE '%%' OR '' = 'ALL') GROUP 
BY A.AAD_DATE 
UNION ALL 
SELECT CAST(YEAR(AAD_DATE) AS VARCHAR(4))+' - '+RIGHT('00'+CAST(MONTH(AAD_DATE) AS VARCHAR(2)),2),
'Patient', SUM(A.AAD_00TO30+A.AAD_31TO60+A.AAD_61TO90+A.AAD_91TO120+A.AAD_121TO150+A.AAD_151TO180+A.AAD_180PLUS) 
FROM MST_AR_AS_ON_DATE A 
WHERE A.AAD_RESP_NOW = 4 AND A.AAD_DATE >= (SELECT MAX(B.AAD_DATE) FROM MST_AR_AS_ON_DATE B WHERE MONTH(B.AAD_DATE) = MONTH(A.AAD_DATE) 
AND YEAR(B.AAD_DATE) = YEAR(A.AAD_DATE)) AND A.AAD_DATE >= '2019-01-01' AND A.AAD_DATE <= '2022-05-18 23:59:59' AND (AAD_RENDOC_ID = -1 OR -1 = -1 ) 
AND (AAD_LOC_CODE LIKE '%%' OR '' = 'ALL') 
GROUP BY A.AAD_DATE 
ORDER BY 1,2 DESC

I am not a developer hence I am not able to optimise the query on query level. I will appreciate if anyone can guide me what changes are required to fine-tune this query.

Thank you in advance.

Please find estimated execution plan below: https://www.brentozar.com/pastetheplan/?id=r1xPQCKPq

Also, Actual execution plan is uploaded in below link. https://www.brentozar.com/pastetheplan/?id=H1JG3q6_c

score 1 · Accepted Answer · answered Jun 08 '22 at 08:12

First things that jump out are the plan affecting CONVERTs: CONVERT(varchar(4),datepart(year,[A].[AAD_DATE]),0)

Two of them, right in the execution plan letting you know one place to get started. Dates and times should be treated like what they are, dates and times. Converting them down to strings for comparisons inevitably causes performance issues. You can also see all the places where you're doing YEAR to YEAR comparisons, same kinds of issues.

Also, this: AAD_LOC_CODE LIKE '%%' OR '' = 'ALL'

Again, lots and lots of issues. You're retrieving all values for AAD_LOC_CODE with the LIKE and then the OR is weird. When will an empty string, '', ever equal anything, let alone, 'ALL'? That's just odd code.

All of this is preventing good index use. While you have filters in place, you're getting all scans on every index or table. Even though you're filtering down to only a few hundred rows, millions are being scanned.

Strong recommendation. Tear this down into it's most component pieces and then rebuild it one step at a time. You've got lots and lots of detritus from, guessing, a series of developers who don't quite know SQL all that well.

Resource consuming query

1 Answers1