Previous Thread:   SqlDependency and Notifications

2/6/2006 11:32:22 AM    Re: CTE Optimization
Yes -- why don't you put your filter in the recursive query instead?  
  
--  
  
Adam Machanic  
  
Pro SQL Server 2005, available now  
  
http://www.apress.com/book/bookDisplay.html?bID=457  
  
--  
  
"Leila" <Leilas@hotpop.com> wrote in message  
  
news:uAXNjWzKGHA.740@TK2MSFTNGP12.phx.gbl...



2/6/2006 7:23:36 PM    CTE Optimization
Hi,  
  
Below is the traditional use of CTE for retrieving nodes of a tree. My  
  
question is that when I use a condition like 'where lvl<=2' the execution  
  
plan shows that filtering the result is the final phase of execution. Does  
  
it mean that if I have a deep level of hierarchies in my table, the  
  
performance will not be good? Will it prepare all of the records and then  
  
filters the result?  
  
Thanks in advance,  
  
Leila  
  
--------------------  
  
USE Northwind  
  
GO  
  
WITH MyChart(EmployeeID,EmpName,BossID,BossName,lvl) AS  
  
(SELECT EmployeeID,FirstName,EmployeeID,FirstName, 1  
  
FROM Employees WHERE EmployeeID=2  
  
UNION ALL  
  
SELECT Emp.EmployeeID,Emp.FirstName,MyChart.EmployeeID,  
  
MyChart.EmpName, MyChart.lvl+1  
  
FROM Employees Emp INNER JOIN MyChart  
  
ON Emp.ReportsTo=MyChart.EmployeeID  
  
)  
  
SELECT * FROM MyChart  
  
where lvl<=2