SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Insert Statement for 2 million rows


Insert Statement for 2 million rows

Author
Message
PJ_SQL
PJ_SQL
Mr or Mrs. 500
Mr or Mrs. 500 (564 reputation)Mr or Mrs. 500 (564 reputation)Mr or Mrs. 500 (564 reputation)Mr or Mrs. 500 (564 reputation)Mr or Mrs. 500 (564 reputation)Mr or Mrs. 500 (564 reputation)Mr or Mrs. 500 (564 reputation)Mr or Mrs. 500 (564 reputation)

Group: General Forum Members
Points: 564 Visits: 652
Hi!

I have multiple (individual) insert statement to insert around 2 million rows (using sqlcmd because the insert stament is around 8 thou lines).

It took about an hr to insert when I tried it on my machine which is physical server with 4 GB RAM.

When I try to run the same on my test server which is virtual machine it is still running,we have bumped up memory from 2 to 4 GB RAM.

Any suggestions?
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (84K reputation)SSC Guru (84K reputation)SSC Guru (84K reputation)SSC Guru (84K reputation)SSC Guru (84K reputation)SSC Guru (84K reputation)SSC Guru (84K reputation)SSC Guru (84K reputation)

Group: General Forum Members
Points: 84695 Visits: 41069
pretea (3/13/2014)
Hi!

I have multiple (individual) insert statement to insert around 2 million rows (using sqlcmd because the insert stament is around 8 thou lines).

It took about an hr to insert when I tried it on my machine which is physical server with 4 GB RAM.

When I try to run the same on my test server which is virtual machine it is still running,we have bumped up memory from 2 to 4 GB RAM.

Any suggestions?


Why is the insert statement around 8 thousand lines?

--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
PJ_SQL
PJ_SQL
Mr or Mrs. 500
Mr or Mrs. 500 (564 reputation)Mr or Mrs. 500 (564 reputation)Mr or Mrs. 500 (564 reputation)Mr or Mrs. 500 (564 reputation)Mr or Mrs. 500 (564 reputation)Mr or Mrs. 500 (564 reputation)Mr or Mrs. 500 (564 reputation)Mr or Mrs. 500 (564 reputation)

Group: General Forum Members
Points: 564 Visits: 652
It is how the vendor sent in the script?

Other thing I can try is to ask them to load data into text file and use BCP.
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (84K reputation)SSC Guru (84K reputation)SSC Guru (84K reputation)SSC Guru (84K reputation)SSC Guru (84K reputation)SSC Guru (84K reputation)SSC Guru (84K reputation)SSC Guru (84K reputation)

Group: General Forum Members
Points: 84695 Visits: 41069
pretea (3/13/2014)
It is how the vendor sent in the script?

Other thing I can try is to ask them to load data into text file and use BCP.


Heh... I can only imagine what that script must look like.

Yes, I agree. Load the text file into a staging table using BCP, validate the data, and then insert it into the final table.

--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Eric M Russell
Eric M Russell
SSChampion
SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)

Group: General Forum Members
Points: 12042 Visits: 10622
pretea (3/13/2014)
Hi!

I have multiple (individual) insert statement to insert around 2 million rows (using sqlcmd because the insert stament is around 8 thou lines).

It took about an hr to insert when I tried it on my machine which is physical server with 4 GB RAM.

When I try to run the same on my test server which is virtual machine it is still running,we have bumped up memory from 2 to 4 GB RAM.

Any suggestions?


Performance on a workstation PC, much less a VM running on your PC, will bear little resemblance to performance on a typical production server. That said, first option should be to get a delimited text version of the records and then BCP into database, like others suggested. You can use 'Export Data' wizard to copy records from original source datbase to text file.
The problem with a script is that all 2 million inserts are probably getting batched into one huge transaction. If all you have to work with is this single script, then use a tool like GSplit to chop it into seperate smaller batches. I'm guessing 20 .sql scripts with 100,000 inserts per batch should take it down to a manageable level. Also, you can use PowerShell script or SQLCMD command and a batch file to execute the scripts sequentially.
http://www.gdgsoft.com/gsplit/
http://technet.microsoft.com/en-us/library/ms170572.aspx


