SQL Server Performance

weird stored procedure performance

Discussion in 'T-SQL Performance Tuning for Developers' started by jeff, Jan 16, 2003.

  1. jeff New Member

    I've located a slow moving SPO and narrowed it down to the following.

    I've simplified it here for ease of reading (in actuality, the second contains is not a contains, but an openrowset on something else, but for the problem is still the same)

    Select a,b,c FROM table1 WHERE
    (date_retrieved BETWEEN '2003-01-01' AND '2003-01-15')
    AND
    (contains(title,'hello') OR contains(subject,'goodbye'))


    It runs slow, and i've done all the optimizing and tuning recommended, to no effect.

    The problem seems to be with the two "nested" blocks. Neither piece by itself takes more than 1 second or so to complete.

    The date retrieved for either range (>= 2003-01-01) or (<= 2003-01-15) runs ~ 1 second.

    The contains(title,'hello') ~ 1 second

    The contains(subject,'goodbye') ~ 1 second.

    However, together the query will take upwards of 40 seconds.

    If I take out either of the nested, it runs in about 3 seconds. So If I make my above query:

    Select a,b,c FROM table1 WHERE
    (date_retrieved >= '2003-01-01)
    AND
    (contains(title,'hello') OR contains(subject,'goodbye'))


    it runs fast.

    If I make it

    Select a,b,c FROM table1 WHERE
    (date_retrieved BETWEEN '2003-01-01' AND '2003-01-15')
    AND
    contains(title,'hello')
    AND
    contains(subject,'goodbye'))


    it runs fast.

    Basically the query is WHERE (a and b) and (c or d).

    that form causes slowness. Is it computing it to be ac * ab * bc * bd? Anyway to provide the same functionality but not make it be so expensive?

    Hope this makes sense.

    thanks, Jeff


  2. bradmcgehee New Member

    Without seeing the query plan, it is hard for me to make a recommendation. But the use of the OR clause can sometimes lead to indexes being ignored, and a table scan being performed instead. Does your query plan show any table or index scans? Also, have you compared the query plan of the full query (that runs slow) to the query plans of the partial queries to see the differences between them? If so, what are they?


    ------------------
    Brad M. McGehee
    Webmaster
    SQL-Server-Performance.Com
  3. jeff New Member

    For the slow one, here is the execution plan

    Path A:
    Remote Scan (15%) [contains b]
    Filter 0%
    Row count spool/lazy spool (0%)

    Path B:
    Remote Scan (0%) [contains a]
    Index spool/Eager spool (0%)

    Path C:
    Clustered index scan (83%) [date retrieved]

    C and B go to Nested Loops/Left Semi Join (1%) call this D

    A goes to D's output of Nested Loops (0%)

    they all go to a filter, and finally the select.

    -------
    The other method with the date_retrieved >= is the same as above, except path a goes from the remote scan to a Table Spool before going to the filter
    ------
    The last method with the contains being and'd provides a whole different execution path, but of course not the results I want.

    The following seems to work


    Select a,b,c FROM table1 WHERE
    (date_retrieved BETWEEN '2003-01-01' AND '2003-01-15')
    AND
    contains(title,'hello')

    UNION ALL

    Select a,b,c FROM table1 WHERE
    (date_retrieved BETWEEN '2003-01-01' AND '2003-01-15')
    AND
    contains(subject,'goodbye')


    and is fast. Is this a better way to do the above??
  4. bradmcgehee New Member

    Obviously the clustered index scan is causing most of your performance problems, and is most likely because of the OR in your query.

    If the new version of your query is working faster and uses less I/O, then you are on the right track.

    Just curious, do you have a clustered index on the date column you are querying? If not, you might want to consider it as this will help with the query's performance on the range of data. You will only know if this helps if you give it a try.


    ------------------
    Brad M. McGehee
    Webmaster
    SQL-Server-Performance.Com

Share This Page