January 30, 2009 at 10:32 am
I have the following 2 queries, with a WHERE on the TIME_ID filed. TIME_ID is a numeric value like a date, a different one for each day, so the first query is pulling 1 day of data, the 2nd is pulling 2 days of data. The table has about 6 months of data and about 45 million rows. There's an index on TIME_ID and the execution plan show an index seek for the 1 day query, but a table scan for the 2 day query. There's no clustered index. This table is a subset of and has the same structure as a live production table with 211 million rows over a couple of years.
Thoughts ?
SELECT time_id, DATEDIFF(ss, voicestart, voiceend) -- 18 seconds 142699 rows
FROM CallLogArchive with (nolock)
WHERE time_id >= 39784 AND time_id <= 39784
SELECT time_id, DATEDIFF(ss, voicestart, voiceend) -- 4:46 minutes 282135 rows
FROM CallLogArchive with (nolock)
WHERE time_id >= 39784 AND time_id <= 39785
CREATE TABLE [dbo].[CallLogArchive](
[id] [bigint] IDENTITY(1,1) NOT NULL,
[Activity_Datetime] [datetime] NULL,
[Time_ID] [int] NULL,
[XID] [char](8) NULL,
[First] [varchar](25) NULL,
[Last] [varchar](25) NULL,
[Supervisor] [char](6) NULL,
[Shift] [char](1) NULL,
[Room] [char](2) NULL,
[Center] [char](1) NULL,
[List_ID] [int] NULL,
[Disp_ID] [int] NULL,
[Campaign_ID] [uniqueidentifier] NULL,
[RWA_ID] [char](10) NULL,
[Start] [datetime] NULL,
[VoiceStart] [datetime] NULL,
[VoiceEnd] [datetime] NULL,
[Released] [datetime] NULL,
[DialerCode] [char](5) NULL,
[Disposition] [uniqueidentifier] NULL,
[CreditTo] [char](38) NULL,
[WaitTime] [datetime] NULL,
[CustomData] [nvarchar](255) NULL,
[Code] [char](10) NULL,
[CallLog_ID] [uniqueidentifier] NULL,
[RecordingLength] [varchar](8) NULL
) ON [PRIMARY]
CREATE NONCLUSTERED INDEX [Time_ID] ON CallLogArchive
([Time_ID] ASC) ON [INDEXES]
January 30, 2009 at 10:57 am
The optimizer must feel that it is less expensive to do a table scan with the second query as a opposed to an index seek and bookmark lookup.
I guess to really answer that question would require looking at the execution plan for both queries.
If you do a lot of ranged queries using time_id I'd suggest putting a clustered index on time_id.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply