How long should an select into query take when selecting from a table with ~200 million rows in sql server 2005?

Question

I have a table with 193,569,270 rows in a SQL Server 2005 database. The table houses activities that are performed by users of our website. The table is defined as:

Name                  DataType
ID                    int (identity)             PK
ActivityTime          datetime
PersonID              int                        (should be an FK, but isn't)
ActivityTypeID        int                        (should be an FK, but isn't)
Data1                 varchar(50)
Data2                 varchar(50)

I have the following indexes:

CREATE NONCLUSTERED INDEX [_MS_Sys_3] ON [dbo].[tblPersonActivity] ([PersonID] ASC)
INCLUDE ( [ID], [ActivityTime], [ActivityTypeID], [Data1], [Data2]) 
WITH (PAD_INDEX  = OFF, 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

CREATE NONCLUSTERED INDEX [IX_Activity] ON [dbo].[tblPersonActivity] ([PersonID] ASC, [ActivityTypeID] ASC, ActivityTime] ASC)
WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 90) ON [PRIMARY]
GO

CREATE NONCLUSTERED INDEX [IX_tblPersonActivity_PersonArchive] ON [dbo].[tblPersonActivity] ([ActivityTime] ASC)
INCLUDE ([ID], [PersonID], [ActivityTypeID], [Data1], [Data2]) 
WITH (PAD_INDEX  = OFF, 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

ALTER TABLE [dbo].[tblPersonActivity] ADD  CONSTRAINT [PK_tblPersonActivity] PRIMARY KEY CLUSTERED ([ID] ASC)
WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO

This is the query I've written:

declare @archiveDate            datetime
declare @curDate                datetime
declare @startDate              datetime
declare @curYear                int
declare @preYear                int

set @curDate = getdate()
set @curYear = year(@curDate)
set @preYear = @curYear - 1
set @archiveDate = @curDate
set @startDate = cast(('1/1/' + cast(@preYear as varchar(4))) as datetime)

declare @InactivePersons table
    (PersonID       int     not null PRIMARY KEY)

insert into @InactiveBuyers
    select 
        b.PersonID 
    from 
        HBM.dbo.tblPersons b with (INDEX(IX_tblPersons_InactiveDate_PersonID), nolock)
    where 
        b.InactiveDate is not null 
        and b.InactiveDate  '1/1/1900' 
        and b.InactiveDate  '12/31/1899' 
        and b.InactiveDate = @StartDate

The last time I ran the query it ran for over 1 day before I killed it. Have I missed something or is this just going to take that kind of time?

Thanks for any help you can provide.

Wayne E. Pfeffer

HLGEM HLGEM · Accepted Answer · 2011-03-29T14:30:51

No this should not take that long if your database is properly set up and indexed.

First you need to create those FKs! There is not excuse for not having them to ensure your data integrity. FKs should have their own indexes.

Inactive date deosn't seem to be in your table structure. Is it a date field? Make it one if it is not or you are wasting time doing implicit conversions.

b.InactiveDate is not null 
        and b.InactiveDate  '1/1/1900' 
        and b.InactiveDate  '12/31/1899' 
        and b.InactiveDate = @StartDate

This whole where clause doesn't make sense. If you are looking for the records that match @startdate then you don't need any of the rest.

Check out the Execution plan to see where this is taking so long, something is causing a table scan.

And if there will be large numbers of records in the table varaible, then a temp table tends to perform faster. You don't say what you are doing with this table in the rest of the proc, are you sure it is the insert statement taking the most time or something else you are doing?

How long should an select into query take when selecting from a table with ~200 million rows in sql server 2005?

1 Answers