Get out of bed people, I'm excited!

I've uploaded the actual query plan (1.Original.sqlplan) and it indeed has got the estimated record counts wrong all along the staging table branch and...

Thanks! That hit the nail on the head - if I understand correctly it's forcing the optimiser to realise it's a 1:n join. The plan is quite different but most...

I'm interested in getting to the bottom of it. In fact it's far more interesting than what I'm meant to be doing which is why I'm having difficulty spending the...

I really think...

Thanks. The estimated plan for just the SELECT is missing everything after the loop join (including stream aggregate), but everything before (indexes used etc.) is exactly the same.

Tomorrow I...

Those are very general benefits but it sounds like you've convinced yourself anyway.

If you are talking about having a seperate detail and summary table then it's not vertical partitioning...

I did try running just the SELECT query early on in the piece and also when I was trying to get the Actual plan. In both cases I stopped it...

I was afraid you were going to say that - so the only way to get the actual plan is to run this interactively in SSMS with that option ticked?...

Are you saying that actual plans are not in that table? or are...

Why do want to divide the load? Do you have a performance or design issue requiring this? For population of summary table you could also use views, triggers and/or a...

OK I extracted what I think is the actual plan from sys.dm_exec_query_plan and it was identical to the estimated one that I uploaded, so we can assume the uploaded query...

The stored procedure (or application layer object) that creates the record takes care of that. For example you create the record in the first table, use SCOPE_IDENTITY() to get the...

Thanks for your interest and suggestions everyone.

I have uploaded usage stats.

The query has not completed for a couple of days, and we have rebooted the server since then so I...

OK, I have reposted an unsanitised query, the sqlplan file, the DDL, and a picture of part of the query plan.

I can try clustering SE on RowNum_Start and RowNum_End but...

Sure, I'll do it first thing tomorrow at work, thats in about 11 hours, thanks for your interest!