Transferring large amount (84 million rows) of data efficiently

Question

I have about 84 millions rows. Of those all of them needs to be transferred to a separate database on the same server, then I delete to delete about 60 millions rows from the source database.

The 84 million rows are all in the same table. That table alone accounts for 90% of the whole database.

So... Source: 84 million rows -> 24 millions rows Destination: 0 rows -> 84 million rows

Source is running full recovery mode, destination will be running simple.

I am wondering what would be the most efficient way to do this?

Plan A:

1) INSERT INTO destination SELECT * FROM source

2) TRUNCATE source

3) INSERT INTO source SELECT * FROM destination WHERE keep_condition = 1

Plan B:

1) Restore a backup of source database as the destination database

2) Drop every tables except the one needed on the destination database

3) TRUNCATE source

4) INSERT INTO source SELECT * FROM destination WHERE keep_condition = 1

Plan C:

1) INSERT INTO destination SELECT * FROM source

2) DELETE source WHERE keep_condition = 0

or something else?

Thanks

Answer 1

I would add that, however you decide to approach this, you'll need to batch these transactions. I've had very good luck with the linked article lately, and I appreciate the way it takes advantage of indexes as opposed to most batched solutions I see.

Even minimally logged, those are big transactions, and you could be spend a lot of time dealing with the ramifications of abnormal log growth (VLFs, truncating, right-sizing, etc.).

Thanks

Answer 2

"Efficient" could apply to log file usage, I/O performance, CPU time or execution time.

I would try to achieve a minimally logged operation, which would be fairly efficient from a logging perspective. This should save you some execution timeas a bonus. If you have the tempdb space, the following might work for you.

CREATE TABLE #temp;
ALTER source -> BULK_LOGGED recovery model

BEGIN TRANSACTION;

    INSERT INTO dest SELECT FROM source;
    INSERT INTO #temp SELECT FROM source WHERE keep_condition=1;
    TRUNCATE TABLE source;
    INSERT INTO source SELECT FROM #temp;

COMMIT TRANSACTION;

ALTER source -> FULL recovery model
DROP TABLE #temp;

For a minimally logged operation to happen, a number of conditions have to be true, including no backups currently running, database set to BULK_LOGGED recovery mode, and depending on your indexes, the target table may have to be empty. Some of this behaviour also changed (improved) from SQL Server 2005 to 2008.

Then again, without knowing the specifics of your table and data, any of your other options may well perform better. Try using

SET STATISTICS IO ON;
SET STATISTICS TIME ON;

.. and see which works best.

EDIT: When performing bulk-logged operations, make sure you make a backup (full or transaction log) before and after the operation if you need point-in-time restore capability and you suspect that other activity may be going on in the database at the same time that your ETL job is running.

I wrote a blog post on minimally logged operations a while ago, there are links in there to other posts and documentation.

Answer 3

Why not BCP?

1. Back up the sourcedb
2. Change sourcedb to bulk-logged

3. Open command prompt

4. bcp server.sourcedb.table out Filename.flt -T -c

5. bcp "SELECT * FROM sourcedb.table WHERE keep_condition = 1" queryout Filename2.flt -T -c

6. bcp Server.destinationdb.table in Filename.flt -T -c -b1000

7. check the data

8. From SSMS Truncate the sourcedb table
9. bcp server.sourcedb.table in Filename2.flt -T -c -b1000
10. Change sourcedb back to full

Answer 4

Don't think you should be recommending changing the recovery model without either a full database backup or t-log backup before and after. One of the features of BULK_LOGGED recovery model is that you will lose the ability to do point-in-time recovery for t-logs containing bulk-logged operations. Classic scenario: nightly full backup, hourly t-log backups. You change the recovery model to bulk-logged and start your operation. Something goes wrong and the transaction rolls back (or you haven't used one). However you're not sure what else was going on in the database so you want to restore to a known good point.

When can you restore back to? Last hourly t-log backup that does not contain bulk-logged operations, potentially losing n minutes of transactions. A full backup or t-log backup before changing the recovery model will create a fallback point. Which one you choose depends on your RTO.

Answer 5

Dropping partitions out of a table is a really fast and resource-efficient way of removing large chunks of data from a table. Were this table partitioned in a manner that supports your source / destination split the answer would be to restore a copy, drop the redundant tables and redundant partition(s) from destination and drop the complementary partitions from source.

The cost of enabling partitioning may make this a more expensive operation overall, however.