Jeremy Miller Jeremy Miller - 3 months ago 7
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?

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

Answer

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

Comments