5 added 18 characters in body
source | link

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. 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...

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...

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...
4 added 388 characters in body
source | link

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...

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...

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...
3 added 235 characters in body
source | link

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 INSERTBULK INSERT, BCP or BCPmaybe 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...

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 or BCP 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...

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...
2 added 725 characters in body
source | link
1
source | link