May 24

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



SQL Server

You may also like

{"email":"Email address invalid","url":"Website address invalid","required":"Required field missing"}

Subscribe to our newsletter now!

Get instant access to the Master C# 9 webinar to learn all the new exciting C# 9 features quickly.
Get regular videos and news on C# and .NET and offers on products and services to master C#.

We will collect, use and protect your data in accordance with our Privacy Policy.