weird stored procedure performance | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

weird stored procedure performance

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

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
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??

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
]]>