Hibernate - First Sql insert takes a long time

1
votes

I'm trying to insert a record in DB using Hibernate. The data gets saved in to multiple tables in the DB. On the hibernate side I have a parent Entity class with one-to-one and one-to-many mapping to other Entity classes. In debug mode, I could see that the save operation results in multiple sql inserts. The first insert sql takes a long time, approximately 300 milliseconds. Please note: This does not include time taken for Session Initialisation, Obtaining JDBC connection etc. 10:46:24.132 [main] DEBUG org.hibernate.SQL - insert into MY_SCHEMA_NAME.PARENT_ENTITY (COLUMN1, COLUMN2, COLUMN3, COLUMN4, COLUMN5, COLUMN6, COLUMN7, COLUMN8, COLUMN9, COLUMN10, COLUMN11, COLUMN12, COLUMN13, COLUMN14) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)

The same sql if I execute from any other tool ( Oracle SQL developer ) it takes about 20 milliseconds.

The subsequent sql inserts executed by hibernate takes only about 15-20 milliseconds.

The question is, why the fist sql insert in Hibernate takes so much time, nearly 10 times when compared to subsequent sql inserts?

1
sqlhibernate

1 Answers

0
votes

To answer this question you need to learn:

In short: when the SQL query is coming from the client to the database for the very first time, then the database is performing some additional steps before the execution of this statement (see the first link). After this very first time, the sql plan for this statement is placed into the shared pool (kind of cache), and the database can skip a few of the most time consuming tasks (hard parsing - optimization and row source generation) for all subsequent requests for this concrete query - this process is called "soft parse" in the diagram from the above link.
If the shared pool is cleared (for example after the database restart), these steps must be repeated again for the first incoming query - and this take an additional time.
The statement is flushed out from the cache when some table/view referenced by the query is changed (for example after ALTER TABLE command, or CREATE/DROP INDEX command), and the database is performing the hard parse again after this, and this take an additional time again.

On the client side when the statement is executed for the first time, it is placed in the cache (see the second link) - and this must take some additional time. After this, for all subsequent statement invocation the statement is retrieved from the cache - and this improves performance.
When the database driver is closed (for example on the application restart), the cache is cleared, and the next statement invocation must again take an additional time.
You can explicitely disable statement caching (see the second lnk for detailed instruction) and most likely you will see that all executed statements will take more time.