3

I have a query the first part of which looks like this -

With CTE AS (
select DISTINCT
A.col1,
A.col2,
.
.
.
from tableA A 
                    JOIN tableB B ON 
                    (
                    B.col3= 'X' and B.col4=A.col3 
                    OR
                    B.col3= 'Y' and B.col4=A.col4
                    OR
                    .
                    .
                    .
                    .
                    )

)

Now this query runs for more than 4 minutes and the execution plan showed a table spool which was data size of 600 MB and looked like this -

enter image description here

I changed this to conditional JOIN to UNIONs -

With CTE AS (
select 
    A.col1,
    A.col2,
    .
    .
    .
    from tableA A 
                    JOIN tableB B ON 
                    (
                    B.col3= 'X' and B.col4=A.col3 
                    )
    UNION
    select 
    A.col1,
    A.col2,
    .
    .
    .
    from tableA A 
                    JOIN tableB B ON 
                    (
                    B.col3= 'Y' and B.col4=A.col4
                    )
     UNION
     .
     .
     .                      

 )

Now the spool with the large number of rows vanished and the run time reduced to 17 seconds. However when I check the IO statistics, the logical reads are higher than the previous slower query. What could the reason be ?

-1

The reason is that AND and OR has the same precedence and the long condition x AND y OR z AND w OR v AND... without round brackets has a bit different meaning than you think.

Your query can be rewritten that way:

FROM tableA AS A 
JOIN tableB AS B ON ( B.col3 = 'X' AND B.col4 = A.col3 )
                 OR ( B.col3 = 'Y' AND B.col4 = A.col4 )
                 OR (. . . . .)
  • I've tried this too and got the same run time and statistics(and plan) as the first query(the one with the spool with 29 million rows). And I think AND has precedence over OR. – vir Aug 13 '18 at 4:45
  • AND and OR actually have different precedence. In absence of brackets, AND has higher priority. Your ON predicate, therefore, would work the same way with or without those brackets. That said, however, specifying the brackets makes the intent clearer, as not everyone can constantly remember the precedence rules. – Andriy M Aug 13 '18 at 12:38

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.