Today I learnt an important lesson: you should never ever use correlated subqueries in SQL Server.
If you want to kill performance, that is a very good way to do it.
This is a portion of the query I was working on (in bold my changes).
The simple addition of this correlated sub-query made the executing time three times worse! The overall query took more than a minute instead of the usual 20 seconds. Wow!
If you search on Google you realize that this using correlated subqueries is really bad idea:
SQL server performance – Death by correlated subqueries
How to fix it?
Don’t use correlated subqueries 🙂
In my case, I converted it using a JOIN clause. (Note that the logic is different as I decided to change the behavior during development but you get the idea).