Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

SQLAndy

I'm Andy Warren, currently a SQL Server trainer with End to End Training. Over the past few years I've been a developer, DBA, and IT Director. I was one of the original founders of SQLServerCentral.com and helped grow that community from zero to about 300k members before deciding to move on to other ventures.

Leading Wildcards Aren’t A Good Default

SQL Server has LIKE, which lets us do various pattern matches, but most commonly we use the percent side as a wildcard, such that “where lastname  like ‘Smi%’” matches Smith and Smit and Smithy. It’s a useful tool. It can also be used to do a contains type search, something along the lines of “where firstname like ‘%nd%’” which would match Andy and Sandi and Tandy and whatever else.

If you dig into show SQL Server uses indexes searches that use a trailing wild card typically optimize well assuming the number of rows it expects to match are reasonable (statistics). Leading wild cards almost always drive SQL to do an index scan or a table scan, which are usually more expensive.

That’s not intuitive to someone that doesn’t live and breath SQL Server. They see it as the column is indexed, I should be able to search it quickly. Because they don’t have a mental model that implies slowness the most common pattern I see in application searches is for the developers to do the contains type search with leading and trailing wildcards. The result is slow performance, even in cases where much better performance is possible.

My preference is to use the trailing wildcard, and then either let the user type in the leading wild card if they need it, or give them a “contains” checkbox to check. That leaves them with flexibility, but gives on average much better performance.

It’s our job to catch this as they use it and educate them. Sometimes they listen, sometimes the queries just stay slow!

SQLRally 2011

Comments

No comments.

Leave a Comment

Please register or log in to leave a comment.