guys,

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).

My question is if there is some other way I should do to speed up the
action, I am thinking about use bcp to copy those records to datafile
and then use bcp to insert it into SQL Server table. Is this the right
way to do it or should I consider other solution (then, what is the
solution.)

Thanks a lot!

On Apr 23, 2:23 pm, Lee <lee.jenkins... (AT) gmail (DOT) com> wrote:
Use a Select Into statement and make sure the destination db is set to
a simple recovery model.

Yes, BCP will be a good option for fast data transfer. All of the BULK
operations (BULK INSERT, SELECT INTO, BCP) are minimally logged when a non
FULL recovery model is set.

Another issue could be the purging of the archived records from your main
table. If you have it as a single DELETE and it takes long time to complete,
then you can break it into smaller DELETE chunks.

If you have SQL Server 2005 Enterprise Edition, an interesting alternative
is to use partitioned tables. Specifically range partitions based on date
ranges (in your case could be weekly) can help with archiving. Take a look
at the following article (in particular the section about Range Partitions):
http://msdn2.microsoft.com/en-us/library/ms345146.aspx

HTH,

Plamen Ratchev
http://www.SQLStudio.com

Lee (lee.jenkins.ca (AT) gmail (DOT) com) writes:
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, esquel (AT) sommarskog (DOT) se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

Plamen, Thanks a lot. I will try it and let you know the result.
Thanks again!

On Apr 23, 2:08 pm, "Plamen Ratchev" <Pla... (AT) SQLStudio (DOT) 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... (AT) sommarskog (DOT) se> wrote:

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... (AT) gmail (DOT) com> wrote:

Brad, Thanks for the reply, my situation is the target table already
have lots of records and I will just append the data to that table.

On Apr 23, 1:58 pm, Brad <Brad.Marsh... (AT) Teksouth (DOT) com> wrote:

Lee (lee.jenkins.ca (AT) gmail (DOT) com) writes:
Yes, that was precisely my reaction when I saw the table. Make the primary
key on record_id non-clustered, and add a clustered index on the date
column. I would guess you should do this on the archive table as well.

Also, I don't see the point with having the IDENTITY property on the
archive table. Just make it a normal column, and you don't need that
SET IDENTITY_INSERT. Not that it affects performance, but it looks cleaner.
However to change this, you would need rename the existing table, create
it a new and copy over. There is no ALTER syntax for changing the
IDENTITY property.




--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx