Worktable Source
2013-07-03 19:16:45
What is the cause of the worktable in the plan and can it be avoided?
Thank you
Thank you
2013-07-04 00:12:30
The worktable is used by the many-to-many merge joins. It can be avoided by making the merge joins one-to-many.
2013-07-04 03:54:05
Presumably, duplicates are introduced at the join to TransLot. That is the last join that is 1-M in sequence. Are you missing a foreign key?
2013-07-04 04:05:09
Of course I did 🙁 at Translot, the primary key on that table has also a LotSeq. Converting a fairly large query a user asked me to, I missed this one. Thank you
2013-07-04 04:10:57
The #Lender table lookup, it has only 2 columns with a clustered index. Is nested loops faster to look through it a hash would be better?
2013-07-04 04:22:12
Possibly. Hard to judge. Give it a try.
2013-07-04 10:45:52
Hash spilled with that few data, I would not think it would have. Why would that be?
Does the hashtable build only one for the lifecycle or it's build for each iteration under cross apply for each thread etc?
Thanks
Does the hashtable build only one for the lifecycle or it's build for each iteration under cross apply for each thread etc?
Thanks
2013-07-04 23:39:10
The hash table is rebuilt for every iteration (couldn't work any other way, if you think about it). It is typically sized based on the expected average number of rows across all iterations. With a large variance between iterations, spills are all but inevitable.
2013-07-05 00:16:10
I thought the hash table builds everything on the source table and it would end up building the same hash from the same full source in every iteration. I must have misunderstood how hash tables work.
2013-07-05 00:24:20
No, that's right. The hash table has to be rebuilt on every iteration because correlated parameters might have changed, or the table might have changed. It hasn't, of course, in your case, but in general it could.
2013-07-05 00:34:16
When you said "With a large variance between iterations, spills are all but inevitable." I guess that does not apply against the #lender lookup table hash built in this queryplan, it should all spill or none spill?
I don't understand why this join ends up being many to many, can I force one to many join somehow?