Post reply

Inserting Certain Number of Rows at a Time

  • 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

  • July 28, 2006 at 1:00 pm

    #652187

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

  • July 28, 2006 at 1:02 pm

    #652190

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

  • July 28, 2006 at 1:07 pm

    #652198

    Thanks Dinakar

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

    Will try this.

    Andrew SQLDBA

  • July 28, 2006 at 3:13 pm

    #652211

    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

  • July 28, 2006 at 3:31 pm

    #652212

    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 ?

     

  • July 28, 2006 at 8:29 pm

    #652223

    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

  • July 31, 2006 at 12:13 pm

    #652448

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

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

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