How to set exact value for ROWS PER COMMIT in Target table

Skip to Content

Actions

June 26, 2014 | 264 Views |

SAP Data Services

As we know that the default value to set on rows per commit is 1000, and maximum value to set is 50000.

BODS recommends to set the rows per commit value between 500 and 2000 for best performance.

The value of rows per commit depends based on number of columns in the target table.

Here is the formula for the same –

Calculate Rows per commit value = max_IO_size (64K for most of the platform)/row size

Row size = (# of columns)*(20 bytes (average column size) )* 1.3 (30% overhead)

Eg: – If no. of columns is 10 then row size = 10*20*1.3=26

CommitRowValue
= 64 K/ 26 = 2.4 K

To report this post you need to login first.

You must be Logged on to comment or reply to a post.

Venkata Ramana Paidi July 1, 2014 at 6:34 am

Hi Siva Prasad,

It is nice explanation for arrow fetch size and rows per commit . You mean as per your example rows per commit is 2400 records?

Thanks & Regards,

Ramana.

like (0)
1. Sivaprasad Sudhir Post authorJuly 1, 2014 at 8:01 am
  
  Yes. Exactly.
  
  like (0)
Mohamed Khaja Sherif M July 3, 2014 at 3:21 am

very Useful 🙂

like (0)
Kurian Marattil July 3, 2014 at 8:28 am

This ease our work. Thanks a lot for the useful information

like (0)
Devasenapathy V August 15, 2015 at 8:42 am

Its very useful.

like (0)
Norman Book January 11, 2016 at 12:32 am

Hi Siva Prasad,

I think you did your math incorrectly in the example (i.e. 10*20*1.3 = 260 <> 26).

Thanks,

Norman

like (0)
Andrew Naryzhny March 23, 2016 at 8:38 pm

If this math is used, then the number will almost never fall between the recommended number of rows per commit. It will be way under. In my case, the table has 45 columns, so that means I will have to set the rows per commit to 55. Something is def wrong here.

like (0)