# Performance issue with Blobs in 6x

**URL:** <https://discourse.hibernate.org/t/performance-issue-with-blobs-in-6x/8030>\
**Category:** Hibernate ORM\
**Created:** [July 25, 2023, 3:57pm UTC](https://discourse.hibernate.org/t/performance-issue-with-blobs-in-6x/8030 "2023-07-25T15:57:16Z")\
**Posts on this page:** 7\
**Page:** 1

<div class="post-metadata">

**Author:** ![zeigler](https://avatars.discourse-cdn.com/v4/letter/z/90ced4/32.png) [@zeigler](https://discourse.hibernate.org/u/zeigler)\
**Post date:** [July 25, 2023, 3:57pm UTC](https://discourse.hibernate.org/t/performance-issue-with-blobs-in-6x/8030/1 "2023-07-25T15:57:16Z")

</div>

We have recently upgraded from Hibernate 5.6.15 to Hibernate 6.2.6. I have a series of performance tests I run against my application and found that almost every metric was slowed by a factor of 3. I took one operation and looked at each query. All were pretty much the same except one query where we need to pull the contents of a document from a BLOB. The query before took approx. 10ms. In Hibernate 6 it takes 50ms. I have been trying to find why this is happening but have had no luck. My query, using Criteria, is as follows:

```auto
Session session = getSessionFactory().getCurrentSession();
CriteriaBuilder builder = session.getCriteriaBuilder();
CriteriaQuery<DocContent> docContentCriteriaQuery = builder.createQuery(DocContent.class);
Root<DocumentContent> docContentRoot = docContentCriteriaQuery.from(DocContent.class);
docContentCriteriaQuery.where(builder.equal(docContentRoot.get(docId), docId));
Query<DocContent> query = session.createQuery(docContentCriteriaQuery);
DocContent content = query.getSingleResult();
return content;

```

DocContent is a simple Java object containing a String and a Blob object. Are there any suggestions you can give me to improve our performance?

---

<div class="post-metadata">

**Author:** ![beikov](https://yyz1.discourse-cdn.com/flex035/user_avatar/discourse.hibernate.org/beikov/32/258_2.png) [@beikov](https://discourse.hibernate.org/u/beikov)\
**Post date:** [July 26, 2023, 10:19am UTC](https://discourse.hibernate.org/t/performance-issue-with-blobs-in-6x/8030/2 "2023-07-26T10:19:12Z")

</div>

One big thing that changed ORM 6 is the way criteria queries are handled behind the scenes. Since criteria queries are not translated to HQL first and then re-parsed, but instead translated directly, query plans are not cached anymore. So this is a potential slowdown. To still allow the caching, you can mark the criteria query as immutable with a session property `session.setProperty(AvailableSettings.CRITERIA_COPY_TREE, false)` and cache the `CriteriaQuery` in e.g. a volatile variable, but maybe it’s easier to just use HQL instead: `session.createSelectionQuery("from DocContent d where d.docId = :docId").setParameter("docId", docId).getSingleResult();`

---

<div class="post-metadata">

**Author:** ![zeigler](https://avatars.discourse-cdn.com/v4/letter/z/90ced4/32.png) [@zeigler](https://discourse.hibernate.org/u/zeigler)\
**Post date:** [July 26, 2023, 4:58pm UTC](https://discourse.hibernate.org/t/performance-issue-with-blobs-in-6x/8030/3 "2023-07-26T16:58:45Z")

</div>

I believe you are on the right track here. I applied the session property to this one query. Performance improved from 50ms to 15ms. This is more like what we normally saw in hibernate 5.x. I attempted to set this in my hibernate properties but it didn’t seem to take. When I ran the entire suite of tests performance was still poor. I am convinced we are on the right track, but just did not put it in the right place. Is there a global setting I can use for the entire system? Or must I add it to every query?

---

<div class="post-metadata">

**Author:** ![beikov](https://yyz1.discourse-cdn.com/flex035/user_avatar/discourse.hibernate.org/beikov/32/258_2.png) [@beikov](https://discourse.hibernate.org/u/beikov)\
**Post date:** [July 26, 2023, 5:10pm UTC](https://discourse.hibernate.org/t/performance-issue-with-blobs-in-6x/8030/4 "2023-07-26T17:10:48Z")

</div>

To get query plan caching you have to reuse the same `CriteriaQuery` instance and hence cache the constructed query somewhere. There is no config property (yet) that you can configure for query plan caching to happen. We are thinking about offering it, but query plan caching for dynamic queries might not be very useful and essentially trash your cache. I’ve seen a lot of OOM cases in the past that happened because the query plan cache filled with all sort of criteria query variants.

---

<div class="post-metadata">

**Author:** ![beikov](https://yyz1.discourse-cdn.com/flex035/user_avatar/discourse.hibernate.org/beikov/32/258_2.png) [@beikov](https://discourse.hibernate.org/u/beikov)\
**Post date:** [July 26, 2023, 5:26pm UTC](https://discourse.hibernate.org/t/performance-issue-with-blobs-in-6x/8030/5 "2023-07-26T17:26:09Z")

</div>

I created [[HHH-17002] - Hibernate JIRA](https://hibernate.atlassian.net/browse/HHH-17002) for this improvement which you can track.

---

<div class="post-metadata">

**Author:** ![zeigler](https://avatars.discourse-cdn.com/v4/letter/z/90ced4/32.png) [@zeigler](https://discourse.hibernate.org/u/zeigler)\
**Post date:** [July 27, 2023, 4:44pm UTC](https://discourse.hibernate.org/t/performance-issue-with-blobs-in-6x/8030/6 "2023-07-27T16:44:31Z")

</div>

How does HHH-17002 compare with this one: HHH-16782 which is marked resolved?

---

<div class="post-metadata">

**Author:** ![beikov](https://yyz1.discourse-cdn.com/flex035/user_avatar/discourse.hibernate.org/beikov/32/258_2.png) [@beikov](https://discourse.hibernate.org/u/beikov)\
**Post date:** [July 28, 2023, 6:22am UTC](https://discourse.hibernate.org/t/performance-issue-with-blobs-in-6x/8030/7 "2023-07-28T06:22:10Z")

</div>

Prior to [[HHH-16782] - Hibernate JIRA](https://hibernate.atlassian.net/browse/HHH-16782) it wasn’t possible to cache criteria query plans at all, so the fix introduced an option for caching the plan for the same criteria query instance.