SQL Server Performance

Need help with query performance

Discussion in 'T-SQL Performance Tuning for Developers' started by crassy303, Jun 21, 2007.

  1. crassy303 New Member

    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
  2. Adriaan New Member

    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.
  3. Adriaan New Member

    ... and this ...

    AND '0008' = substring(C.ZZZ,1,4)

    ... should really be standard SQL syntax:

    AND C.ZZZ LIKE '0008%'
  4. crassy303 New Member

    <blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><i>Originally posted by Adriaan</i><br /><br />... and this ...<br /><br />AND '0008' = substring(C.ZZZ,1,4)<br /><br />... should really be standard SQL syntax:<br /><br />AND C.ZZZ LIKE '0008%'<br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br /><br />And this just saved a major company many millions of dollars and<br />secured my bonus ... I'm not kidding. <img src='/community/emoticons/emotion-2.gif' alt=':D' /><br /><br />The issue here was that the substring expression made<br />it impossible for the optimizer to use the index, so when no<br />matches were found the server had to do a whole scan. This query runs in a loop...<br /><br />THANK YOU SO MUCH Adriaan!

Share This Page