Hibernate batch processing supported by Corticon [hibernate.jdbc.batch_size] - Forum - Corticon - Progress Community

Hibernate batch processing supported by Corticon [hibernate.jdbc.batch_size]

 Forum

Hibernate batch processing supported by Corticon [hibernate.jdbc.batch_size]

This question is not answered

I wonder if Corticon's EDC does support batch processing as specified by Hibernate by using the property hibernate.jdbc.batch_size.

Some background info:https://vladmihalcea.com/2015/03/18/how-to-batch-insert-and-update-statements-with-hibernate/

and https://docs.jboss.org/hibernate/orm/3.3/reference/en/html/batch.html

I did a small test with a Corticon project but adding the settings to the Database Access properties doesn't seem to have any effect. If it is possible, what settings are needed to have it activated for Corticon?

All Replies
  • Lucas,

    We've investigate this previously and it does not have the desired benefits. Features we depend upon like identity strategies and 2nd level cache are not compatible with hibernate batch; at least in how Corticon uses them. Batches can only be for a single table.  Operations on other tables closes the batch and starts another one.

    Jim

    James Arsenault  |  Product Development  |  Progress Software
    email: james.arsenault@progress.com |  direct: 781-280-4934

  • One thing to note about batch processing in hibernate.  When you mix inserts and updates across tables then the batch mode does not have the effect you expect.  Only consecutive inserts or updates to the same table (using the same SQL expression) are batched.
     
    Along with  hibernate.jdbc.batch_size you might need to set hibernate.order_inserts and hibernate.order_updates to true to get performance benefits.  There is also hibernate.jdbc.batch_versioned_data flag also that has a significant impact.
     
    Eg. 
    With out ordering a batch mode execution could have the following executions
     
    Query:{[insert into Post (title, version, id) values (?, ?, ?)][Post no. 0,0,1]}
    Query:{[insert into Comment (post_id, review, version, id) values (?, ?, ?, ?)][1,Post comment 0:0,0,51]} {[insert into Comment (post_id, review, version, id) values (?, ?, ?, ?)][1,Post comment 0:1,0,52]}
    Query:{[insert into Post (title, version, id) values (?, ?, ?)][Post no. 1,0,2]}
    Query:{[insert into Comment (post_id, review, version, id) values (?, ?, ?, ?)][2,Post comment 1:0,0,53]} {[insert into Comment (post_id, review, version, id) values (?, ?, ?, ?)][2,Post comment 1:1,0,54]}
    Query:{[insert into Post (title, version, id) values (?, ?, ?)][Post no. 2,0,3]}
    Query:{[insert into Comment (post_id, review, version, id) values (?, ?, ?, ?)][3,Post comment 2:0,0,55]} {[insert into Comment (post_id, review, version, id) values (?, ?, ?, ?)][3,Post comment 2:1,0,56]}
     
    Query:{[update Post set title=?, version=? where id=? and version=?][Blog Post no. 1,1,2,0]}
    Query:{[update Comment set post_id=?, review=?, version=? where id=? and version=?][2,Blog Post comment 1:0,1,53,0]}
    Query:{[update Post set title=?, version=? where id=? and version=?][Blog Post no. 0,1,1,0]}
    Query:{[update Comment set post_id=?, review=?, version=? where id=? and version=?][1,Blog Post comment 0:1,1,52,0]}
    Query:{[update Post set title=?, version=? where id=? and version=?][Blog Post no. 2,1,3,0]}
    Query:{[update Comment set post_id=?, review=?, version=? where id=? and version=?][3,Blog Post comment 2:0,1,55,0]}
    Query:{[update Comment set post_id=?, review=?, version=? where id=? and version=?][3,Blog Post comment 2:1,1,56,0]}
    Query:{[update Comment set post_id=?, review=?, version=? where id=? and version=?][1,Blog Post comment 0:0,1,51,0]}
    Query:{[update Comment set post_id=?, review=?, version=? where id=? and version=?][2,Blog Post comment 1:1,1,54,0]}
     
     
    Whereas with the ordering the execution s would look like:
     
    Query:{[insert into Post (title, version, id) values (?, ?, ?)][Post no. 0,0,1]} {[insert into Post (title, version, id) values (?, ?, ?)][Post no. 1,0,2]} {[insert into Post (title, version, id) values (?, ?, ?)][Post no. 2,0,3]}
    Query:{[insert into Comment (post_id, review, version, id) values (?, ?, ?, ?)][1,Post comment 0:0,0,51]} {[insert into Comment (post_id, review, version, id) values (?, ?, ?, ?)][1,Post comment 0:1,0,52]} {[insert into Comment (post_id, review, version, id) values (?, ?, ?, ?)][2,Post comment 1:0,0,53]} {[insert into Comment (post_id, review, version, id) values (?, ?, ?, ?)][2,Post comment 1:1,0,54]} {[insert into Comment (post_id, review, version, id) values (?, ?, ?, ?)][3,Post comment 2:0,0,55]} {[insert into Comment (post_id, review, version, id) values (?, ?, ?, ?)][3,Post comment 2:1,0,56]}
     
    Query:{[update Comment set post_id=?, review=?, version=? where id=? and version=?][1,Blog Post comment 0:0,1,51,0]}
    Query:{[update Comment set post_id=?, review=?, version=? where id=? and version=?][1,Blog Post comment 0:1,1,52,0]}
    Query:{[update Comment set post_id=?, review=?, version=? where id=? and version=?][2,Blog Post comment 1:0,1,53,0]}
    Query:{[update Comment set post_id=?, review=?, version=? where id=? and version=?][2,Blog Post comment 1:1,1,54,0]}
    Query:{[update Comment set post_id=?, review=?, version=? where id=? and version=?][3,Blog Post comment 2:0,1,55,0]}
    Query:{[update Comment set post_id=?, review=?, version=? where id=? and version=?][3,Blog Post comment 2:1,1,56,0]}
    Query:{[update Post set title=?, version=? where id=? and version=?][Blog Post no. 0,1,1,0]}
    Query:{[update Post set title=?, version=? where id=? and version=?][Blog Post no. 1,1,2,0]}
    Query:{[update Post set title=?, version=? where id=? and version=?][Blog Post no. 2,1,3,0]}
     
     
    Setting the hibernate.jdbc.batch_versioned_data to true further reduces the number of executions:
     
    Query:{[insert into Post (title, version, id) values (?, ?, ?)][Post no. 0,0,1]} {[insert into Post (title, version, id) values (?, ?, ?)][Post no. 1,0,2]} {[insert into Post (title, version, id) values (?, ?, ?)][Post no. 2,0,3]}
    Query:{[insert into Comment (post_id, review, version, id) values (?, ?, ?, ?)][1,Post comment 0:0,0,51]} {[insert into Comment (post_id, review, version, id) values (?, ?, ?, ?)][1,Post comment 0:1,0,52]} {[insert into Comment (post_id, review, version, id) values (?, ?, ?, ?)][2,Post comment 1:0,0,53]} {[insert into Comment (post_id, review, version, id) values (?, ?, ?, ?)][2,Post comment 1:1,0,54]} {[insert into Comment (post_id, review, version, id) values (?, ?, ?, ?)][3,Post comment 2:0,0,55]} {[insert into Comment (post_id, review, version, id) values (?, ?, ?, ?)][3,Post comment 2:1,0,56]}
     
    Query:{[update Comment set post_id=?, review=?, version=? where id=? and version=?][1,Blog Post comment 0:0,1,51,0]} {[update Comment set post_id=?, review=?, version=? where id=? and version=?][1,Blog Post comment 0:1,1,52,0]} {[update Comment set post_id=?, review=?, version=? where id=? and version=?][2,Blog Post comment 1:0,1,53,0]} {[update Comment set post_id=?, review=?, version=? where id=? and version=?][2,Blog Post comment 1:1,1,54,0]} {[update Comment set post_id=?, review=?, version=? where id=? and version=?][3,Blog Post comment 2:0,1,55,0]} {[update Comment set post_id=?, review=?, version=? where id=? and version=?][3,Blog Post comment 2:1,1,56,0]}
    Query:{[update Post set title=?, version=? where id=? and version=?][Blog Post no. 0,1,1,0]} {[update Post set title=?, version=? where id=? and version=?][Blog Post no. 1,1,2,0]} {[update Post set title=?, version=? where id=? and version=?][Blog Post no. 2,1,3,0]}
     
     
    So we go from 15 to 11 to 4 executions.  Depending on the specific situation these flags have significant effect on the hibernate batch processing.  The ordering might reduce the number of SQL executions at the expense of the SQL sorting.  So there is tradeoff that needs to be made based on the specific case.  Hibernate also recommends the batch size to be reasonable as well (between 5 and 30).
     
    I hope this helps.
     
    -Prashant
     

    Prashant Thumma | Software Architect - Progress Corticon | pthumma@progress.com

  • Thank you both for your answers!

    @Jim: What you're stating can be true. But I've seen at different customers that in one rulesheet multiple entities are created; all the same. It would be very interesting to be able to handle them in little batches.

    @Prashant: The samples you show seems to refer to the blog link/similar example. But can the same effect be achieved using Corticon Studio? I created a small project with a rulesheet creating entities. But insert queries are not combined (as far as I can see).

    Actually, I've used the settings with the HPBP and did see reduction of executions and better performance (20% reduction in time).

    So, the question remains how to achieve these settings to work with Corticon.