Sfrgttk

Website seeing my Facebook data?

How to politely refuse in-office gym instructor for steroids and protein

Boss asked me to sign a resignation paper without a date on it along with my new contract

Not a Long-Winded Riddle

How to write cases in LaTeX?

Is a creature that sees a Medusa's eyes automatically subjected to a saving throw?

When Are Enum Values Defined?

Critique vs nitpicking

Converting very wide logos to square formats

Am I correct in stating that the study of topology is purely theoretical?

Why is 'diphthong' pronounced the way it is?

How can the probability of a fumble decrease linearly with more dice?

How do you funnel food off a cutting board?

Taking headphones when quitting job

Is there a file that always exists and a 'normal' user can't lstat it?

Non-Cancer terminal illness that can affect young (age 10-13) girls?

Why is that max-Q doesn't occur in transonic regime?

A fantasy book with seven white haired women on the cover

Broad Strokes - missing letter riddle

Can a player sacrifice a creature after declaring that creature as blocker while taking lethal damage?

What can I do to encourage my players to use their consumables?

"Starve to death" Vs. "Starve to the point of death"

Could a warlock use the One with Shadows warlock invocation to turn invisible, and then move while staying invisible?

Potential client has a problematic employee I can't work with

Query with conditional JOIN running slow

sql server partitioned view execution plan questionsConditional Join - Specific Column SelectFull outer join does not work for two small tables, error 1064Poor performance on inner join using dates and betweenNew query slower even if it has less logical reads than the old oneLogical read when use Offset in SQLIndex lost while joining on unionMost efficient way to join two tables with MAX valueHow does LEFT JOIN with WHERE clause works?High processor utilization when running a stored procedure

2

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 ?

sql-server-2014 join execution-plan statistics union

share|improve this question

asked Aug 13 '18 at 4:10

virvir

112

bumped to the homepage by Community♦ 1 min ago

This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.

add a comment | 

2

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 ?

sql-server-2014 join execution-plan statistics union

share|improve this question

asked Aug 13 '18 at 4:10

virvir

112

bumped to the homepage by Community♦ 1 min ago

This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.

add a comment | 

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 ?

sql-server-2014 join execution-plan statistics union

share|improve this question

asked Aug 13 '18 at 4:10

virvir

112

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

                    .

                    .

                    .

                    .

                    )



)

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

     .

     .

     .                      



 )

share|improve this question

asked Aug 13 '18 at 4:10

virvir

112

share|improve this question

asked Aug 13 '18 at 4:10

virvir

112

asked Aug 13 '18 at 4:10

virvir

112

asked Aug 13 '18 at 4:10

virvir

112

1 Answer
1

active

oldest

votes

0

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

share|improve this answer

answered Aug 13 '18 at 4:38

KondybasKondybas

2,646912

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  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
  

  
  
  
  

add a comment | 

Your Answer

StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "182"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});

function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: false,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: null,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});


}
});

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Sign up using Google

Sign up using Facebook

Sign up using Email and Password

Post as a guest

Name

Email

Required, but never shown

StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f214739%2fquery-with-conditional-join-running-slow%23new-answer', 'question_page');
}
);

Post as a guest

Name

Email

Required, but never shown

0

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

share|improve this answer

answered Aug 13 '18 at 4:38

KondybasKondybas

2,646912

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  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
  

  
  
  
  

add a comment | 

0

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

share|improve this answer

answered Aug 13 '18 at 4:38

KondybasKondybas

2,646912

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  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
  

  
  
  
  

add a comment | 

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

share|improve this answer

answered Aug 13 '18 at 4:38

KondybasKondybas

2,646912

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

share|improve this answer

answered Aug 13 '18 at 4:38

KondybasKondybas

2,646912

answered Aug 13 '18 at 4:38

KondybasKondybas

2,646912

answered Aug 13 '18 at 4:38

KondybasKondybas

2,646912