October 1, 2010 at 12:56 pm
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
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:
October 5, 2010 at 8:11 am
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