correlated sub queries ? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

correlated sub queries ?

Author

Topic

esermeh
Starting Member

3 Posts

Posted - 2006-04-12 : 00:51:47

I was trying to optimize this query on adventureworks. maybe it is possible. right now It is using a correlated sub query.

-- to find employees hired at the same date.
-- try to make it more efficient
-- this one gives correct result
-- this is a correlated quer because of that it gives a slow hash join.
-- creating an index on hiredate impoves it 75%
--------------------------------------

USE adventureworks
SELECT E.EmployeeID, E.HireDate FROM HumanResources.Employee E
WHERE E.HireDate IN
(
SELECT EI.HireDate FROM HumanResources.Employee EI
WHERE EI.employeeID !=E.employeeID
)
ORDER BY E.EmployeeID

mwjdavidson
Aged Yak Warrior

735 Posts

Posted - 2006-04-13 : 09:55:59

Try:

USE adventureworks
GO
SELECT E.EmployeeID, 
       E.HireDate
FROM   HumanResources.Employee AS E
JOIN 
(
  SELECT   E.HireDate
  FROM     HumanResources.Employee AS E
  GROUP BY E.HireDate
  HAVING   COUNT(DISTINCT E.EmployeeID) > 1
) AS tmp
ON tmp.HireDate = E.HireDate
ORDER BY E.EmployeeID

Mark