Impact of "Rows per batch" on OLE DB Destination?
OLE DB Destination has a "Rows per batch" parameter.Are there best practices/guidelines around what it should be set to (for better performance etc)? For example, if each row is of size 100 bytes, how does performance change when I set a batchsize of 0, 100, 1000, 10,000 etc?I guess the default value for this parameter is zero. Does that mean, internally it is implemented as:For each row:begin transactioninsert rowcommit transactionthanks,Nitesh
October 7th, 2005 3:45pm

Nitesh Ambastha wrote: OLE DB Destination has a "Rows per batch" parameter.Are there best practices/guidelines around what it should be set to (for better performance etc)? For example, if each row is of size 100 bytes, how does performance change when I set a batchsize of 0, 100, 1000, 10,000 etc?I guess the default value for this parameter is zero. Does that mean, internally it is implemented as:For each row:begin transactioninsert rowcommit transactionthanks,NiteshHi Nitesh.Why not do some benchmarking for it? Find out the answers. It'd be really useful info for the community.-Jamie
Free Windows Admin Tool Kit Click here and download it now
October 8th, 2005 7:51am

Mmmmmhmmmmm, I'd like to see that! :)
October 8th, 2005 6:37pm

OK I thought I'd have a go at this. Where is this "rows per batch" parameter/property?I can't see it. Am I being really dumb?
Free Windows Admin Tool Kit Click here and download it now
October 8th, 2005 6:58pm

Jamie,It is on "OLE DB Destination". When you double click on OLE DB Destination, Editor pops up to configure the destination. It is on the first screen / tab (Connection Manager), Second one from bottom. Default value is not 0. It is -1, which indicates that no value has been assigned (this is according to BOL).ThanksSutha
October 10th, 2005 3:07am

Sutha Thiru wrote: Jamie,It is on "OLE DB Destination". When you double click on OLE DB Destination, Editor pops up to configure the destination. It is on the first screen / tab (Connection Manager), Second one from bottom. Default value is not 0. It is -1, which indicates that no value has been assigned (this is according to BOL).ThanksSuthaOK, I'm glad I'm not heading for the loony bin. I was looking at an IDW15 instance which doesn't have it. I've checked my IDW16 VM and its there.I wonder why it (and many other properties) were removed on IWD15?And here's another question. Why are they (Rows per batch, keep nulls, table lock, check constraints, keep ID, max insert commit size) not available via the preoperties pane?cheers Sutha-Jamie
Free Windows Admin Tool Kit Click here and download it now
October 10th, 2005 4:55am

Good question. Only reason I could think of is as they are new options, they forgot to add it into properties. Can anyone from MS respond that these are going to be available via properties when the product is released in Nov?ThanksSutha
October 10th, 2005 5:46am

Sutha Thiru wrote: Good question. Only reason I could think of is as they are new options, they forgot to add it into properties. Can anyone from MS respond that these are going to be available via properties when the product is released in Nov?ThanksSuthaAs an aside...I don't think they are new properties. I'm *sure* I remember seeing them way back when. It seems to me like they disappeared for IDW15 and now they've reappeared again. Weird!!-Jamie
Free Windows Admin Tool Kit Click here and download it now
October 10th, 2005 6:54am

I did test way back when on the perf of loading batches per transaction. I generally found that 2000 was a good figure. However I supsect this depends greatly on, the wdith of the rows being inserted,the io configuration memory in the systemInterestingly, why is there no batch rows setting for the SQL destination?
October 10th, 2005 12:36pm

I'm guessing cos it uses bulk insert which doesn't allow you to specify batch sizes (does it???)-Jamie
Free Windows Admin Tool Kit Click here and download it now
October 10th, 2005 1:18pm

In SQL server 2005, it is our decision to remove properties "RowsPerBatch" and "BatchSize" from SQL server destination adapter. Reason is SQL server dest adapter is not written to utilize the functionalities the two properties provide - e.g. query plan optimization. We will consider whether to enable them in the next version.
October 10th, 2005 1:29pm

"Rows per batch" is a Fast Load Option. In the OLE DB Destination, it is only visible (along with some other options like Keep Identity, Keep Nulls etc.) if you choose the Data Access Mode "Table or View - Fast Load". If you choose the data access mode "Table or view", they won't show up. They never disappeared from IDW15, my guess is you are looking at it with the non fast load option, and therefore are not finding it there.They are available from the properties window, but in a different way. You can specify them in the FastLoadOptions. For example, FastLoadOptions : TABLOCK,CHECK_CONSTRAINTS,ROWS_PER_BATCH = 1000
Free Windows Admin Tool Kit Click here and download it now
October 10th, 2005 1:52pm

This seems to indicate the number of rows it will load into the buffer from the source before committing to the destination. I set a value of 20000 and then see the connector enumerate the workflow in real time by incrementing the counter by the same amount. I suppose that there is an optimal value based on the size of your record, the number of rows you specify with this settting, the Maximum Insert Commit Size setting, and the amount of RAM (perhaps) on the machine. Understanding this relationship would, indeed, be quite useful for folks who are moving millions of records around and want to do so quickly...which is how I found this blog in my favorite search engine.
June 14th, 2012 12:33pm

I don't know much, but I had tables with millions to tens of millions of rows, and until I limited the rows per batch to 100,000, I would end up with the transaction log full. And it went much faster, too. MUCH. Everything was fine after that. Don't leave it at 0 for huge row sets. I think it tries to log the entire query without a commit so it can rollback the whole thing at once if it fails. I don't understand too much on the logging though. But I did get it to work. Maybe someone else can chime in on this point.
Free Windows Admin Tool Kit Click here and download it now
June 14th, 2012 3:56pm

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics