March 28, 2004 at 7:28 pm
If I have 943 rows in a table (setup below) and perform the following select through query analyzer the execution plan shows that the query is picking up the correct index (index seek).
select CodeInstanceID from DT_DateTest where ExpiryDate is null
However, using 943 rows and adding an extract character to the insert statements will result in a table scan through query analyzer.
Any ideas how to get SQL server to use the proper index without a hint?
-- Create the table
drop table DT_DateTest
GO
CREATE TABLE [DT_DateTest] (
[CodeInstanceID] [int] IDENTITY (1, 1) NOT NULL ,
[Name] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[EffectiveDate] [datetime] NULL ,
[ExpiryDate] [datetime] NULL
) ON [PRIMARY]
GO
-- put some data into the table
--SET nocount ON
DECLARE @l_insert INT
DECLARE @l_toggle INT
set @l_insert = 0
set @l_toggle = 0
WHILE @l_insert < 943
BEGIN
if (@l_toggle = 0)
Begin
insert DT_DateTest values ('TestA' + cast(@l_insert as varchar(5)),getdate(),NULL)
set @l_insert= @l_insert + 1
set @l_toggle = 1
End
Else
Begin
insert DT_DateTest values ('TestB' + cast(@l_insert as varchar(5)),getdate(),getdate())
set @l_insert= @l_insert + 1
set @l_toggle = 0
End
END
GO
-- Create index
CREATE INDEX [DT_expiryDateIDX] ON [dbo].[DT_DateTest]([ExpiryDate])
GO
March 28, 2004 at 8:21 pm
Exactly what extract were you doing?
For instance I tried Month(ExpiryDate) = 3 but with so few rows (roughly 5 pages) and the fact it has to check them all so it opts. I added 19000 rows and even the IS NULL gives me a table scan becuase the relative speed wouldn't be improved. Try it with the hint and you won't find it much if any faster. I also suggest using larger samples.
March 30, 2004 at 2:56 am
If you use "set statistics io on" and run the the query in your example you will see that SQL has to perform 475 logical reads. This is due to it first scanning the index and then having to look up the other fields in your select statement via a bookmark onto the tables clustered index.
A "select * from DT_DateTest" only makes 5 logical reads, clearly it is doing less work in performing a table scan.
Another alternative is to use a covered index which contains all the fields that your select statement is going to need. SQL will then not have to look anywhere other than the pages used by the index to satisfy the requirements of the query.
create index [DT_expirydate_covered] on [dbo].[DT_DateTest] ([ExpiryDate], [CodeInstanceID])
Using this covered index will result in the database only performing 3 logical reads. But beware, this index may be of little use to any other queries in your system and like any index will carry an update overhead.
Hope this helps.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply