I am trying to import a longish SQL script (3.5 millions of lines, 1,5 GB in size) containing mainly short INSERT INTO statements with data using sqlcmd -E -d <database> -S -i <scriptfile> and it is nearly crawling to a halt (approximatly 150 lines per second) without inferring any noticeable load on the SQL server host (all CPU cores are nearly idle, Disk throughput is at around 200 KB/s).

As the source file is located on an NFS share, I first suspected this to be the culprit, but an identical script available locally runs at just the same speed.

The database is basically empty, the tables are created by the very same script, no triggers or other fancyness - just the raw, mainly numeric or varchar data.

What is SQLCMD waiting for? Is there any way to speed it up?


we are limited in our means to alter the data in the SQL script files. The data is provided by a third party for an import procedure. I believe it was originally exported using Management Studio 2005 "Script table as..." feature.

Editing the file is tedious due to sheer size - any operation takes forever with common text editors, although prepending a "SET NOCOUNT ON" was done by copying two files together - and it brought a speedup of approximately 50%.

Due to the text encoding (Unicode-LE) it is impossible to edit with the common set of GNU textutils (which would otherwise work fine with large files) without converting - and I am reluctant to converting because of data fidelity issues which are likely to come with it.

So I am somewhat stuck with how to apply the suggestions on inserting the BEGIN TRAN/COMMIT TRAN blocks or converting the individual inserts into larger sets.

  • You could pull out most of the insert commands into a separate file and BULK INSERT it. Or do you have tangled dependencies and foreign keys to satisfy? Commented May 31, 2012 at 18:43
  • Can you push some requirements back to the 3rd party vendor? Even they should realize that what they're providing you is not practical. Feel free to point them here. :-) Commented May 31, 2012 at 20:51

1 Answer 1


Some ideas:

  • Inject some GO commands every thousand or few thousand lines. Then instead of one ginormous batch it is broken up into multiple batches.
  • Change your individual INSERT statements to INSERT ... VALUES () with a thousand sets each.
  • Use transactions and commit and/or checkpoint gratuitously (again, every 1000 inserts or so is probably a good place to start, in combination with GO). Your watch and the log will thank you.
  • It's a silly trivial little thing, but make sure your script has SET NOCOUNT ON - otherwise the UI, SQL Server, and the network in between spend a lot of time sending 1 row(s) affected messages back and forth for every single insert.
  • Best is probably to use BULK INSERT, BCP or maybe even SSIS instead of Management Studio. Part of the problem is probably the memory overhead on your own machine just loading that huge file, never mind trying to parse/execute it. I'd be curious to see private bytes for ssms.exe while this is going on...
  • Based on offline discussion with the OP, adding another option: ask the vendor for a backup instead of a script. Then you can restore that to some test or dummy system, and generate inserts from that manually (or use import/export, or Red Gate's data compare tool). This may end up being net same amount of time, but won't kill the log with that huge script of millions of inserts...
  • 1
    +1 - This is the second-least-efficient way of loading 3.5m rows. The only thing worse I can think of would be to load it from XML
    – JNK
    Commented May 31, 2012 at 18:49
  • @JNK or 3.5 million individual one-line text files Commented May 31, 2012 at 18:50
  • 2
    I'm not supposed to be using 3.5 million individual one line text files?
    – Zelda
    Commented May 31, 2012 at 18:52
  • Well, only if you want the process to take all week so you can fill your timesheet while you were playing Draw Something instead... Commented May 31, 2012 at 18:54
  • 1
    Regarding your second point. Have a look at this SO post. Commented Jun 1, 2012 at 2:45

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.