Search code examples
sqlsql-serverperformancesubquerycorrelated-subquery

Performance issue with correlated SQL Server query

I have a query that looks like the following, it is just entirely too slow and I don't know how to speed it up. This query is currently correlated. Will a temp table to then join solve this?

SELECT 
    e.ID, e.Name
FROM
    Employees e
WHERE
    e.Salary > (SELECT AVG(e2.Salary)
                FROM Employees e2
                WHERE e2.DepartmentID = e.DepartmentID)
Solution

  • Please try following SQL query

    with cte as (
select
    *,
    AVG(Salary) over (partition by DepartmentID) average
from employees
)
select * from cte where Salary > average

    Here you will see that I used SQL Average aggregation function with Partition By clause In order to use it I preferred a SQL CTE expression