"The universe is complicated and for the most part beyond your control, but your life is only as complicated as you choose it to be."
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (84K reputation)SSC Guru (84K reputation)SSC Guru (84K reputation)SSC Guru (84K reputation)SSC Guru (84K reputation)SSC Guru (84K reputation)SSC Guru (84K reputation)SSC Guru (84K reputation)

Group: General Forum Members
Points: 84695 Visits: 41069
Eric M Russell (3/13/2014)
pretea (3/13/2014)
Hi!

I have multiple (individual) insert statement to insert around 2 million rows (using sqlcmd because the insert stament is around 8 thou lines).

It took about an hr to insert when I tried it on my machine which is physical server with 4 GB RAM.

When I try to run the same on my test server which is virtual machine it is still running,we have bumped up memory from 2 to 4 GB RAM.

Any suggestions?


Performance on a workstation PC, much less a VM running on your PC, will bear little resemblance to performance on a typical production server. That said, first option should be to get a delimited text version of the records and then BCP into database, like others suggested. You can use 'Export Data' wizard to copy records from original source datbase to text file.
The problem with a script is that all 2 million inserts are probably getting batched into one huge transaction. If all you have to work with is this single script, then use a tool like GSplit to chop it into seperate smaller batches. I'm guessing 20 .sql scripts with 100,000 inserts per batch should take it down to a manageable level. Also, you can use PowerShell script or SQLCMD command and a batch file to execute the scripts sequentially.
http://www.gdgsoft.com/gsplit/
http://technet.microsoft.com/en-us/library/ms170572.aspx


BCP will actually do all of that.

--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Eric M Russell
Eric M Russell
SSChampion
SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)

Group: General Forum Members
Points: 12042 Visits: 10622
Jeff Moden (3/13/2014)
Eric M Russell (3/13/2014)
pretea (3/13/2014)
Hi!

I have multiple (individual) insert statement to insert around 2 million rows (using sqlcmd because the insert stament is around 8 thou lines).

It took about an hr to insert when I tried it on my machine which is physical server with 4 GB RAM.

When I try to run the same on my test server which is virtual machine it is still running,we have bumped up memory from 2 to 4 GB RAM.

Any suggestions?


Performance on a workstation PC, much less a VM running on your PC, will bear little resemblance to performance on a typical production server. That said, first option should be to get a delimited text version of the records and then BCP into database, like others suggested. You can use 'Export Data' wizard to copy records from original source datbase to text file.
The problem with a script is that all 2 million inserts are probably getting batched into one huge transaction. If all you have to work with is this single script, then use a tool like GSplit to chop it into seperate smaller batches. I'm guessing 20 .sql scripts with 100,000 inserts per batch should take it down to a manageable level. Also, you can use PowerShell script or SQLCMD command and a batch file to execute the scripts sequentially.
http://www.gdgsoft.com/gsplit/
http://technet.microsoft.com/en-us/library/ms170572.aspx


BCP will actually do all of that.

Yes, BCP is best option, unless all that's available is the insert script. We can not BCP a .sql insert script.


"The universe is complicated and for the most part beyond your control, but your life is only as complicated as you choose it to be."
PJ_SQL
PJ_SQL
Mr or Mrs. 500
Mr or Mrs. 500 (564 reputation)Mr or Mrs. 500 (564 reputation)Mr or Mrs. 500 (564 reputation)Mr or Mrs. 500 (564 reputation)Mr or Mrs. 500 (564 reputation)Mr or Mrs. 500 (564 reputation)Mr or Mrs. 500 (564 reputation)Mr or Mrs. 500 (564 reputation)

Group: General Forum Members
Points: 564 Visits: 652
Yes let's see what see, have asked them to get the text version of the records and use then we can use BCP if not use smaller batches.

Thanks for all the suggestions.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search