Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have a big insert script I need to run. Its about 55,000 records and 160 columns. The script is already created and I can't create it again.

The problem I have is that this runs for about 4 hours or so, and during that time the system that uses this database gets really slow and timeout a lot.

I would not care if my INSERT is slower but it shouldn't impact other users.

I was thinking in doing some batch of let's say 500 rows and use the WAITFOR, but was wondering if there could be a better option for doing this.

share|improve this question
    
So you have a big long script with 55,000 individual insert statements? –  Aaron Bertrand Jul 29 '13 at 20:59
    
Yes, actually they do a little more than insert, have some if and update or insert in multiple tables –  Luis Tellez Jul 29 '13 at 21:02
    
@LuisTellez This answer from Aaron will help you as well stackoverflow.com/a/1602362/1387418 –  Kin Jul 29 '13 at 21:08
add comment

2 Answers

up vote 4 down vote accepted

Break the operation into chunks of separate transactions.

BEGIN TRANSACTION;
  INSERT ...
  INSERT ...
  -- maybe 1000 or 2500 of these
COMMIT TRANSACTION;

GO
WAITFOR DELAY '00:00:01';
GO

BEGIN TRANSACTION;
  INSERT ...
  INSERT ...
  -- maybe 1000 or 2500 more of these
COMMIT TRANSACTION;

This will prevent all 55,000 inserts from jamming up your log and operating as a single, blocking transaction. I talked about this methodology to some degree in this blog post even though it talks about more tightly controlled DELETE operations.

If you can, have the file(s) regenerated in a format that makes them easier to BULK INSERT or bcp.

share|improve this answer
    
i dont have the database from witch this was generated. –  Luis Tellez Jul 29 '13 at 21:04
1  
@Luis ok, well C# can whip up a pretty quick file parser that can generate slightly different files based on the content. Just a thought. –  Aaron Bertrand Jul 29 '13 at 21:05
    
Thanks Aaron, this is what I had in mind but was wondering if I could do something better. –  Luis Tellez Jul 30 '13 at 14:17
add comment

Without knowing more about the system, doing small batches and then wait for a few milli seconds will usually significantly reduce contention. However, you need to be aware that it will significantly slow down the insert.

Make sure you do not have transactions that span your batches or you will make things much worse.

share|improve this answer
    
"Make sure you do not have transactions that span your batches or you will make things much worse." Can you please explain what you mean by this? im not sure what do you mean by it. –  Luis Tellez Jul 29 '13 at 21:03
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.