help tuning query, incorrect index usage | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

help tuning query, incorrect index usage

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

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

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.
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
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?
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.
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.
]]>