CODEWITHSUNDEEP
sql-serverperformancet-sql
DataDoer
DataDoer

Reputation: 209

SQL Server OR condition performance issue

Here is simpler version of one of the SELECT statement from my procedure:

select
    ...
from
    ...
where
    ((@SearchTextList IS NULL) OR 
     (SomeColumn IN (SELECT SomeRelatedColumn From #SearchTextListTable)))

@SearchTextList is just a varchar variable that holds a comma-separated list of strings. #SearchTextListTable is single column temp table that holds search text values.

This query takes 30 seconds to complete, which is performance issue in my application.

If I get rid of the first condition (i.e. if I remove OR condition), it takes just ONE second.

select
    ...
from
    ...
where
    SomeColumn IN (SELECT SomeRelatedColumn From #SearchTextListTable)

Can somebody please explain why this much difference?

What's going on internally in SQL Server engine?

Thanks.

Upvotes: 2

Views: 3147

Answers (1)

James Z
James Z

Reputation: 12317

Since you said that the SQL is fast when you don't have the OR specified, I assume the table has index for SomeColumn and the amount of rows in #SearchTextListTable is small. When that is the case, SQL Server can decide to use the index for searching the rows.

If you specify the or clause, and the query is like this:

((@SearchTextList IS NULL) OR 
 (SomeColumn IN (SELECT SomeRelatedColumn From #SearchTextListTable)))

SQL Server can't create a plan where the index is used because the plans are cached and must be usable also when @SearchTextList is NULL.

There's usually 2 ways to improve this, either use dynamic SQL or recompile the plan for each execution.

To get the plan recompiled, just add option (recompile) to the end of the query. Unless this query is executed really often, that should be an ok solution. The downside is that it causes slightly higher CPU usage because the plans can't be re-used.

The other option is to create dynamic SQL and execute it with sp_executesql. Since in that point you know if @SearchTextList will be NULL, you can just omit the SomeColumn IN ... when it's not needed. Be aware of SQL injection in this case and don't just concatenate the variable values into the SQL string, but use variables in the SQL and give those as parameter for sp_executesql.

If you only have this one column in the SQL, you could also make 2 separate procedures for both options and execute them from the original procedure depending on which is the case.

Upvotes: 1

Related Questions