Hacker News Reader

Comment by tyfon | Hacker News Reader

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.

View on HN

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.

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

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.

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.

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.

The table _does_ have a unique index on the combination of caseid and type, but I'm not sure if that is relevant.

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.

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.

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.

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

Reply on news.ycombinator.com