Geek asks and answers

T-SQL - not optimal plan used - WHERE clause must be short-circuited

We have many "stored search procedures" that take several parameters with a null value to search for rows of data in different tables. They are usually built like this:

SELECT      *
 FROM       Table1 T1
 INNER JOIN Table2 T2
     ON     T2.something = T1.something
 WHERE      (@parameter1 IS NULL OR T1.Column1 = @parameter1)
        AND (@parameter2 IS NULL OR T2.Column2 = @parameter2)
        AND (@parameter3 IS NULL OR T1.Column3 LIKE '%' + @parameter3 + '%')
        AND (@parameter4 IS NULL OR T2.Column4 LIKE '%' + @parameter4 + '%')       
        AND (@parameter5 IS NULL OR T1.Column5 = @parameter5)

This can last up to 30-40 parameters, and what we noticed, even if only parameter1 is provided, the execution plan goes through scanning the indices of other tables, which can significantly slow down the query (several seconds). Tests show us that saving only the first row from the WHERE statement makes the request instant.

  • I read that shortcuiting is not possible, but is there any work or ways to build queries that could be more efficient?

  • , SELECT/FROM/JOINS, WHERE , , , , , .

+5
1

SQL Server . SQL Server , null, , , not null.

option (recompile) SQL Server 2005, SQL Server 2008 , .

, , SQL Server null.

T-SQL

, , . SP , .

create table T
(
  ID int identity primary key,
  Col1 int,
  Col2 int
);

go

create index IX_T on T(Col1);

go

create procedure GetT
  @Col1 int,
  @Col2 int
as

select ID
from T
where (Col1 = @Col1 or @Col1 is null) and
      (Col2 = @Col2 or @Col2 is null)
option (recompile);

go

exec GetT 1, null
exec GetT 1, 1
+4

More articles:


All Articles