November 12, 2009 at 12:38 am
Hey guys
Current index definition:
CREATE NONCLUSTERED INDEX [IX_tblX_X1X2X3X4X5] ON [dbo].[tblX]
(
X1 ASC, -- VARCHAR(8)
X2 ASC, -- VARCHAR(6)
X3 ASC, -- VARCHAR(3)
X4 ASC, -- VARCHAR(6)
X5 ASC -- VARCHAR(2)
)
INCLUDE (X6, X7, X8, X9) -- These are floats
WITH (PAD_INDEX = ON, FILLFACTOR = 80, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
The query below results in the attached execution plan with the suggested index which I've listed below the query.
SELECT
[dbo].[tblX].[X1],
[dbo].[tblX].[X5],
[dbo].[tblX].[X4],
[dbo].[tblX].[X2],
[dbo].[tblX].[X3]
FROM
[dbo].[tblX] LEFT OUTER JOIN
[dbo].[tbl_Others]
ON [dbo].[tblX].[X1] = [dbo].[tblOther].[X1]
WHERE
[dbo].[tblOther].[OtherColumn] IS NULL AND
([dbo].[tblX].[X4] >= @fromX4 AND
[dbo].[tblX].[X4] <= @toX4
) AND
([dbo].[tblX].[X3] >= @fromX3 AND
[dbo].[tblX].[X3] <= @toX3
) AND
([dbo].[tblX].[X2] >= @fromX2 AND
[dbo].[tblX].[X2] <= @toX2
) AND
([dbo].[tblX].[X5] = 'A' OR
[dbo].[tblX].[X5] = 'B'
)
ORDER BY
[dbo].[tblX].[X1]
CREATE NONCLUSTERED INDEX [<Name of Missing Index, sysname,>]
ON [dbo].[tblX] ([X5],[X3],[X4],[X2])
INCLUDE ([X1])
GO
Is my index not a covering index for this specific query since it is defined on all the columns in the WHERE clause as well as including X1 in it's definition?
Thank you
November 12, 2009 at 1:03 am
GDI Lord (11/12/2009)
Hey guysCurrent index definition:
CREATE NONCLUSTERED INDEX [IX_tblX_X1X2X3X4X5] ON [dbo].[tblX](
X1 ASC, -- VARCHAR(8)
X2 ASC, -- VARCHAR(6)
X3 ASC, -- VARCHAR(3)
X4 ASC, -- VARCHAR(6)
X5 ASC -- VARCHAR(2)
)
INCLUDE (X6, X7, X8, X9) -- These are floats
WITH (PAD_INDEX = ON, FILLFACTOR = 80, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
The query below results in the attached execution plan with the suggested index which I've listed below the query.
SELECT[dbo].[tblX].[X1],
[dbo].[tblX].[X5],
[dbo].[tblX].[X4],
[dbo].[tblX].[X2],
[dbo].[tblX].[X3]
FROM
[dbo].[tblX] LEFT OUTER JOIN
[dbo].[tbl_Others]
ON [dbo].[tblX].[X1] = [dbo].[tblOther].[X1]
WHERE
[dbo].[tblOther].[OtherColumn] IS NULL AND
([dbo].[tblX].[X4] >= @fromX4 AND
[dbo].[tblX].[X4] <= @toX4
) AND
([dbo].[tblX].[X3] >= @fromX3 AND
[dbo].[tblX].[X3] <= @toX3
) AND
([dbo].[tblX].[X2] >= @fromX2 AND
[dbo].[tblX].[X2] <= @toX2
) AND
([dbo].[tblX].[X5] = 'A' OR
[dbo].[tblX].[X5] = 'B'
)
ORDER BY
[dbo].[tblX].[X1]
CREATE NONCLUSTERED INDEX [<Name of Missing Index, sysname,>]ON [dbo].[tblX] ([X5],[X3],[X4],[X2])
INCLUDE ([X1])
GO
Is my index not a covering index for this specific query since it is defined on all the columns in the WHERE clause as well as including X1 in it's definition?
Thank you
Simple answer, no.
Looking at your query, the suggested index is better. Your index starts with X1 where X5 appears to be more selective, with X2, X3, X4 being ranges. I'd implement the suggested index to support this query.
November 12, 2009 at 1:34 am
And the complex answer? 😉
Actually, X5 only has two values, 'A' and 'B'. Unless using only those two values is better for this specific query, although it goes against what Josef Richberg says in his article on statistics and indices titled "Index Primer - Just what statistics are kept?" over here.
November 12, 2009 at 2:33 am
The index that you have there is completely useless for seeks. The leading column is not used in the where clause at all. That query will execute with an index scan at best. If you want seeks, you'll need to reorder the index columns
Columns used for equality matches should go on the left and columns for inequalities further to the right. Columns used for neither should go in INCLUDE or on the absolute right of the index key. Seeks aren't possible past the first column used for inequality match.
http://sqlinthewild.co.za/index.php/2009/02/06/index-columns-selectivity-and-inequality-predicates/
It's not all about selectivity. There's far more than just that involved in selecting the order of columns. The first part of that series may be of interest too. http://sqlinthewild.co.za/index.php/2009/01/19/index-columns-selectivity-and-equality-predicates/
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 12, 2009 at 10:36 am
GDI Lord (11/12/2009)
And the complex answer? 😉Actually, X5 only has two values, 'A' and 'B'. Unless using only those two values is better for this specific query, although it goes against what Josef Richberg says in his article on statistics and indices titled "Index Primer - Just what statistics are kept?" over here.
If X5 only has the values 'A' and 'B' why are you testing for these values in the WHERE clause of your query? If those are the only values, that part of the query will always be true.
Not sure about what Josef Richberg has to say in his article, but I have found that looking at indexes as you would a telephone book seems to make things easier. It may not always be the correct metaphor, but seems to work well.
November 13, 2009 at 2:39 am
Thanks Gail, thanks Lynn.
Lynn Pettis (11/12/2009)
If X5 only has the values 'A' and 'B' why are you testing for these values in the WHERE clause of your query? If those are the only values, that part of the query will always be true.
Because the client could decide to include 'C' or 'D' or 'E' records sometime in the future.
November 13, 2009 at 6:44 am
GDI Lord (11/13/2009)
Thanks Gail, thanks Lynn.Lynn Pettis (11/12/2009)
If X5 only has the values 'A' and 'B' why are you testing for these values in the WHERE clause of your query? If those are the only values, that part of the query will always be true.Because the client could decide to include 'C' or 'D' or 'E' records sometime in the future.
If the client adds those records, would they be excluded from the is query?
November 13, 2009 at 7:02 am
Not necessarily.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply