What is the best way of inserting data into SQL Server in parallel?

user_5182405 0
Viewed 410

I am trying to insert data from MS Access (.mdb) file into SQL Server 2014 Standard edition. Each file contains 100 tables. I have more than three thousand files to be loaded into SQL Server. Out of the 100 tables, some are loaded to a common table and the rest of them to individual tables in SQL Server.

Currently it is doing using Microsoft Jet.4.0 through a front end loop over file list and then over table list. The process takes three to four days to be completed that is not efficient enough.

I want to make the maximum use of server resources and minimize the operation time. My server has 128GB RAM with 32 core processors in Active-Passive cluster environment. How can I insert all files in parallel or in any efficient approach?

What I have tried:

I have coded a multi threaded program to send insert requests to the SQL Server which I found sending multiple requests to the SQL Server at the same time but the SQL Server is processing those requests serially. As a consequence, there is no improvement of performance.

I tried SQL Server Agent Job, which demonstrates to be parallel, however it seems to be difficult to control to me.

If we calculate, one file takes 2 min lo be loaded and there are three thousand of them. So, six thousand minutes that is 4.16 day. Average file size is around 100 mb. I found no working method even after googling and testing every method I have found each day of last couple of the months.

Is there any suitable way to insert data efficiently?

tsql sql-server sql
3 Answers

I'll add an unhelpful comment - and I'm not trying to be snarky - just a little historical perspective from an older developer. This is a classic 1980s nightmare design...and why the invention of the web was such a boom. Clearly the real solution to this is to move to a web application using a common SQL back end server rather than attempting to collect these 1000s of data sets as a batch...…. Having said that - I do understand that there are areas where the infrastructure isn't there for the web.

user_84972520

I recommend using DtsWizard.exe - it will generate a package on the fly that you can save off and modify. You can use it to become familiar with SSIS and all the options of loading - ie. parallel loading, fast loading, etc.

More importantly - I also recommend disabling triggers and indexes before loading. This can make a huge difference. If you have FK indexes you can explore leaving them on - it can help in some cases.

Another option is bulk loading - you can use the command line - or c# SqlBulkCopy class - and set the options accordingly to bypass triggers, etc. Folks have mentioned loading records should be faster than what you are experiencing, some of these little options can kill performance.

Also, are you loading blobs? xml? Do you have xml indexes?

user_6005310

because a bulk insert job can hold lock on the database file, consider augmenting the number of files of the system tempdb database AND your destination database.

It is recommended to use as many files as the number of CPUs(available for SQL Server)-(it is not the default installation option on SQL2014), adding a few more won't harm and can sometimes lower the latches, even more if you have several disk partitions, on different channels. then you can choose to store a table on one partition file. I also would recommend using Sql Server Agent with multiple jobs, each one with a simple bulk insert cmd line

user_69950740
Related