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.
By RichardBrianSmith 97 asked May 15 at 09:27 PM
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:
(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.
By Aaron Bertrand ♦ 1.7k answered May 19 at 04:03 PM