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