Stored procedure won't use index

[Trudie]

Hi,

We're using SQL Server 2000.

A very basic query on a 5 million row table would not work. If we
hard coded the one input parameter, the query used the foreign key
index and produced an instantaneous result. If, however, we pass the
parameter in as a parameter, the query doesn't use the index and takes
forever. E.g.

Declare @ID int
SET @ID = 17697

Select top 1 AccountID from tblAccounts where GroupID = @ID

We have fixed the problem by using an index hint to force the query to
use the index. However, my concern is - why is the index not
automatically used when we remove the hardcoded ID and repalce it with
a parameter? Is it a problem with the index? We do not use index
hints as a coding standard, so will this happen to other stored
procedures in our DB?

Any advice about how and why this occurs would be much appreciated.

Trudie

[Sjaak van Esdonk]

Did you create the index after the stored procedure was created? You
can try to rebuild the SP using the with recompile option. This way
the query plan is not being cached and rebuild every time you call the
SP

[Trudie]

The index was created before the SP

Trudie

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Erland Sommarskog]

[posted and mailed, please reply in news]

Trudie (tr****@sparkdata.co.uk) writes:

We're using SQL Server 2000.

A very basic query on a 5 million row table would not work. If we
hard coded the one input parameter, the query used the foreign key
index and produced an instantaneous result. If, however, we pass the
parameter in as a parameter, the query doesn't use the index and takes
forever. E.g.

Declare @ID int
SET @ID = 17697

Select top 1 AccountID from tblAccounts where GroupID = @ID

This is a little unclear. In the narrative, you talk about a parameter,
but the example there is a plain variable. And, yes, that makes a
difference.

In the snippet above, SQL Server has no idea of what value @ID has,
so it will have to make some general assumption. If the value of
GroupID is skewed, so that 4.5 half million rows all have 0 in
the column, using the index is a very poor idea, in case @ID is 0.

When you have a parameter to a stored procedure, SQL Server does build
the query plan from the value the parameter has the first time you
call the procedure, and that plan is then cached. So if you first call
it with some value is that is too frequent to permit an Index Seek,
you will get a table scan also when you pass values that are more
selective.

Even when you call the procedure with a good value the first time, I
suspect that the optimizer does not build a plan based on an index,
if most other values are bad for the index.

--
Erland Sommarskog, SQL Server MVP, so****@algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp