Worktable Source

GokhanVarol 2013-07-03 19:16:45

What is the cause of the worktable in the plan and can it be avoided?
Thank you

source

SQLkiwi 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.

M-M Merge Join

GokhanVarol 2013-07-04 03:48:45
The root table Trans has CntyCd, BatchDt, BatchSeq unique, there is a left merge join to TransMortgage on CntyCd, BatchDt, BatchSeq and TransMortgage has CntyCd, BatchDt, BatchSeq, MtgSeq unique
I don't understand why this join ends up being many to many, can I force one to many join somehow?
SQLkiwi 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?
GokhanVarol 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
GokhanVarol 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?
SQLkiwi 2013-07-04 04:22:12
Possibly. Hard to judge. Give it a try.
GokhanVarol 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

alt text

SQLkiwi 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.
GokhanVarol 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.
SQLkiwi 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.
GokhanVarol 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?