FORCE INDEX USE in a WHERE statement?

[davism]

Hi all,

I have a question that may be able to be resolve here and greatly appreciate any information. It involves SQL Server 2005.

I have a stored procedure that runs ok considering the volume of data that I am dealing with. However, I did a query plan on it and it showed an area that had a cost of 39% and a table scan. In looking at that a little further, it came down to a value that was put into the WHERE clause, for instance,

WHERE a='test'
AND b=1
AND c='hello'

I'm just looking at how I can made it a little better. The situation or cost is associated with the b=1. Right now, if I remove that then the stored procedure actually runs 3 times faster based on the statistics (albeit, remember, this is relatively quick already) but I will need that b=1 in there.

The interesting thing is that the query plan is actually showing it as a table scan. However, that column "b" is actually defined as a non-clustered index. My question is HOW can I have it use the index on the AND statement. (like a FORCED INDEX or INDEX HINT but not on the table. (NOTE: I did implement an INDEX HINT for it on the TABLE but I obviously can only have one and the INNER JOIN was already using an index so adding the index hint appeared to negate the other and ran much longer.))

I'm not sure why the optimizer is doing this but does anybody know any way to force an index use on a WHERE statement?

Any info would be greatly appreciated.

 

[gmmastros]

Does this table have a primary key? Is there a clustered index on this table?

I ask because.... By default, primary keys are implemented using a clustered index. Furthermore, if there was a clustered index on this table, you would not get a table scan. Instead you would get a clustered index scan.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[davism]

gmmastros, basically, the answer is no and no. No, there isn't a primary key and no there isn't a clustered index.

I did change the SARGS around and added the b=1 as the last condition in the WHERE and it did improve it but not by much. Instead of like 1078 it went to 1050 (on the elapsed time in the statistics).

If I comment out that b=1 then I get an elapsed time of like 300. BUT I need that b=1 in there.

 

[gmmastros]

Without the b, you do not get a table scan, right? I assume you are getting an index scan or an index seek. This is probably because you have an index that covers all of your query when the b column is not part of the query. When you include the b column, the index that WAS used is no longer used because the b column is not part of the index. In this case, it is quicker for SQL to use the table data instead.

My suggestion is this...

1. determine which index is used when the b column is not included in the query.
2. modify the index to include the b column. With SQL2005, there are 2 different ways to include a column. You can make the column part of the index so that it is included in the ordering, or you can use the INCLUDE keyword when creating the index. This would include the b column as part of the data in the index, but not part of the ordering.

http://msdn.microsoft.com/en-us/library/ms190806.aspx

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[davism]

Interesting...I found another index (must have not associated the name then) that includes b and d (where d is the main item). d is the item in the INNER JOIN statement

So, something like:

INNER JOIN TestTable on TestTable.d = MyTest.a

How can I used that that found index that incorporates d and b?

Would that be:

INNER JOIN TestTable on TestTable.d = MyTest.a
AND b=1

Although, I tried that and still got a table scan with a 39% cost. :-(

 

[gmmastros]

Read the link I pointed you to and then make sure you have an index that includes all of the columns used in your query.

If possible, I would encourage you to create a clustered index on this table.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[davism]

The index is creating using the d and the b columns already. So, using the INCLUDE that is discussed in the link you would not be applicable.

Would I or should I utilize something like:

INNER JOIN TestTable on TestTable.d = MyTest.a
AND TestTable.b=1

That would/should utilize the index that had the d and b in it, right?

 

[davism]

Actually, that not do anything. It still maintained the table scan.

I'm going to try the inner join with a forced index.

 

[davism]

Putting it in the utilized an RID but that did not improve anything.

It does not appear that there is much value in that it is doing either way (with or without an index). It doesn't make sense though.

I do know for certain that taking it about although reduces the execution time quite signficantly.

 

[davism]

Rephrase:

I do know for certain that taking it out really reduces the execution time quite signficantly.

 

[davism]

Does anybody have any other information for this? The item is an index and it just doesn't make a lot of sense why that index is not being used.

Any information would be of great value and appreciated.

Thanks