Our development team has recently become worried that criteria and HQL are too slow. I ran some benchmarks to understand where the slowness is coming from.
I ran the following queries 1001 times each. The first time I run the query the entity is not cached by the session, but every time after that the entity is cached.
Entity e = (Entity) session.get(Entity.class, new EntityID("Composite key value 1", "Composite key value 2"));
First call:
80.505875
Average of next 1000 calls:
0.045958 ms
Entity e = (Entity) session.createCriteria(Entity.class)
.add(Restrictions.eq("id", new EntityID("Composite key value 1", "Composite key value 2")))
.uniqueResult();
First call:
91.489098 ms
Average of next 1000 calls:
0.847434 ms
As a side note, the same query in HQL took longer the first time, but was statistically equivalent on all subsequent calls.
I've read that the criteria to HQL and HQL to SQL translations take an insignificant amount of time compared to database queries. According to my tests, it appears that these translations take 18.4 times longer than the session get.
0.847434 / 0.045958 = 18.439314
Assuming some implementation details, I expect that the translation of the queries takes 0.801476 ms. This means the translation step takes 17.4 times as long as the session get. With JDBC we can run these queries in under 0.025368 ms. This is a conversion project that previously used ODBC, so we are looking for speeds that are comparable to what we had before. However, even with caching, it seems we are unable to approach comparable speeds.
(0.847434 - 0.045958) / 0.045958 = 17.439314
I expected that by using caching to avoid talking to the database, we would reach speeds comparable to JDBC on subsequent calls. Is it normal for criteria translation to take the better part of a millisecond to run? How do people reach speeds comparable to JDBC with caching using criteria?
Edit: My post contained a major mistake, where it appeared the same code produced two different statistics. This is now fixed.
I ran the following queries 1001 times each. The first time I run the query the entity is not cached by the session, but every time after that the session is cached.
Your assumption is wrong. When using Criteria API, the criteria query is not cached in the persistence context. Even though you are using the id property, an SQL query is performed. Whereas when using get method, the object is indeed maintained in the first level cache (persistence context).
You can verify it by enabling show_sql=true. For the first use case you will see a single query printout. For the criteria, you will see 1000 queries.
The reason that the first warm-up invocation takes significantly slower is because a connection has to be obtained from pool / created, a statement maybe cached etc.
Saying that, adding setCacheable(true) to the criteria, you will see the average time dropping close to the level of calling get.