Really baffling why the inserts are so slow during the run...

Can you try switching the index on #results to a clustered one?

CREATE CLUSTERED INDEX #IXS ON #RESULTS(LEVEL);

Have you got a select that produces more rows? I hadn't spotted that one was only 25 rows, which is far too small a sample.

We need one that produces about...

And the same details for that query inserting into tempdb

select id,ParentID,ChildID

INTO #test

FROM yourTable

where parentID=blah....

so can you try a simple select from your table for a parent that is at level 2 or 3?

use variables to exclude any network latency e.g.

select @id=id,@parent=ParentID,@child=ChildID

FROM yourTable

where parentID=blah....

This...

Hmmm, that still looks terrible, doesn't it...

Let's hope your laptop is just very slow to write to disk!

Ok, that looks interesting.

You can see that each insert is taking a very long time.

Can you try creating a permanent table somewhere other than tempdb for the results - instead...

That actual execution plan was for a run that only pulled top level items, no children at all, so we can't tell much from that.

Can you run it again for...

And the statistics???

Thanks for the plan, but that seems to be an estimated plan, not an actual plan?

Can you produce a plan from an actual execution please?

And also include SET STATISTICS_TIME ON;...

haiao2000 (10/10/2012)

---

I really appreciate that you spend time write up this great post. I am still trying to understand how your code works myself :-).

The part I don't understand is...

Hi there, I would like to offer you my own twist on this using the "identity hack".

The outline of the logic is to replace the recusive cte with a recursive...

opc.three (10/9/2012)

---

You do make it seem simple,...

I have a question - how are you supposed to deploy from DEV->QA->PROD with embedded credentials/data sources?

They will surely need to be different for each?

Also, wonder about the security...

JimS-Indy (10/9/2012)

---

Yeah, so I'm getting the impression the ASP.NET app must accept all comers, authenticate them, then use the Network Service to access the db. I can live with that....

Ah good, glad to help:-D