I have a stored procedure that hits the wrong index on the table it is querying against. When I take the query out of the sp it hits the right index. I've tried to drop and recreate the sp without any luck. I tried to use WITH (INDEX = [myIndex]), but it complains with:
"Query processor could not produce a query plan because of the hints defined in this query. Resubmit the query without specifying any hints and without using SET FORCEPLAN."
However, I can use this with the query taken out of the sp.

I'm stumped. Anyone have any suggestions on forcing the sp to use the correct index?

Thanks,
Aaron

-Aaron

Can you post user store procedure?

Here is the stored procedure:

CREATE PROCEDURE shettle.SearchAddRepAddLine
    @AddressLine varchar(1000),
    @Location varchar(100)
AS

IF @Location = '%'
    SELECT IsNull(Num, '') As StreetNumber,
         IsNull(PreDir, '') As Predirection,
         IsNull(shettle.InitCap(StrName), '') As StreetName,
         IsNull(shettle.InitCap(StrType), '') As StreetType,
         IsNull(SecUnitDes, '') As SecUnitDesignator,
         IsNull(SecUnit, '') As SecUnit,
         IsNull(shettle.InitCap(City), '') As City,
         IsNull(State, '') As State,
         IsNull(Zip, '') As Zipcode,
         IsNull(X, '') As X,
         IsNull(Y, '') As Y
    FROM addrep.dbo.Addpt WITH (INDEX = 4)
    WHERE MailAddLine = @AddressLine
    ORDER BY 1, 2, 3, 4, 5, 6, 7, 8, 9
ELSE
    SELECT IsNull(Num, '') As StreetNumber,
         IsNull(PreDir, '') As Predirection,
         IsNull(shettle.InitCap(StrName), '') As StreetName,
         IsNull(shettle.InitCap(StrType), '') As StreetType,
         IsNull(SecUnitDes, '') As SecUnitDesignator,
         IsNull(SecUnit, '') As SecUnit,
         IsNull(shettle.InitCap(City), '') As City,
         IsNull(State, '') As State,
         IsNull(Zip, '') As Zipcode,
         IsNull(X, '') As X,
         IsNull(Y, '') As Y
    FROM addrep.dbo.Addpt WITH (INDEX = 4)
    WHERE MailAddLine = @AddressLine
    AND MailCityLine LIKE @Location
    ORDER BY 1, 2, 3, 4, 5, 6, 7, 8, 9
GO

-Aaron


-Aaron

Have you drop or reindex your table. Try using the index name instead of the index id.


FROM addrep.dbo.Addpt WITH (INDEX (your indexname)))

You can use an index ID instead of the index name, but this is usaully not a good consideration because the index IDs can change if you drop and have recreate the indexes different that when you first created them in the table.

Why do you think the query processor is using the wrong index; that is, why do you think you want to be using the index you specify, rather than the plan it decides it needs? On what column(s) is your desired index defined for?

Jeff Mason
Custom Apps, Inc.
www.custom-apps.com

The index I'm trying to use is a non-clustered index on the column I'm using in the WHERE clause, namely MailAddLine. When ran with this index it returns in under a second. The stored procedure is using another clustered index that takes 7 secs.

Also, when I run the query with some sample input in Query Analyzer it uses the index on MailAddLine. Just the stored procedure uses the wrong index. I'm not sure why the another index is used when it's column its indexed is not even listed in the WHERE clause.

Then it really gets frustrating when you try to use WITH (INDEX = idxAddpt_MailAddLine) and it complains. If I tell it to use the index it normally uses (WITH (INDEX = idxAddpt_APID)) it goes through fine but again with the 7 sec processing time.

-Aaron

When the stored procedure is called, what is the value of @Location?

Note that if this value changes back and forth from '%' to a value <> '%', the cached query plan may be the wrong one and this may be the reason why the error message is issued. Try adding the WITH RECOMPILE option to the CREATE PROCEDURE line. This will force the stored procedure to be recompiled every time it is run.

Using WITH RECOMPILE may not be very efficient if the cached plan is usually the 'right' one; see BOL for the WITH COMPILE option for ways to use it more sparingly.

Jeff Mason
Custom Apps, Inc.
www.custom-apps.com