Recursive CTE Query / Where clause Help

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
set @sectionID=43199417;

WITH section_hierarchy ([Level], ID, parentID, title, contents, [Root]) 
AS
(
	select	0, s.ID, s.parentID, s.title, s.contents, s.ID
	from sections s
	
	union all
	
	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
)
select *
from section_hierarchy
where [Root]=@sectionID

Open in new window


Any ideas on how I can better structure this CTE to make it a bit more efficient?
LVL 33
raterusAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Brian CroweDatabase AdministratorCommented:
Is it possible you have a circular reference?  If so you can set the MAXRECURSION value to prevent an infinite loop and then troubleshoot by the output.

Here is a link to a post with more information on MAXRECURSION:
http://www.sql-server-helper.com/error-messages/msg-310.aspx
0
raterusAuthor Commented:
Hmm, I don't think maxrecursion is is, though that is definitely good to know.  Even setting that option=1, the query spins away!

For the results I'm querying, I know the data is good, and no circular references, I can't say that's the case across the whole table.  I'm dealing with over 40 million rows in this table, so what concerns me is the first statement "select ... from myreallyhugetable" with no where clause!
0
raterusAuthor Commented:
I think I got it, here it is fixed on my original example.  I thought I had tried this and nothing returned, but it works now

declare @sectionID int
set @sectionID=43199417;

WITH section_hierarchy ([Level], ID, parentID, title, contents) 
AS
(
	select	0, s.ID, s.parentID, s.title, s.contents
	from sections s
        where ID=@sectionID
	
	union all
	
	select	h.[Level] + 1, c.ID, c.parentID, c.title, c.contents
	from sections c
		inner join section_hierarchy h on h.ID=c.parentID
)
select *
from section_hierarchy

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Newly released Acronis True Image 2019

In announcing the release of the 15th Anniversary Edition of Acronis True Image 2019, the company revealed that its artificial intelligence-based anti-ransomware technology – stopped more than 200,000 ransomware attacks on 150,000 customers last year.

sameer2010Commented:
Why are you not having s.ID in the first query? It is processing all rows when the WHERE specified specific ID
0
raterusAuthor Commented:
sameer2010, I'm not sure I understand your comment, I have s.ID in the first query
0
PortletPaulfreelancerCommented:
I believe you have diagnosed the problem.

The first portion of the union all should commence at a single record.
Then it should explore the hierarchy beneath just that in the second part.

As it stood you were recursing through every record of the source I think.
0
PortletPaulfreelancerCommented:
raterus

Thank you, I do try to be clear - not always successfully. Please note that my comment was meant in the context of this question "Basically I go into the query knowing the root ID ..."

So it is relevant here, but you can start the recursion with multiple records - if that is needed (but with too many you are asking for trouble of course).

Cheers,
Paul
0
raterusAuthor Commented:
Thanks for the explanation Paul, every "example" I found never quite made this clear!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.