Do you want to kill SQL Server performance? Use correlated subqueries.

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

2016-05-24 22_35_20-Store

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

2016-05-24 22_45_09-New notification

 

Permanent link to this article: https://www.productivecsharp.com/2016/05/killing-sql-server-performance/


Warning: in_array() expects parameter 2 to be array, null given in /home/andreaa9/public_html/wp-content/themes/graphene/inc/plugins.php on line 92
>