April 17, 2008 at 1:47 pm
I need help tweaking out a little more performance from a reporting table. Here's a simplification of my problem.
My fact table looks like this
(Sales_Person_key, State_Code, Product_key, Location_Owned_By_Key, Period_MM, Total_Sales)
I have a stored procedure that does a
"SELECT Product_key, sum(Total_Sales)" from this fact table and inner join it with 3 temp tables... 1 for sales_persons, 1 for states, and 1 for products.
These temp tables contain the selections from a very complex filter interface.
I have an index on each of the filter fields in the fact table (that I use on the INNER JOIN), and a primary key on each of the temp tables.
Is there anything in this design that would make the performance better? A query takes about 4 seconds when the fact table is about 1 million rows. Sometimes my temp tables have couple hundred rows (either many sales people, products, etc.)
Any ideas?
Thanks in advance!
Sam
Here's my end sql:
SELECT
S.product_key,
s.Location_Owned_By_Key,
Sum(S.Total_Sales),
FROM Sales_Summary AS S WITH (NOLOCK)
INNER JOIN #TblOfProducts p WITH (NOLOCK) ON s.Product_Key = p.product_key
INNER JOIN #TblOfSalesPersons AS sp WITH (NOLOCK) ON sp.Sales_Person_key = s.Sales_Person_key
INNER JOIN #TblOfStates SC WITH (NOLOCK) ON SC.State_Code = S.State_Code
WHERE Period_MM BETWEEN 200610 AND 200709
GROUP BY S.product_key, s.Location_Owned_By_Key
April 17, 2008 at 6:01 pm
What does your execution plan show? Is there an table or index scan on Sales_Summary?
Even though you have an index on each of the filter fields in the fact table it's possible only one is being used. You may be better served by one index that has all of the filters
create index ix_a on Sales_Summary (product_key, Sales_Person_key, State_Code, Period_MM )
or, even better, a covering index with all of the fields referenced by your query to avoid RID lookups
create index ix_b on Sales_Summary (product_key, Sales_Person_key, State_Code, Period_MM, Location_Owned_By_Key, Total_Sales)
Bear in mind that this can negatively impact updates, inserts, and deletes.
See if switching from INNER JOINS to EXISTS helps
SELECT
S.product_key,
s.Location_Owned_By_Key,
Sum(S.Total_Sales)
FROM Sales_Summary AS S WITH (NOLOCK)
where exists (select 1 from #TblOfProducts p WITH (NOLOCK) where s.Product_Key = p.product_key)
and exists (select 1 from #TblOfSalesPersons AS sp WITH (NOLOCK) where sp.Sales_Person_key = s.Sales_Person_key)
and exists (select 1 from #TblOfStates SC WITH (NOLOCK) where SC.State_Code = S.State_Code)
WHERE Period_MM BETWEEN 200610 AND 200709
GROUP BY S.product_key, s.Location_Owned_By_Key
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply