2

I would like to know if there is any way to rewrite the EXPRESSION in the query (just expression, not the whole query) to short-circuit useless evaluation of THEN phase?

Demo data:

CREATE TABLE #Docs (
    ID INT NOT NULL
    ,DocType TINYINT NOT NULL
    );

CREATE TABLE #DocsItems (
    IDDocs INT NOT NULL
    ,Amount NUMERIC(19,6)
    );

INSERT INTO #Docs(ID, DocType) VALUES(1,1),(2,1),(3,2),(4,2),(5,2),(6,2);
INSERT INTO #DocsItems(IDDocs,Amount) VALUES(3,50.),(3,25.),(3,33.),(4,44.),(4,123.),(6,11.);

Subject query:

SELECT
    -- expression
    SumAmount = CASE 
                    WHEN D.DocType <> 1 THEN (SELECT SUM(Amount) FROM #DocsItems WHERE IDDocs = D.ID)
                END
FROM #Docs D
WHERE D.DocType = 1  -- so CASE condition evaluates to False

Query plan: enter image description here

If I rewrite the query (on purpose) to:

SELECT
    -- expression
    SumAmount = CASE 
                    WHEN 2 = 1 /* rewrite*/  THEN (SELECT SUM(Amount) FROM #DocsItems WHERE IDDocs = D.ID)
                END
FROM #Docs D
WHERE D.DocType = 1

The plan goes the way it should / could / would in the original query: enter image description here

  • 1
    What exactly do you mean? This isn't clear for me – George.Palacios Oct 10 at 17:24
  • It's doing useless nested loop with #DocsItems table table scan. I know that in MS SQL Server CASE not always short-circuits - this is the example. I want to know if there is a way to rewrite this expression to short-circuit evaluation of this - always false - condition. – jerik1 Oct 10 at 17:32
  • table scan happens if there are not enough rows for an index to make sense or no index also why sum if you exclude it in your where statement, if at all you better have a union where you merge 2 statements one with the sum and one exclude the sum and return 0 or null – PPann Oct 10 at 17:45
  • 1
    @PPann this is not the point of my question, sorry for being so unclear, don't know how to say it other way. I counted on you get it from demo code and actual plan – jerik1 Oct 10 at 17:56
5

What is currently happening

When running your query, the table scan, stream agg & compute scalar operators are not evaluated at runtime.

enter image description here

enter image description here


Why is it happening

The apply NL join means that for each row in #Docs, return a row from #Docsitems that matches the predicate. This predicate should be WHERE IDDocs = D.ID

But the compute scalar operator (EXPR1007) next to the select (the actual case statement) is calling the scalar function in the inner side of the NL apply (EXPR1005) only when Doctype <> 1. This as you know cannot happen, and they both return NULL.

Compute scalar between NL & SELECT:

enter image description here

Compute scalar on the inner side of the join:

enter image description here

This all seems to be due to how the CASE statement functions & removing literals. (difference between CASE 2 = 1 vs. CASE WHEN D.DocType <> 1)


Resolving

If you change the query to this:

SELECT
    -- expression
    SumAmount = 
                     (SELECT SUM(Amount) FROM #DocsItems WHERE IDDocs = D.ID AND D.DocType <> 1 )

FROM #Docs D
WHERE D.DocType = 1  -- so CASE condition evaluates to False

You should get the execution plan you desire:

enter image description here


Removing the SELonLOJ rule that is changing the plan on the rewrite.

Guessing

Reverting the rule that was applied to get closer to the CASE WHEN query can be done by adding the hint: OPTION( QUERYRULEOFF SELonLOJ )

SELECT
    -- expression
    SumAmount = 
                     (SELECT SUM(Amount) FROM #DocsItems WHERE IDDocs = D.ID AND D.DocType <> 1 )

FROM #Docs D
WHERE D.DocType = 1  -- so CASE condition evaluates to False
OPTION( QUERYRULEOFF SELonLOJ   );

enter image description here

Showing the same situation (minus filter & left join) as the case statement, but the runtime elimination is not occuring.

enter image description here

&

enter image description here


Another rule that seems to get closer to the case when when turning it off is JoinPredNorm

SELECT
    -- expression
    SumAmount = 
                     (SELECT SUM(Amount) FROM #DocsItems WHERE IDDocs = D.ID AND D.DocType <> 1 )

FROM #Docs D
WHERE D.DocType = 1  -- so CASE condition evaluates to False
OPTION( QUERYRULEOFF JoinPredNorm   )

With the filtering happening on the #Docs table as expected.

enter image description here

  • never mind that there are no indexes and there only can be an table scan – PPann Oct 10 at 17:41
  • @PPann - forget the indexes that is not the point – jerik1 Oct 10 at 17:43
  • @jerik1 I'll delete the answer for now since the question has shifted to how to remove it entirely. – Randi Vertongen Oct 10 at 17:50

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.