5 ms·

Another issue with sql server which is very baffeling. It is MUCH faster to use sub selects than left join if you data allows it. That is do not: select a.bla

by tyfon 10y ago

Another issue with sql server which is very baffeling. It is MUCH faster to use sub selects than left join if you data allows it. That is do not: select a.bla, b.blabla from foo a left join bar b on (a.id = b.id) Do: select a.bla, (select blabla from bar where id = a.id) as blabla from foo a I have reduced a big etl query with about 20 left joins from 2 hours running time down to 7 minutes by this. And before you ask, yes the tables have been properly indexed, it is just that left join performance in sql server is very bad in some situations.

Tostino 10y ago
Did you compare the execution plans of both queries? Without doing that, it's hard to know why the planner did what it did. Maybe statistics were off and it was choosing a worse plan for the left join because it thought it'd be faster, where the sub-select it was forced into what should have been an inferior plan (if statistics were correct), which in reality was faster.
- gigatexal 10y ago
  yes I, too, would like to see more on this. I am skeptical that doing sub queries is faster than joins
gfody 10y ago
Technically the sub select is a semi join which could be optimized differently than left join (and could give different results). If bar.id had a unique constraint though the queries should generate identical plans.
- tyfon 10y ago
  Well, I simplified the example a bit, for the real query bar.id is not unique and has to be constrained by other means. Think an application/case with a table with credit scoring variables where each variable has a case id and a variable type id, so i had to join the same table multiple times with different criteria. But replacing all of the left joins here with subselects did reduce the query time by an insane amount. However I have seen this behaviour with unique indexed left joins too just not so extreme. And for comparison (i have the same schema in my analytics postgresql database), the exact same query here takes about 1.5 minutes with left joins.
  - dsp1234 10y ago
    The two statements, as mentioned by gfody, are not equivalent. For the first statement, there can be any number of output rows based on the join condition. For the second statement, by definition, there can only be output row per input row in foo. That's a lot more information that the query planner can use to make optimizations.
    - tyfon 10y ago
      The table _does_ have a unique index on the combination of caseid and type, but I'm not sure if that is relevant.
      - dsp1234 10y ago
        It's possible, as the other commenters mentioned, it would come down to reviewing the query plan. I was just trying to address the two parts of your original comment, which were "is very baffeling" and "left join performance in sql server is very bad in some situations.". Hopefully my answer helped to un-baffel a little.
koolba 10y ago
You've got this backwards. Assuming the join brings back at most one row (i.e. The join key is a unique key) the left join is better because it can be optimized differently. The sub select must be done as a nested loop but the join can be done via a hash join. For a full data set (not just one of selected) it'd be more efficient. Simply count the net logical IOs.
- tyfon 10y ago
  That is why I think this is baffeling behaviour by sql server since in certain queries you can get an order of magnitude faster execution by dropping left join and using subselect. It's counter intuitive but try it in a real dataset.
iask 10y ago
This is true. I've done this in production and shaved a lot of times of some queries.

by tyfon 10y ago

Tostino 10y ago

Did you compare the execution plans of both queries? Without doing that, it's hard to know why the planner did what it did. Maybe statistics were off and it was choosing a worse plan for the left join because it thought it'd be faster, where the sub-select it was forced into what should have been an inferior plan (if statistics were correct), which in reality was faster.

gigatexal 10y ago
yes I, too, would like to see more on this. I am skeptical that doing sub queries is faster than joins

gfody 10y ago

Technically the sub select is a semi join which could be optimized differently than left join (and could give different results). If bar.id had a unique constraint though the queries should generate identical plans.

tyfon 10y ago
Well, I simplified the example a bit, for the real query bar.id is not unique and has to be constrained by other means. Think an application/case with a table with credit scoring variables where each variable has a case id and a variable type id, so i had to join the same table multiple times with different criteria. But replacing all of the left joins here with subselects did reduce the query time by an insane amount. However I have seen this behaviour with unique indexed left joins too just not so extreme. And for comparison (i have the same schema in my analytics postgresql database), the exact same query here takes about 1.5 minutes with left joins.
- dsp1234 10y ago
  The two statements, as mentioned by gfody, are not equivalent. For the first statement, there can be any number of output rows based on the join condition. For the second statement, by definition, there can only be output row per input row in foo. That's a lot more information that the query planner can use to make optimizations.
  - tyfon 10y ago
    The table _does_ have a unique index on the combination of caseid and type, but I'm not sure if that is relevant.
    - dsp1234 10y ago
      It's possible, as the other commenters mentioned, it would come down to reviewing the query plan. I was just trying to address the two parts of your original comment, which were "is very baffeling" and "left join performance in sql server is very bad in some situations.". Hopefully my answer helped to un-baffel a little.

koolba 10y ago

You've got this backwards. Assuming the join brings back at most one row (i.e. The join key is a unique key) the left join is better because it can be optimized differently. The sub select must be done as a nested loop but the join can be done via a hash join. For a full data set (not just one of selected) it'd be more efficient. Simply count the net logical IOs.

tyfon 10y ago
That is why I think this is baffeling behaviour by sql server since in certain queries you can get an order of magnitude faster execution by dropping left join and using subselect. It's counter intuitive but try it in a real dataset.

iask 10y ago

This is true. I've done this in production and shaved a lot of times of some queries.