2

Are there any solutions more efficient than simple INSERT INTO or exporting and importing files to move millions of rows from a table with 3 columns to a similar table in another database, without blocking the destination database and not losing any of the records in transactions that can be rolled back in case of failure?

4
  • Thought about using SSIS ? Sep 20, 2017 at 10:06
  • it blocks database i think, does it?
    – FargolK
    Sep 20, 2017 at 10:12
  • It depends, you can set the table lock off, and adjust the batch size to have a minimal of locking Sep 20, 2017 at 10:49
  • 1
    I don't think an insert blocks the database. It might block the target table however.
    – user1822
    Sep 20, 2017 at 11:17

3 Answers 3

4

Are there any solutions more efficient than simple INSERT INTO or exporting and importing files to move millions of rows from a table with 3 columns to a similar table in another database,

Your best bet for this is going to be SSIS.

without blocking the destination database

Inserting into one table won't block the database, but it will cause blocking on the table to one degree or another. The length and severity of the blocking will largely be determined by the number of indexes and how well they suit your queries.

If you want to reduce blocking, insert data in smaller chunks. I usually go for around 1000 rows at a time to avoid lock escalation.

Another way to reduce blocking is to use an optimistic isolation level on queries that read data from the able.

and not losing any of the records in transactions that can be rolled back in case of failure?

This is another reason why smaller batches is a good idea. It will make it easier for you to figure out the offending row, retry the transaction, etc.

There are also some neat trick shots in this post, by Michael Swart: Modifying Tables Online – Part 1: Migration Strategy

0
1

A trickle feed (delta) is generally recommended for larger data sets but it will depend on how often you want to run it and the actual delta volume.

If you are simply looking to keep a secondary copy in consistent state/sync with primary, perhaps you should look at replication, log shipping or availability groups.

You could also use BCP or SSIS to move data into new database into new table - which will not lock the actual destination table - and then use partition switching, this should result in minimum locking in the destination database.

Kendra Little has a very good article on partition switching: https://littlekendra.com/2017/01/19/why-you-should-switch-in-staging-tables-instead-of-renaming/

1

At my work we do a little trick with a C# app developed exclusively to move about 500-600gb of data daily from our Oracle OLTP Servers to our SQL Server Datawarehouse, in about an hour, with a lot of threading (about 30 threads)... the logic could be transfered without too much problems to SSIS (and that's because we first tested the performance on a SSIS package xD)

We open N connections to the source table, and we modified the select to filter the rows based on the Module (MOD in Oracle, % in SQL Server) of a number field... this trick enables us to read a single table with a lot of threads at the same time (this is something similar that happens when you import data to Hadoop using Sqoop, since Sqoop uses an indexed number field, and split the read task on the N workers assigned for the job, each worker reads a different range of data at the same time)

For example you could simply make 10 connections to the same table and put this kind of query to read the data with a Data Flow Task

select number_field, varchar_field, date_field from table where (number_field % 10) = 0
select number_field, varchar_field, date_field from table where (number_field % 10) = 1
....
select number_field, varchar_field, date_field from table where (number_field % 10) = 8
select number_field, varchar_field, date_field from table where (number_field % 10) = 9

Each query would read a different chunk of data from the source table, and insert without problems on the destination table, if you use OLEDB Destination you could edit the options to uncheck the option to lock the destination table, and use a batch size below 5000 rows, since above 5000 rows, the rows are writed first on the temp db, and then transfered to the destination table, like Erik said, 1000 rows is not a bad batch size

With this trick we move daily tables with 10 rows to about 500 million rows (historic data... that is updated daily with some process running on the OLTP Oracle... we truncate the destination table daily and read all the rows from Oracle again... that's the reason why our process reach the hour of execution)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.