September 29, 2008 at 8:17 am
I am having 80 millions record in a table. I want to move all the records to a destination table using SSIS. I am using OLEDB Source and OLEDB Destination in Dataflow Tasks for moving the records.
I want to move 10000 records per batch. I have updated 10000 in the
OLEDB Destination Editor-->Connection Manager-->
Rows per Batch = 10000. The Default value was 1.
My Question is :
1)Does this update, really boost up my performance of the transformation?
2) What is the maximum value i can give which does not affect my performance.
3) Do we have any other Data Flow Task which transfers the data quickly between tables in SSIS.
September 29, 2008 at 8:33 am
Altering the rows per batch will restrict the number of records being transferred at a time. The maximum number of rows that can fit in memory will give you the best performance. It is best to have as few batches as possible, but if your batch size has to overflow into virtual memory performance suffers.
So, the answer depends on the size of the rows and the amount of available memory. You should adjust the setting up and down until you get the best performance.
September 29, 2008 at 8:53 am
you try for fast load option in the destination editor...It should help u out...
September 29, 2008 at 11:06 am
Hi,
Thanks for the reply.
What do you mean by having Batches. How can i make it batch? Can you please explain more elaborate.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply