Batch Insert Sizing

  • 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!

  • 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

  • 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