2

One of my multi-step queries makes use of optional filters. Rather than using dynamic sql or if statements, I am attempting to use option(recompile) to make the filtered step choose appropriate indexes regardless of which optional filters are provided. This did not work once I tried to store the results of the sub-expression.

I was able to reproduce this in SSMS as follows:

declare @UID bigint = 100

--Query A: Uses index on UID
select count(1) from Entries 
where (@UID is null or UID = @UID)
option(recompile);

--Query B: Does an index scan (much slower)
select @cnt = count(1) from Entries 
where (@UID is null or UID = @UID)
option(recompile);

--Query C: Uses index on UID
select @cnt = count(1) from Entries 
where (UID = @UID);

My goal is to make query B use an index on UID if @UID is not null. I could accomplish this by conditionally replacing it with Query C, but my preference is to avoid that, as it strikes me as overly verbose. I can't use Query A, since I want to store the intermediate result for use in the next query within a stored procedure.

I did try selecting into a temp table and I tried using nullif, but these approaches still did a scan rather than a seek.

5
  • 1
    Why not use Dynamic SQL? sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries Jun 23 '17 at 15:14
  • @sp_BlitzErik: I try to avoid dynamic SQL unless absolutely necessary. It's far more annoying to maintain. This query isn't executed often enough for me to mind paying for a recompile. As an aside, one not-so-obvious way to fix this (which makes me cringe) is to use Query A in its own stored procedure.
    – Brian
    Jun 23 '17 at 15:30
  • @JoeObbish: Only 3 filters. My current fix is to just use if statements (in my question, this corresponds to "conditionally replacing it with Query C.") However, I'd still love to know exactly how to convince SQL to figure this stuff out on its own. I was expecting to be able to use the Sommarskog's Static SQL - The Basic Technique. However, as soon as I tried to store intermediate results, the technique failed.
    – Brian
    Jun 23 '17 at 20:51
  • Do you get a better plan with set @cnt = (select ...)? Jun 24 '17 at 10:01
  • @MichaelGreen: No, that gives the same result.
    – Brian
    Jun 24 '17 at 15:29
3

If @UID is NULL then the query cannot be executed with a seek. That means in order to get a seek you need the value of the variable to not be NULL and you need the parameter embedding optimization to be applied to the query. In other words, the optimizer needs to see the value of the variable before creating the plan. Usually this can be accomplished with a RECOMPILE hint but not always. The final example of the linked blog shows how the parameter embedding optimization cannot be applied when assigning a value to a variable.

We can also see hints of that behavior by running your example queries. For query A I get the following as a seek predicate:

Seek Keys[1]: Prefix: [SE_DB].[dbo].[Entries].UID = Scalar Operator((100))

However, for query B I get the following predicate:

[@UID] IS NULL OR [SE_DB].[dbo].[Entries].[UID]=[@UID]

The @UID reference was not replaced with 100 for query B so we must get a scan.

We can also see the difference in the query trees created for optimization with undocumented trace flag 8606:

enter image description here

Query A is on the left and query B is on the right. For query A, the query optimizer builds the query plan using the literal value of the variable. For query B, the query optimizer builds the plan only knowing that a variable was used.

There are plenty of workarounds available that don't require dynamic SQL or branching code. You can add up the results of two subqueries:

SELECT @cnt =
(
    select count(1) cnt
    from Entries 
    where @UID is null
) +
(
    select count(1) cnt
    from Entries 
    where @UID is not null and UID = @UID
);

You can use UNION ALL:

SELECT @cnt = SUM(cnt)
FROM
(
    select count(1) cnt
    from Entries 
    where @UID is null

    UNION ALL

    select count(1) cnt
    from Entries 
    where @UID is not null and UID = @UID
) t
option(recompile);

You can also insert into a temp table and grab the value from the temp table:

select count(1) cnt INTO #t
from Entries
where (@UID is null or UID = @UID)
option(recompile);

SELECT @cnt = MAX(cnt) FROM #t;

DROP TABLE #t;
1
  • For some reason when I tried the temp table approach previously it didn't embed the query. I must have unknowingly done something different, because I can't reproduce it. Thank you for explaining the issue in such detail.
    – Brian
    Jun 26 '17 at 13:04

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.