3 added 41 characters in body
source | link

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 127.0.0.1 -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?

Edit:

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" should be possiblewas 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.

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 127.0.0.1 -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?

Edit:

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" should be possible by copying two files together. 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.

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 127.0.0.1 -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?

Edit:

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.

2 added 871 characters in body
source | link

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 127.0.0.1 -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?

Edit:

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" should be possible by copying two files together. 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.

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 127.0.0.1 -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?

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 127.0.0.1 -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?

Edit:

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" should be possible by copying two files together. 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.

    Tweeted twitter.com/#!/StackDBAs/status/208290618261250048
1
source | link

sqlcmd slow against SQL Server 2008 R2

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 127.0.0.1 -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?