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,BossNa
me,lvl) AS
(SELECT EmployeeID,FirstName,EmployeeID,FirstNam
e, 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<=2Yes -- 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...
> 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,BossNa
me,lvl) AS
> (SELECT EmployeeID,FirstName,EmployeeID,FirstNam
e, 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
>