CODEWITHSUNDEEP
mysqlperformancehibernateoptimizationhql
Angga
Angga

Reputation: 2323

how to make HQL that will generate SQL to insert multiple values in one statement?

I need hibernate to generate sql like this INSERT INTO table_a (a_id, a_name) VALUES (5, 'a5'),(6, 'a6');.

With sql like that you can add 2 row with 1 statement. I can get 

a_id, a_name

------------------
5     a5
6     a6

When in hibernate, when you save set of one to many relationship, hibernate will insert with multiple insert statement. This will cause if you insert 1000 row to 1 table with HQL will resulted in something like this :

INSERT INTO scoring.table_a (`a_id`, `a_name`) VALUES (1, 'a');
INSERT INTO scoring.table_a (`a_id`, `a_name`) VALUES (2, 'a');
....
...
..
INSERT INTO scoring.table_a (`a_id`, `a_name`) VALUES (1000, 'a');

And the elapsed time is :

Executed 1,000 queries; elapsed time (seconds) - Total: 0.78, SQL query: 0.78, Building output: 0

And when i test with the same values, use SQL INSERT INTO table_a (a_id, a_name) VALUES (5, 'a'),(6, 'a'),(),...,...,(1000, 'a'); will resulted in elapsed time like this :

Query 1 of 1, Rows read: 0, Elapsed time (seconds) - Total: 0.02, SQL query: 0.02, Building output: 0

The result of my test is, 1 statement of 1000 value(0.02s) will be about 39 times faster than 1000 insert statement with each have 1 value(0.78s) like hibernate do. So is there a way to make HQL that generate SQL like that for insert or maybe update also. Or this is means we must Override the hibernate dialect?

Thanks for the any hint

Upvotes: 7

Views: 4138

Answers (3)

Md. Zahangir Alam
Md. Zahangir Alam

Reputation: 557

There is a very nice chapter about batch processing in the Hibernate docs.

Set the property

hibernate.jdbc.batch_size 20

Then use this code

Session session = sessionFactory.openSession();
Transaction tx = session.beginTransaction();

for ( int i=0; i<100000; i++ ) {
    Customer customer = new Customer(.....);
    session.save(customer);
    if ( i % 20 == 0 ) { //20, same as the JDBC batch size
        //flush a batch of inserts and release memory:
        session.flush();
        session.clear();
    }
}

tx.commit();
session.close();

Channge the customer objects from above code according to your need.

Upvotes: 0

Arya
Arya

Reputation: 3039

HQL supports only the INSERT INTO ……… SELECT ……… ; there is no chance to write INSERT INTO ……… VALUES, i mean while writing the insert query, we need to select values from other table, we can’t insert our own values manually. (see documentation and this)

Upvotes: 2

Merlin
Merlin

Reputation: 36

You may set hibernate.jdbc.batch_size property to a non-zero value. It will allow Hibernate to use batch INSERT. Take a look at a official documentation.

The following code is an example how to use JDBC batch inserts through Hibernate:

Session session = sessionFactory.openSession();
Transaction tx = session.beginTransaction();
for ( int i=0; i < 10000; i++ ) {
    RecordA record = new RecordA(.....);
    session.save(record);
    if ( i % BATCH_SIZE == 0 ) { // BATCH_SIZE is your choice, but equal to property
        //flush a batch of inserts and release memory:
        session.flush();
        session.clear();
    }
}
tx.commit();
session.close();

It's also discussed here: Hibernate batch size confusion

Upvotes: 0

Related Questions