Posted by Ross Presser on 06/04/05 00:45

On 3 Jun 2005 11:07:33 -0700, mas wrote:

> Tried that already, dropped and recreated the same procedure and also
> created a whole new procedure with different name - still get the same
> results.

Then try comparing the execution plans, updating the index statistics, or
consider adding an index. This part of your post

> I immediately copied the SQL out of the SP and pasted
> into another QA window, changed the variables to be hard coded values
> and ran it. It completed in 10 seconds.

makes me think very strongly that there is a vital difference in execution
plans. When hard coded values are handed to the query optimizer, it can
make very intelligent decisions about what index to use. But a stored
procedure must be compiled ahead of time, and parameter values are not
known ahead of time, and the optimizer sometimes does not make the right
decisions.

This can happen especially if you have too many parameters in the query.
For instance

SELECT * FROM TBL1
INNER JOIN TBL2 ON TBL1.K=TBL2.K
INNER JOIN TBL3 ON TBL1.M=TBL3.M
WHERE (TBL1.F1 = @X OR @X IS NULL)
AND (TBL2.F2 = @Y OR @Y IS NULL)
AND (TBL3.F3 = @Z OR @Z IS NULL)

Even if you have clustered indexes on F1, F2, F3, the optimizer might not
use them. Instead it will do a table scan on all three tables, because it
knows the "OR @X IS NULL" subclause might match every row if @X gets passed
in as null.

This kind of query is best broken up into separate queries:

IF @X IS NOT NULL
THEN SELECT * FROM TBL1 .... WHERE TBL1.F1 = @X
ELSE
IF @Y IS NOT NULL
THEN SELECT * FROM TBL1 .... WHERE TBL2.F2 = @Y
ELSE
IF @Z IS NOT NULL
THEN SELECT * FROM TBL1 ... WHERE TBL3.F3 = @Z

Precisely this kind of thing bit me very hard in the butt last year. The
users wanted to be able to search the member table on any combination of
name, memberID, or address fields. I had to get them to pare it down to
three or four kinds of most commonly used queries, and separate it that
way.

I'm sure that there will be other replies to this thread that will explain
it even better but I hope this helps.

[Back to original message]