how can i get rid of this sort spill

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.

Plan.pesession (337.1 kB)
avatar image By RichardBrianSmith 97 asked May 15 at 09:27 PM
more ▼
(comments are locked)
10|10000 characters needed characters left

1 answer: sort voted first

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.

avatar image By Aaron Bertrand ♦ 1.7k answered May 19 at 04:03 PM
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



asked: May 15 at 09:27 PM

Seen: 53 times

Last Updated: May 19 at 04:03 PM