Post reply

Batch Insert Sizing

  • October 1, 2010 at 12:56 pm

    #387921

    I have been working on fairly large insert of rows, I decided to batch the load rather than do all seven million records at once. While testing I noticed altering the number of rows in the batch alters the total time it takes to insert the records. The batch size which completed fastest became slower if I increased or decreased the batch size. Is there a way to determine generally or specifically what batch size would work fastest without the trial and error approach? I am thinking it might be connected to the record byte size and the size of pages/memory chunks SQL uses, though the specifics of that are a bit beyond my present level of understanding.

    Does anyone know of a way to determine optimal batching sizes, or can point me in the right direction to learn this?

    Thanks!

  • October 4, 2010 at 11:33 pm

    #1231462

    Complex question, but see the following two-part blog entry by Paul Randal - it contains a lot of very good information about loading data quickly, especially the effect of batching on log buffer flushes:

    http://sqlkpi.com/BLOGS/PAUL/post/Benchmarking-1-TB-table-population-(part-1).aspx

    ...and the Data Loading Performance Guide:

    http://msdn.microsoft.com/en-us/library/dd425070.aspx

    Paul White
    All articles available on SQL.kiwi
    @SQL_Kiwi

  • October 5, 2010 at 8:11 am

    #1231721

    Why, thank you! Always can use some more reading material.

Viewing 3 posts - 1 through 2 (of 2 total)

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