how can i get rid of this sort spill

RichardBrianSmith 2017-05-15 21:27:41

This query comes from a very detailed SSRS report, and is supposed to be its summary. However, whilst waiting 23 seconds is fine for the "details report" it is not for this "summary report" that only returns 300 rows.

Aaron Bertrand 2017-05-19 16:03:10
I wonder if your estimate on the seek for [RB Organic Ltd$Item Ledger Entry].[ix_RBOrganicLtd_ItemLedgerEntryPostingDate] would be a lot better (but still not perfect) if you changed this criteria:

HAVING (SUM(vl.ActualCost) + SUM(vl.ExpectedCost) <> 0) 
OR (SUM(ile.quantity) <> 0)

To this:

HAVING (SUM(vl.ActualCost + vl.ExpectedCost) + SUM(ile.quantity) <> 0)

(Which is the same semantically, I think, unless you can have negatives for ActualCost or ExpectedCost.)

Getting rid of the OR might take away a layer of guessing, and bring estimates closer to actual.

After that, I might try filtering earlier, or materializing some of the aggregations into their own #temp tables so that they can have their own statistics and so it isn't one big query for the optimizer to try and wrangle.