Inserting Certain Number of Rows at a Time

Question

Post reply

Inserting Certain Number of Rows at a Time

Andrew-331669

SSC-Addicted

Points: 442
More actions
July 28, 2006 at 12:12 pm

#113115

Hello All
I was not the original DB Architect on this database. I have an issue of an insert taking a very long time, something like 30 hours. It is doing an
INSERT INTO []
SELECT (blah, blah, blah)
WHERE (blah, blah, blah)
What I am curious of, can I select all the data that needs to be inserted into a table variable, or a temp table, and then make the insert run thru a certain number of records, then commit, and then start where it left off, insert some more, then commit, etc... all the until it finishes? Say insert 100,000 rows at a time, then commit, and then start off at row 100,001 thru 200,000, and commit each set as it goes along? There are approx close to 500 Billion rows this is working with, and if it runs into a problem, I am having to wait until it rolls everyone back, to begin troubleshooting.
Thanks
Andrew

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Dinakar Nethi-176633 SSCarpal Tunnel Points: 4689 More actions · Answer 1

whoa 500 BIL records????

Is this a one time process or a routine one?

I would recommend doinng a BULK INSERT or BCP for this. It will be significantly faster. You could create multiple files of 500,000 records per file (there is a batch size option with BCP) and write some vb program to BULK INSERT the records into the table.

******************
Dinakar Nethi
Life is short. Enjoy it.
******************

Dinakar Nethi-176633 SSCarpal Tunnel Points: 4689 More actions · Answer 2

I have done something like this recently and it still took 5 hrs for 1 bil records. so expect at least that much time frame. It also depends on the activity on the server during the process.

******************
Dinakar Nethi
Life is short. Enjoy it.
******************

Andrew-331669 SSC-Addicted Points: 442 More actions · Answer 3

Thanks Dinakar

That is a much better time frame than the one that I am currenly seing.

Will try this.

Andrew SQLDBA

Andrew-331669 SSC-Addicted Points: 442 More actions · Answer 4

And this can be done from one database table into another table that resides on the same server and in the same database?

I am reading thru the SQL BOL for the syntax

Thanks

Andrew

PW-201837 SSC-Insane Points: 20805 More actions · Answer 5

Does the table being inserted have triggers on it, or does it have foreign key constraints ?

Is your transaction log repeatedly auto-growing during the insert operation ?

Andrew Liles Mr or Mrs. 500 Points: 520 More actions · Answer 6

Yes, the translog is growing, and the insert is going very slow. No triggers and no constraints on the table that the data is going in too.

Thanks

Andrew SQLDBA

Dinakar Nethi-176633 SSCarpal Tunnel Points: 4689 More actions · Answer 7

(1) Keep the Db in simple mode

(2) You can create a job to truncate the log and let it run every minute or after every 2 minutes.

also if you have indexes, you might want to drop them, do the transfer and then create the index'es.

******************
Dinakar Nethi
Life is short. Enjoy it.
******************