Jeremy Miller Jeremy Miller - 2 months ago 4
SQL Question

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?

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


Please try following SQL query

with cte as (
    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