I have a large table (millions of rows), with a parent-child hierarchy (ID->parentID)
I'm trying to use a CTE to query a handful of records out. Basically I go into the query knowing the root ID, and I need to query that record, and any records that fall under it.
The problem I have is that my queries take forever to run, in fact I can't get usable results from them because they take so long! It works ok when there aren't many records to query, but when I should get hundreds of results, it never stops.
here's what I have
declare @sectionID int
WITH section_hierarchy ([Level], ID, parentID, title, contents, [Root])
select 0, s.ID, s.parentID, s.title, s.contents, s.ID
from sections s
select h.[Level] + 1, c.ID, c.parentID, c.title, c.contents, [Root]
from sections c
inner join section_hierarchy h on h.ID=c.parentID
Any ideas on how I can better structure this CTE to make it a bit more efficient?