Posted by Lee on 04/23/07 23:10

Should I remove the clusterd index on the record_id field and create
nonclustered index on this field and create a clustered index on
record_date field since in my query, I always select a range of data
by date.

On Apr 23, 3:16 pm, Lee <lee.jenkins...@gmail.com> wrote:
> Erland, Thanks a lot for the reply, also forgot to say thanks to Brad,
> Here is the table:
>
> CREATE TABLE [dbo].[tbl_record](
> [record_id] [int] IDENTITY(1,1) NOT NULL,
> [record_CC_id] [int] NOT NULL,
> [record_content] [varchar](500) COLLATE SQL_Latin1_General_CP1_CI_AS
> NOT NULL CONSTRAINT [DF_tbl_record_record_content] DEFAULT (''),
> [record_date] [datetime] NOT NULL CONSTRAINT
> [DF_tbl_record_record_date] DEFAULT (getdate()),
> [record_ip] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
> NULL CONSTRAINT [DF_tbl_record_record_ip] DEFAULT (''),
> [record_active] [bit] NOT NULL CONSTRAINT
> [DF_tbl_record_record_archive] DEFAULT (1),
> CONSTRAINT [PK_tbl_record] PRIMARY KEY CLUSTERED
> (
> [record_id] ASC
> )WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF, FILLFACTOR = 90) ON
> [PRIMARY]
> ) ON [PRIMARY]
>
> And The stored procedure is here:
>
> ALTER PROCEDURE [dbo].[ArchiveRecords]
> (
> @ddate datetime
> )
> AS
> BEGIN TRAN
> SET IDENTITY_INSERT record_archive.dbo.tbl_record_archive ON;
> INSERT INTO record_archive.dbo.tbl_record_archive
> (
> record_id,
> record_CC_id,
> record_content,
> record_date,
> record_ip,
> record_active
> )
> SELECT
> record_id,
> record_CC_id,
> record_content,
> record_date,
> record_ip,
> record_active
> FROM tbl_record WHERE record_date <= @ddate;
> DELETE FROM tbl_record WHERE record_date <= @ddate;
> SET IDENTITY_INSERT record_archive.dbo.tbl_record_archive OFF;
> IF @@ERROR = 0 BEGIN COMMIT TRAN END ELSE BEGIN ROLLBACK TRAN END
>
> On Apr 23, 2:31 pm, Erland Sommarskog <esq...@sommarskog.se> wrote:
>
>
>
> > Lee (lee.jenkins...@gmail.com) writes:
> > > I have a project need to move more than 100,000 records from one
> > > database table to another database table every week. Currently, users
> > > input date range from web UI, my store procedure will take those date
> > > ranges to INSERT records to a table in another database, then delete
> > > the records, but it will take really long time to finish this action
> > > (up to 1 or 2 hours).
>
> > It shouldn't take 1-2 hours to move 100.000 rows. It sounds like the
> > process is not well implemented, or that there are indexes missing. Yes,
> > you can gain speed by using BCP, but you also add complexity to the
> > solution that I can't really see should be needed with the volumes you
> > indicate?
>
> > Would it be possible for you to post the definition of the tables, including
> > indexes and the stored procedure?
>
> > --
> > Erland Sommarskog, SQL Server MVP, esq...@sommarskog.se
>
> > Books Online for SQL Server 2005 athttp://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books...
> > Books Online for SQL Server 2000 athttp://www.microsoft.com/sql/prodinfo/previousversions/books.mspx- Hide quoted text -
>
> - Show quoted text -

[Back to original message]