OR Condition Performance
This article describes a very typical scenario, and one that I have encountered several times in recent years. It is a common mistake that is made by experienced developer and beginner alike.Before discussing the mistake, let's start by describing how the optimizer uses indexes.
If all columns referenced by the OR condition have indexes, these are considered for use by the optimizer. If one or more of the columns are not indexed, it will always perform an index or table scan.
I think this is fairly well understood. What is not well understood is how it behaves when one of the conditions is on a variable.
... where col1 = @col1 or @col1 = 0
This will always perform a scan. Unfortunately you will often come across a requirement where this is the most elegant solution. As always, an example is the best to way to explain what I mean. First set up a table with some test data.
set nocount on
create table or_test (id int identity primary key clustered, col1 int,
col2 int)
go
insert into or_test (col1, col2)
select convert(int, rand() * 1000000), convert(int, rand() * 1000000)
go 1000000
create index idx_or_test_col1 on or_test (col1)
go
create index idx_or_test_col2 on or_test (col2)
go
create table or_test (id int identity primary key clustered, col1 int,
col2 int)
go
insert into or_test (col1, col2)
select convert(int, rand() * 1000000), convert(int, rand() * 1000000)
go 1000000
create index idx_or_test_col1 on or_test (col1)
go
create index idx_or_test_col2 on or_test (col2)
go
The requirement is a stored procedure that searches by id, col1, col2, or a combination of these. The common approach is as follows:
create procedure test1
@id int = 0,
@col1 int = 0,
@col2 int = 0
as
begin
set nocount on
select id, col1, col2
from or_test
where (id = @id or @id = 0)
and (col1 = @col1 or @col1 = 0)
and (col2 = @col2 or @col2 = 0)
end
go
exec test1 @id = 400000
go
@id int = 0,
@col1 int = 0,
@col2 int = 0
as
begin
set nocount on
select id, col1, col2
from or_test
where (id = @id or @id = 0)
and (col1 = @col1 or @col1 = 0)
and (col2 = @col2 or @col2 = 0)
end
go
exec test1 @id = 400000
go
This produces the following execution plan:
There are a number of alternatives, but none as elegant. My preferred solution is to use dynamic SQL and execute it with sp_executesql. This ensures that the plan is cached and re-used each time. Try the following:
create procedure test2
@id int = 0,
@col1 int = 0,
@col2 int = 0
as
begin
set nocount on
declare @sql nvarchar(255), @cond nvarchar(255)
set @sql = 'select id, col1, col2 from or_test '
set @cond = ''
if @id <> 0 set @cond = 'where id = @id '
if @col1 <> 0
if @cond = ''
set @cond = 'where col1 = @col1 '
else
set @cond = @cond + 'and col1 = @col1 '
if @col2 <> 0
if @cond = ''
set @cond = 'where col2 = @col2 '
else
set @cond = @cond + 'and col2 = @col2 '
set @sql = @sql + @cond
exec sp_executesql @sql, N'@id int, @col1 int, @col2 int', @id, @col1,
@col2
end
go
exec test2 @id = 400000
go
@id int = 0,
@col1 int = 0,
@col2 int = 0
as
begin
set nocount on
declare @sql nvarchar(255), @cond nvarchar(255)
set @sql = 'select id, col1, col2 from or_test '
set @cond = ''
if @id <> 0 set @cond = 'where id = @id '
if @col1 <> 0
if @cond = ''
set @cond = 'where col1 = @col1 '
else
set @cond = @cond + 'and col1 = @col1 '
if @col2 <> 0
if @cond = ''
set @cond = 'where col2 = @col2 '
else
set @cond = @cond + 'and col2 = @col2 '
set @sql = @sql + @cond
exec sp_executesql @sql, N'@id int, @col1 int, @col2 int', @id, @col1,
@col2
end
go
exec test2 @id = 400000
go
Giving the following execution plan
I know that a lot of people frown on dynamic SQL because they feel it is not as efficient (the above is much more efficient) and because they worry about the danger of SQL injection. You should always include error handling and you should obviously test your stored procedures thoroughly before deploying them to a production environment.
This article underlines the importance of always checking the execution plan for queries you write. OR condition performance is just one of many areas in which tuning of SQL can make a huge difference to application performance.
Not found what you're looking for?
Use this search box. It is tuned for SQL Server searches. Try it and see!Do you have a question about SQL Server? Would you like to answer a question?
Go to the SQL FAQ to get started.
