Why is my intermediate result set so huge?

I'm trying to optimize this stored procedure which involves aggregations from a number of joined tables.

I made a change to one of the views that is joined to make it run significantly faster (using a window function instead of group bys and elminating joins so it returns same results in 4 seconds instead of 33s) but now the proc is taking LONGER to run and I'm at a loss as to why.

avatar image By JNK 76 asked Jul 31, 2013 at 12:15 PM
more ▼
(comments are locked)
10|10000 characters needed characters left

1 answer: sort voted first

Why is my intermediate result set so huge?

The query plan is optimized on the basis that the table-valued function returns one row:

alt text

The optimizer knows that its estimate here might be slightly wrong, so it introduces a table spool to cache the results of the view:

alt text

The reasoning is that evaluating the subtree below the spool is expensive, so caching the results will be a big win if there turns out to be more than one row on the upper input to the nested loops left outer join.

Unfortunately, the estimate of the number of rows to be cached by the spool is also wrong. The estimate is 6327 rows, but there are 78638 rows at runtime:

alt text

Overall, the effect is that the 78,638 rows are read from the table spool 6,143 times, giving a grand total of 483,073,234 rows:

alt text

One way to give the optimizer better information would be to materialize the results of the table-valued function in a temporary table. The optimizer will likely choose a hash or merge outer join instead of nested loops, resulting in a plan that only processes the output of the view once.

sp.png (10.4 kB)
sp.png (12.9 kB)
sp.png (6.7 kB)
sp.png (9.5 kB)
avatar image By SQLkiwi ♦ 6.6k answered Aug 02, 2013 at 02:38 AM
more ▼
(comments are locked)
10|10000 characters needed characters left
Your answer
toggle preview:

Up to 50 attachments (including images) can be used with a maximum of 209.7 MB each and 209.7 MB total.

Follow this question

Topics:

x631
x445
x5
x1

asked: Jul 31, 2013 at 12:15 PM

Seen: 762 times

Last Updated: Aug 02, 2013 at 02:38 AM