weird stored procedure slowness

[unseth]

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

 

[MeanGreen]

Not sure why, but you could try this to remove the &quot;or&quot;:

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')

Hope this helps.

 

[unseth]

interesting.. that seems to work. I would think that would be more expensive, i'll have to look at the execution plans.

thanks