SA0128 : Avoid using correlated subqueries. Consider using JOIN instead

Correlated subqueries can cause performance issues, as they are executed for every row in the outer query.

Description

A correlated subquery can lead to performance problems in SQL Server. This issue arises because a correlated subquery is executed repeatedly for every row in the outer query. This can significantly slow down query performance, especially with large datasets, unless the optimizer transforms it into a more efficient join.

For example:

SQL
Copy
1-- Example of a correlated subquery
2SELECT e.EmployeeID, e.Name
3FROM Employees e
4WHERE e.Salary > (SELECT AVG(Salary) FROM Salaries s WHERE s.DepartmentID = e.DepartmentID);

In this example, the SELECT AVG(Salary) subquery is correlated with the outer query by e.DepartmentID. The subquery executes for each employee, leading to potential performance degradation due to repetitive calculations.

  • Increased execution time due to repeated subquery evaluations for each outer row.

  • Higher resource consumption, affecting database server performance.

`

How to fix

Consider using a self-join instead of a correlated subquery to improve query performance and consistency.

Follow these steps to address the issue:

  1. Identify the subquery in your SQL query that is causing performance issues due to its correlation with the outer query.

  2. Convert the correlated subquery into an equivalent self-join to allow SQL Server to utilize more efficient join operations such as hash or merge joins.

  3. Ensure the join conditions correctly replicate the logic of the original subquery for accurate results.

For example:

SQL
Copy
1-- Original correlated subquery
2SELECT e.EmployeeID, e.Name
3FROM Employees e
4WHERE e.Salary > (SELECT AVG(Salary) FROM Salaries s WHERE s.DepartmentID = e.DepartmentID);

Can be rewritten using a self-join as follows:

SQL
Copy
1-- Rewritten query using a self-join
2SELECT DISTINCT e.EmployeeID, e.Name
3FROM Employees e
4JOIN (
5    SELECT DepartmentID, AVG(Salary) AS AvgSalary
6    FROM Salaries
7    GROUP BY DepartmentID
8) avgSalaries ON e.DepartmentID = avgSalaries.DepartmentID
9WHERE e.Salary > avgSalaries.AvgSalary;

Scope

The rule has a Batch scope and is applied only on the SQL script.

Parameters
Name Description Default Value
IgnoreCorrelatedQueriesInsideExistsClause

Ignore correlated queries inside EXISTS clause.

yes
Remarks

The rule requires Analysis Context. If context is missing, the rule will be skipped during analysis.

Effort To Fix
1 hour per issue.
Categories

Performance Rules, Bugs

Additional Information
Example Test SQL
SQL
Copy
 1SELECT DISTINCT c.LastName, c.FirstName, e.BusinessEntityID 
 2FROM Person.Person AS c JOIN HumanResources.Employee AS e
 3ON e.BusinessEntityID = c.BusinessEntityID 
 4WHERE 5000.00 IN
 5    (SELECT Bonus
 6    FROM Sales.SalesPerson sp
 7    WHERE e.BusinessEntityID = sp.BusinessEntityID) ;
 8
 9SELECT 
10        F.custCode, 
11        F.CustLocNum, 
12        (SELECT COUNT(*) FROM DimFacilitySupplier S WHERE S.FacilityKey = F.FacilityKey) AS Cnt
13FROM #DimFacility F;
14
15SELECT 
16        F.custCode, 
17        F.CustLocNum
18FROM DimFacility F
19WHERE F.FacilityKey = ANY (SELECT S.FacilityKey FROM DimFacilitySupplier S);
20
21SELECT 1
22FROM dbo.Table_1 t1
23WHERE N'Hi' IN
24  (SELECT t2.testdata1
25    FROM dbo.Table_2 t2
26    WHERE t1.testkey = t2.testkey);
Analysis Results
  Message Line Column
1 SA0128 : Avoid using correlated subqueries. Consider using JOIN instead. 5 5
2 SA0128 : Avoid using correlated subqueries. Consider using JOIN instead. 12 2
3 SA0128 : Avoid using correlated subqueries. Consider using JOIN instead. 24 3
See Also

Other Resources

Analysis Rules