Where clause prevents index seek

Question

Where clause prevents index seek

Viewing 8 posts - 16 through 22 (of 22 total)

You must be logged in to reply to this topic. Login to reply

MMartin1 One Orange Chip Points: 27795 More actions · Answer 1

So you are able to change the index on the table and not a query? If you knew for a fact that changing the query will not break anything else you could not do the work of changing it yourself on a emergency basis? I assume if you posted here it is at least an urgent matter.

----------------------------------------------------

PHXHoward SSCrazy Eights Points: 8389 More actions · Answer 2

Lynn's solution got us through it. I don't understand why it worked but it did.

Creating a persisted computed column with '_' + PK and then creating a NCI on that column caused the query to seek on that index.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 3

PHXHoward (11/30/2015)
Lynn's solution got us through it. I don't understand why it worked but it did.
Creating a persisted computed column with '_' + PK and then creating a NCI on that column caused the query to seek on that index.

Run this:

select top 10 * from dbo.<your table name here>;

Look at the data in your PK column and the computed column. You have indexed the computed column which has the same data type as the concatenated value generated in the query. SQL can now do a seek on the NCI you created instead of having to scan the entire table concatenating an underscore and converting an integer value to a character string to complete the comparison.

PHXHoward SSCrazy Eights Points: 8389 More actions · Answer 4

I get it that they look the same but I did not know that SQL Server was smart enough to use an NCI on a computed column that is not referenced anywhere in the query.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 5

PHXHoward (11/30/2015)
I get it that they look the same but I did not know that SQL Server was smart enough to use an NCI on a computed column that is not referenced anywhere in the query.

We use a PK that is a VARCHAR(36) value. SQL Server was doing a NCI scan on a date index instead of scanning the clustered index to convert and compare the varchar value to a nvarchar value to find a specific row of data. By creating a persisted computed column and indexing it, you gave SQL Server a means to quickly find the data it wanted without having to do additional work.

MMartin1 One Orange Chip Points: 27795 More actions · Answer 6

PHXHoward (11/30/2015)
I get it that they look the same but I did not know that SQL Server was smart enough to use an NCI on a computed column that is not referenced anywhere in the query.

When you do operations in the WHERE clause (like in your case '_' + myString) SQL cannot really use an index to retrieve the resulting value since it does not know what that resulting value will be. When you persist the results ahead of time to the table, then SQL can order those results accordingly in a NCI.

----------------------------------------------------

PHXHoward SSCrazy Eights Points: 8389 More actions · Answer 7

On its own, it is checking the table to see if there is a persisted computed column that has a NCI that matches the WHERE criteria. That's pretty great. I love SQL Server.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 8

PHXHoward (12/1/2015)
On its own, it is checking the table to see if there is a persisted computed column that has a NCI that matches the WHERE criteria. That's pretty great. I love SQL Server.

Actually, it is looking for an index that it can use efficiently to accomplish the task. We provided that by creating a persisted computed column and creating an index on that column.