Why is query grabbing all records from FactSalesTransaction And FactSalesPayment

Zane 2013-03-05 16:32:10


This report is running extremely slow (20+ minutes).

So I ran this query plan through PlanExplorer and saw it was pulling all of the data from two of my fact tables instead of filtering out just that store and that day.

When I replaced the @xmlTemp table variable with simple, hard-coded date range parameters, the query ran in 4 seconds instead of 20+ minutes.

Aaron Bertrand 2013-03-05 17:38:33
Some very brief initial suggestions from discussions elsewhere:

  1. Try creating @xmlTemp as a #temp table with a clustered index on (StartDate, EndDate) instead of a table variable. This may provide SQL Server with more accurate stats information (though questionably useful if the table only has one row).
  2. If @xmlTemp has only one row always, use two variables instead of a table in the first place.
  3. Try adding the (RECOMPILE) option to the statement, especially if you convert to variables instead of the #temp table (parameter sniffing).
  4. Try using OPTION (MAXDOP 1) – parallelism is definitely in use, and at the lower end the threads seem partially imbalanced. I wonder if parallelism is helping or hurting here – can't hurt to test duration with and without.
  5. You may need to perform more rigorous stats updates. A lot of these estimates are way, way off.
  6. Remove the DISTINCTs. For this set of columns I find it hard to believe this is eliminating any duplicates, but the optimizer has to work as if there are dupes to remove.
  7. Consider using Table-Valued Parameters (TVPs) instead of shredding XML for the different companies / stores.