Re: Question about move large amount of data from database to database
- From: Lee <lee.jenkins.ca@xxxxxxxxx>
- Date: 23 Apr 2007 15:16:00 -0700
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...@xxxxxxxxxxxxx> wrote:
Lee (lee.jenkins...@xxxxxxxxx) 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...@xxxxxxxxxxxxx
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
.
- Follow-Ups:
- References:
- Question about move large amount of data from database to database
- From: Lee
- Re: Question about move large amount of data from database to database
- From: Erland Sommarskog
- Question about move large amount of data from database to database
- Prev by Date: Re: Question about move large amount of data from database to database
- Next by Date: Re: Question about move large amount of data from database to database
- Previous by thread: Re: Question about move large amount of data from database to database
- Next by thread: Re: Question about move large amount of data from database to database
- Index(es):
Relevant Pages
|
