SQL Server Performance Forum – Threads Archive

Need help with query performance

Hello! I need some help with tuning the following query. I run it on a MS SQL 2000 server and it takes more then 5 seconds. Execution plan shows index scans and merge joins taking place. Any optimization tips are greatly appreciated! SELECT
SUM(A.ROW_TOTAL)
FROM A
INNER JOIN B ON A.ID = B.A_ID
INNER JOIN C ON B.C_GUID = C.GUID
INNER JOIN D ON A.ID = D.A_ID
WHERE
(datepart(mm, D.SOME_DATE)= datepart(mm, dateadd(mm,-1,’2007-06-21′)))
AND
(datepart(yyyy, D.SOME_DATE)= datepart(yyyy, dateadd(mm,-1,’2007-06-21′)))
AND (D.YYY = 1)
AND ‘0008’ = substring(C.ZZZ,1,4)
The tables are:
A: 3M rows, clustered index on ID (int)
B: 3M rows, clustered index on ID (int)
C: 200k rows, clustered index on GUID (like 0D7353C9-07AF-4A20-898F-00025860C13A)
D: 3M rows, clustered index on ID (int) Other columns:
D.SOME_DATE: datetime
D.YYY: int
C.ZZZ: varchar

5 seconds is not bad at all for filtering on a 3M row table. But perhaps the YYY column is highly selective? The main issue is that you’re applying some function on a column value, then filtering for the results. If there is an index for the column, then his index cannot be used (just because of the function). Your query even applies two functions on the SOME_DATE column. In the end, you’re just filterning for SOME_DATE to be in the previous calendar month for a given date. If you’re spelling out the dates, then why not simply write out the dates … If the SOME_DATE column has dates with only midnight for the time part, then you can use this:
(D.SOME_DATE BETWEEN ‘2007-05-01’ AND ‘2007-05-31’) If SOME_DATE has the time of the day as well, the simplest solution is this:
(D.SOME_DATE >= ‘2007-05-01’ AND D.SOME_DATE < ‘2007-06-01’) Another issue is having a clustered index on a GUID – that’s really not recommended. It’s a 200k table, so there’s no big impact, but still not recommended.

… and this … AND ‘0008’ = substring(C.ZZZ,1,4) … should really be standard SQL syntax: AND C.ZZZ LIKE ‘0008%’

<blockquote id="quote">quote:<hr height="1" noshade id="quote">Originally posted by Adriaan … and this … AND ‘0008’ = substring(C.ZZZ,1,4) … should really be standard SQL syntax: AND C.ZZZ LIKE ‘0008%' <hr height="1" noshade id="quote"></blockquote id="quote"> And this just saved a major company many millions of dollars and secured my bonus … I’m not kidding. <img src=’/community/emoticons/emotion-2.gif’ alt=’

‘ /> The issue here was that the substring expression made it impossible for the optimizer to use the index, so when no matches were found the server had to do a whole scan. This query runs in a loop… THANK YOU SO MUCH Adriaan!

]]>