12

I have a query on a large table that looks like this:

declare @myIdParam int = 1

select * 
from myTable
where (@myIdParam is null or myTable.Id = @myIdParam)

There are several similar conditionals like this in the where clause, and there are also a lot of joins, but this is a summary.

Effectively, if @myIdParam is null, we do not want to restrict the results using this parameter.

I am not a DB pro, but from my tests it seems like this NULL check is done for every record and not optimized out in any way.

If I remove the null check and assume the parameter is not null, the query returns instantly. Otherwise, it takes up to ten seconds.

Is there a way to optimize this so the check is done only once at runtime?

  • 1
    Look at this answer: stackoverflow.com/questions/3415582/… tl;dr use OPTION(RECOMPILE) – vercelli Aug 26 '16 at 15:07
  • @vercelli this does the trick. Considering this question is really about optional parameters, I'd say it's a duplicate of the one you linked. – Mystagogue Aug 26 '16 at 15:18
  • Probably, but it's a post from 6 years ago. Maybe with SqlServer 2014 or 2016 there's a new approach. (I tested it on 2014 without recompile and took forever) – vercelli Aug 26 '16 at 15:25
  • Since your actual query has many optional parameters, dynamic SQL will provide the best performance. See sommarskog.se/dyn-search.html for a thorough article on the subject. – Dan Guzman Aug 27 '16 at 15:25
  • @DanGuzman using WITH RECOMPILE as outlined in the question vercelli linked cut the query time from just under a minute to practically instant with highly selective criteria. I consider this the best option for balancing performance and readability. – Mystagogue Aug 29 '16 at 14:57
8

One way is to use dynamic SQL, using a null check to optionally add that part of the where clause.

declare @myIdParam int = 1
declare @vc_dynamicsql varchar(max)

set @vc_dynamicsql = 'select * from myTable where 1=1'

if @myIdParam is not null
    set @vc_dynamicsql = @vc_dynamicsql + ' and  myTable.Id = @myIdParam'

EXECUTE sp_executesql @vc_dynamicsql
  • 2
    I would really prefer not to do this, but it is a solution. My hope is that someone comes along with a much better one. – Mystagogue Aug 26 '16 at 15:05
  • 1
    This is the best way to handle this class of search query. The stackoverflow answer referred to by @vercelli contains great references for how to do this. – Max Vernon Aug 26 '16 at 16:29
  • This is the best method but I did notice that the @params parameter for sp_ExecuteSQL is missing and the @vc_dynamicsql parameter needs to be a NVARCHAR. – James Anderson Aug 31 '16 at 10:00
4

Any time you put a function around a column `ISNULL(@var, table.col)' for example you remove SQL's ability to use an index. This really is the best performing option if you want to keep it in a single query.

@var IS NULL or @var = table.col

Otherwise you have two options. The first is dynamic SQL and @Mystagogue's answer is sufficient for that otherwise you can put in two queries like this:

IF @var is NULL
     SELECT * FROM table
ELSE
     SELECT * FROM table WHERE @var = col

In both this format and the dynamic SQL you will actually get a different query plan for each of the queries (which will potentially yield better performance).

  • The Sql in the question is not using ISNULL or any other function. – Mystagogue Aug 26 '16 at 18:29
  • @MystagogueI I was referencing a now deleted answer. – Kenneth Fisher Aug 26 '16 at 18:37
0

Well, you can:

declare @myIdParam int = 1;

select *
from myTable
where nullif(@myIdParam, myTable.Id) is null;

Keep in mind, however, that the nullif() function is essentially a wrapper over case. It's not a silver bullet that magickally eliminates OR and thus speeds up the query.

  • using functions in a where clause has a negative impact on performance because it prevents the use of indexes (or so I've heard) – Mystagogue Aug 29 '16 at 14:55
  • @Mystagogue, yes - it usually makes search conditions non-SARGable. Alas, this is the only way I know how to answer your question without resorting to dynamic SQL or multiple UNIONs. When I had this exact task, I chose dynamic SQL. – Roger Wolf Aug 30 '16 at 1:26

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.