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 transaction
insert row
commit transaction
thanks,
Nitesh

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 transaction
insert row
commit transaction
thanks,
Nitesh

Hi Nitesh.
Why not do some benchmarking for it? Find out the answers. It'd be really useful info for the community.

-Jamie|||Mmmmmhmmmmm, I'd like to see that! :)|||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?|||

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).

Thanks
Sutha

|||

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).

Thanks
Sutha

OK, 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|||

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?

Thanks
Sutha

|||

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?

Thanks
Sutha

As 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|||

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 system

Interestingly, why is there no batch rows setting for the SQL destination?

|||I'm guessing cos it uses bulk insert which doesn't allow you to specify batch sizes (does it?)

-Jamie|||

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.

|||

"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

sql