Worktable Source

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?

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?

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

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?

Possibly. Hard to judge. Give it a try.

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

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.

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.

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.

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?