Performance tuning Part -6-correlated sub-queries

Avoid correlated sub-queries. Correlated sub-queries cause more reads and increase the overall execution time.

Demo

Note: AdventureWorks2017 database has been used for this demo

SET STATISTICS IO ON
--Script1 (Correlated subquery)
SELECT D.SalesOrderID

       ,D.ProductID

       ,(

              SELECT OrderDate

              FROM Sales.SalesOrderHeader H

              WHERE H.SalesOrderID = D.SalesOrderID

              ) AS OrderDate

       ,(

              SELECT DueDate

              FROM Sales.SalesOrderHeader H

              WHERE H.SalesOrderID = D.SalesOrderID

              ) AS DueDate

FROM Sales.SalesOrderDetail D

GO
--Script2(Join)
SELECT D.SalesOrderID

       ,D.ProductID

       ,H.OrderDate

       ,H.DueDate

FROM Sales.SalesOrderDetail D

LEFT JOIN Sales.SalesOrderHeader H ON H.SalesOrderID = D.SalesOrderID

First script is using correlated sub-queries while second script is using join

Both the queries are returning the same data but Logical reads for  Sales.SalesOrderHeader table in first query are too high.

Author: Sauras Pandey

Sauras Pandey is an MCP, SQL Server DBA/Architect with 9+ years of experience working in a variety of environments

Leave a Reply

Your email address will not be published.