SQL Server Performance

help tuning query, incorrect index usage

Discussion in 'T-SQL Performance Tuning for Developers' started by rklimes, Jul 10, 2007.

  1. rklimes New Member

    I have a poorly performing view that consists on 3 union-ed sql statement. one of them takes approx 5 min to return results. The execution plan shows that 43% of the cost is attributed to returning G.colG1 and this is done using a clustered index scan on table_G. This clustered index contains both G.CASE_ID and G.BUSINESS_UNIT so I would expect it to do an index seek. I would assume that by doing a seek it would improve the performance. Any suggestions would be appreciated. Thanks.

    below is query in question


    SELECT A.colA1,
    A.colA2,
    A.colA3,
    A.colA4,
    A.colA5,
    A.colA6,
    A.colA7,
    A.colA8,
    A.colA9,
    B.BUSINESS_UNIT,
    B.CASE_ID,
    C.colC1,
    C.colC2,
    D.colD1,
    CONVERT(CHAR(10), D.colD2, 121) AS colD2,
    D.colD3,
    E.colE1,
    E.colE2,
    E.colE3,
    MIN(F.colF1) AS colF1,
    G.colG1,
    K.colK1,
    K.colK2,
    L.colL1
    FROM table_A AS A
    INNER JOIN table_B AS B
    ON A.colA2 = B.colB2
    AND A.colA3 = B.colB3
    AND A.colA4 = B.colB4
    AND A.colA5 = B.colB5
    AND A.colA6 = B.colB5
    INNER JOIN table_C AS C
    ON B.CASE_ID = C.CASE_ID
    AND B.BUSINESS_UNIT = C.BUSINESS_UNIT
    INNER JOIN table_D AS D
    ON B.CASE_ID = D.CASE_ID
    AND B.BUSINESS_UNIT = D.BUSINESS_UNIT
    INNER JOIN table_E AS E
    ON B.CASE_ID = E.CASE_ID
    AND B.BUSINESS_UNIT = E.BUSINESS_UNIT
    INNER JOIN table_F AS F
    ON B.CASE_ID = F.CASE_ID
    AND B.BUSINESS_UNIT = F.BUSINESS_UNIT
    INNER JOIN table_G AS G
    ON B.CASE_ID = G.CASE_ID
    AND B.BUSINESS_UNIT = G.BUSINESS_UNIT
    INNER JOIN table_K AS K
    ON C.colC3 = K.colK3
    INNER JOIN table_L AS L
    ON K.colK4 = L.colL4
    WHERE (A.colA7 <= 2)
    AND (F.colF1 IS NOT NULL)
    AND (E.colE4 <> 'P')
    GROUP BY A.colA1,
    A.colA2,
    A.colA3,
    A.colA4,
    A.colA5,
    A.colA6,
    A.colA7,
    A.colA8,
    A.colA9,
    B.BUSINESS_UNIT,
    B.CASE_ID,
    C.colC1,
    C.colC2,
    D.colD1,
    D.colD2,
    D.colD3,
    E.colE1,
    E.colE2,
    E.colE3,
    G.colG1,
    K.colK1,
    K.colK2,
    L.colL1
  2. rklimes New Member

    Here is something that, in my mind, make this more confusing. When I removed all the columns and tables that are not necessary to return G.colG1 it does use an index seek on table_G instead of index scan. If the join to table_G is the same and there is no where clause in table_G, shouldn't the execution be the same while retrieving G.colG1


    selectA.colA1,
    A.colA2,
    A.colA3,
    A.colA4,
    A.colA5,
    A.colA6,
    A.colA7,
    A.colA8,
    A.colA9,
    B.BUSINESS_UNIT,
    B.CASE_ID,
    E.colE1,
    E.colE2,
    E.colE3,
    G.colG1
    FROM table_A AS A
    INNER JOIN table_B AS B
    ON A.colA2 = B.colB2
    AND A.colA3 = B.colB3
    AND A.colA4 = B.colB4
    AND A.colA5 = B.colB5
    AND A.colA6 = B.colB5
    INNER JOIN table_E AS E
    ON B.CASE_ID = E.CASE_ID
    AND B.BUSINESS_UNIT = E.BUSINESS_UNIT
    INNER JOIN table_F AS F
    ON B.CASE_ID = F.CASE_ID
    AND B.BUSINESS_UNIT = F.BUSINESS_UNIT
    INNER JOIN table_G AS G
    ON B.CASE_ID = G.CASE_ID
    AND B.BUSINESS_UNIT = G.BUSINESS_UNIT
    WHERE (A.colA7 <= 2)
    AND (F.colF1 IS NOT NULL)
    AND (E.colE4 <> 'P')
    GROUP BY A.colA1,
    A.colA2,
    A.colA3,
    A.colA4,
    A.colA5,
    A.colA6,
    A.colA7,
    A.colA8,
    A.colA9,
    B.BUSINESS_UNIT,
    B.CASE_ID,
    E.colE1,
    E.colE2,
    E.colE3,
    G.colG1
  3. Adriaan New Member

    quote:When I removed all the columns and tables that are not necessary to return G.colG1 it does use an index seek on table_G instead of index scan.
    Wild guess: there is an index on table_G, where colG1 is the first column with the 'removed' columns after it?

    When your column list consists of columns that are covered by an index, then SQL can (and does) read the values from the index. For that it probably needs to do an index scan (which should still be faster than a table scan).

    I presume there is a multi-column FK realtionship between table_A and table_B. If table_A has an identity column, then you could repeat that on table_B, and use it as the FK, or at least use it for joining.
  4. rklimes New Member

    quote:Wild guess: there is an index on table_G, where colG1 is the first column with the 'removed' columns after it?

    There is only one index on table_G , UNIQUE CLUSTERED INDEX with CASE_ID and BUSINESS_UNIT.


    quote:I presume there is a multi-column FK realtionship between table_A and table_B. If table_A has an identity column, then you could repeat that on table_B, and use it as the FK, or at least use it for joining

    There are no Primary-Foreign keys in DB (peoplesoft application related) but yes the columns in table_a that are used to join to table_b would be primary-foreign keys if this was conventional design.

    Note, I have reindexed and updated statistic on table_g and table_b
  5. Adriaan New Member

    Hm, on second thoughts ... perhaps the most significant thing you removed from the query was MIN(F.colF1) - aggregate calculations have their own requirements.

    Another thing that catches my eye, and that is even more disturbing than the FK between A and B: the repeated use of CASE_ID and BUSINESS_UNIT as an FK. But you're stuck with this design, right?
  6. rklimes New Member

    thank for the replies Adriaan. I didn't realize I had removed a column from the select list that was in a table still being used in the joins. However, after I put MIN(F.colF1) back into the select list it is still using an index seek to return G.colG1. And Yes unfortunately I am stuck with the design, at least to to point of what is being used as a "key". In peoplesoft, keys are not implemented in the database but simulated using UNIQUE CLUSTERED INDEXES.
  7. Adriaan New Member

    In that case I wouldn't worry too much about getting an index scan instead of index seek. It could be worse: you could have a table scan.

Share This Page