CODEWITHSUNDEEP
sql-serverperformancet-sqlwildcard
ArtK
ArtK

Reputation: 1185

SQL Server : wildcard penalty

I need to know if SQL Server will have a significant performance penalty for using wildcard or duplicating the same filter in a WHERE clause.

I will have 2 variables in WHERE statement which are controlled by stored procedure code. I need to know if these queries will cause performance problems

Listing 1

declare @filter1 varchar(20)
declare @filter2 varchar(20)
(...)
set @filter2 = @filter1
(...)
select Col1, Col2, Col3
from Table1
where Col1 like @filter1 or Col1 like @filter2

Listing 2

declare @filter1 varchar(20)
declare @filter2 varchar(20)
(...)
set @filter2 = '%'
(...)
select Col1, Col2, Col3
from Table1
where Col1 like @filter1 and Col1 like @filter2

I know you may question why I'm turning to such strange code. I just have different number of filters depending on first variable passed to stored procedures and I need to arbitrarily assign second filter. But sometimes that second filter is not needed. I looked at using CASE in WHERE clause but despite positive feedback on this portal, CASE will not work here.

Upvotes: 0

Views: 152

Answers (1)

Joel Coehoorn
Joel Coehoorn

Reputation: 415600

The thing to remember about databases is that performance is almost entirely driven by indexing. Good indexing and usage = good performance. Bad indexing or usage = bad performance. A whole lot of what you do when tuning a query is just writing it to line up better with your indexes, or modifying indexes to line up better with a query.

Leading wildcards prevent you from using normal indexes at all! So, yes, there is likely to be a steep performance penalty for this.

Moving beyond wildcards: even an OR condition will make Sql Server less likely to use an index. You might consider writing the query like this:

select Col1, Col2, Col3
from Table1
where Col1 like @filter1

union

select Col1, Col2, Col3
from Table1
where Col1 like @filter2

To make it clear to Sql Server that it can use the index, or this:

Set @filter2 = COALESCE(@filter2, @filter1)
select Col1, Col2, Col3
from Table1
where Col1 like @filter1 and Col1 like @filter2

Again, if possible, only use trailing wildcards. This still isn't great for indexing, but it's better than nothing.

If this is a very large text/varchar(max) kind of column, you should also consider full-text index.

Upvotes: 3

Related Questions