Using the Stack Overflow public export, take these two queries looking for a particular user by Id (the clustering key):
|
1 2 |
SELECT * FROM dbo.Users WHERE Id = 26837; SELECT * FROM dbo.Users WHERE Id LIKE 26837; |
The first one (=) gets a clustered index seek, does just 3 logical reads, and correctly estimates that only 1 row will be returned.
The second one (LIKE) does a clustered index scan, reads the entire table, and wildly overestimates that 475,005 rows will be returned – even though only 1 row will.
The actual execution plans complete with the warning on the SELECT statement tell the story: SQL Server thinks you’re implicitly converting the Id to a VARCHAR(12), and then doing a comparison there.
Wanna go faster without changing your code? Sure, we all do – so give SQL Server a pre-converted field that it can use for comparisons:
|
1 2 |
ALTER TABLE dbo.Users ADD IdAsVarchar AS CAST(Id AS VARCHAR(12)); CREATE INDEX IX_IdAsVarchar ON dbo.Users(IdAsVarchar); |
We’re adding a computed field, then indexing that field.
Then run your really, really stupid query again – don’t change it, just run it exactly as-is:
|
1 |
SELECT * FROM dbo.Users WHERE Id LIKE 26837; |
And presto, SQL Server does an index seek on our newly created computed field:
Sure, it’s still warning about implicit conversion, but get this: it now correctly estimates just 1 row will come back, and does just 6 logical reads.
I’d rather fix the code, but if you can’t, this is a pretty spiffy way to help SQL Server do bad things – faster.
5 Comments. Leave new
Brent Ozar — helping SQL Server do bad things faster, since 1999.
” do bad things – faster.” ah! the Max Power Way!
https://www.youtube.com/watch?v=7P0JM3h7IQk
Excellent article. Many times we cannot change the code. They would probably gripe even about this change.
” I don’t know about approving that change. Once in a blue moon if the stars aligned just right sometime in the future it could affect performance. It takes space doesn’t it? I just don’t know if I am comfortable with the change…”
Argh!
Brent prestidigitates SQL Server again.
Thank You for today’s magic, Brent.
Always nice to have another trick up the sleeve for the shrill cry of “We can’t change the code!!”
Implicit conversion always follows data type precedence, except when it doesn’t.
Somewhat interesting to see that this is doing a “backwards” conversion on both sides of an operator, whereas you’d normally see it happen on the way into a sproc parameter or the like (getcha puns here).
Gah. I hate it. I’ve been moaning for some time that I’d like to see T-SQL strongly typed. It’d naturally follow that devs and architects would actually have to think about what they’re doing and code quality would shoot up. I can see why it’d be a task, with the data types living in their own so-called “families”; the best I can generally do is explicitly make sure the two expressions either side of any operators match even if the precedence list is in my favour (i.e. I’ll cast a varchar to an nvarchar if I’m comparing to an nvarchar, even though it’ll happen implicitly) and hope that that hammers the danger of blind coding home to other devs who have to read my code.
On the other hand it does provide us all with a neverending stream of low-hanging fruit that makes us look like highly-skilled wizards, but relying on that sort of thing for my career would be an uncomfortable step towards the sort of ecosystem ERPs tend to foster.