Post reply

Using INNER JOINS as a filter technique, where should I index?

  • April 17, 2008 at 1:47 pm

    #121147

    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

    #804062

    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