Query with conditional JOIN running slow 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 -



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 ?
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 (. . . . .)