4

Please can someone explain me any pitfall or drawback with "Use batch update for Inserts" option with Pentaho Kettle Table output or update steps? One of my colleage's opinion is, if commit size set to 1000 and if any one of these 1000 records failed to get inserted/updated, then that entire batch of 1000 records will get ommitted from updating into that table. I don't know whether its true or false and at present am unable to test it myself.

What if this option is not enabled and still I continue with higher commit size, same 1000?

RVandakar
  • 81
  • 1
  • 5
  • 16

1 Answers1

1

It really is just the database commit size. Best practice would be to set it somewhere between 100 & 2000.

If you make it too small you will slow down your transformation, likewise if you make it to large it can slow down your transformation and even run out of memory. You need to play with the size since it really is just trial and error.

As far as errors either way your transformation will fail once it reaches the one record in error at least in 4.1

Abercrombieande
  • 679
  • 6
  • 12
  • So, you mean to say, till version 4.1, the problem exits where if one record of the batch fails, entire batch fails. Am using CE 5.0.1. So I can proceed using it with a moderate commit size, say some 200 to 500 with batch update? Our daily/weekly transaction record size may be in the range of 500 to 5000. – RVandakar Oct 22 '14 at 05:29