|
|
Posted by Lee on 04/23/07 22:16
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
[Back to original message]
|