Query gets slower after multiple calls

Hibernate ORM
1

Hello,
i have run into issues with hibernate native query.

I have pretty complex query consisting of multiple unions and many where clauses. The query works fine for about 10 calls but then slows down significantly. Changing simple param(for example removing single value from “IN” condition) makes the query fast again. The database queries takes the same amount of time but it seems like the building of the query is much slower - according to “generate_statistics” logging.

The code looks like this:

NativeQuery hibernateQuery = (NativeQuery) entityManager.createNativeQuery(
        aggregationQueryBuilder.query.toString(),
        "AggregationSearchResult"
    )
        .setFirstResult(offset)
        .setMaxResults(size);

    addQueryParameters(hibernateQuery, queryParams);

    List<AggregationSearchResult> result = hibernateQuery.getResultList();

addQueryParams looks like this:

private void addQueryParameters(NativeQuery query, HashMap<String, Object> queryParams) {
    queryParams.forEach(
        (name, value) -> {
          if (value instanceof Collection) {
            query.setParameterList(name, (Collection) value);
          } else if (value instanceof Object[]) {
            query.setParameterList(name, (Object[]) value);
          } else if (value instanceof LockMode) {
            query.setLockMode(name, (LockMode) value);
          } else {
            query.setParameter(name, value);
          }
        }
    );

where the “AggregationSearchResult” is defined using @ConstructorResult. I am not aware of using any kind of caching, even thought it seems to be some kind of caching problem since changing the params helps.

Using hibernate 6.2.5 and spring boot 3.1.1. Upgrading to hibernate 6.4 did not fix the issue.

Any kind of help or hint where to look would be appreciated.

2

Without seeing the actual query and parameters it’s hard to tell, but know that binding a parameter list (Object[] or Collection) incurs an additional cost, because the SQL string must be adapted to fit the amount of parameter list elements. When a different SQL query is generated, this means the database will have to hard parse that string and re-optimize it.
To circumvent this, you can use in clause parameter padding. Alternatively you could use an array parameter if your database supports that, but that requires more work.

3

Thanks for the response. After more digging it seems to not be related to hibernate since the problem persist with just pure JDBC. For some reason processing/polling of the data slows down after multiple requests.