0

I'm trying to send in large IN clause (possibly in thousands) in a Criteria Query using JPA and the query has reached its limit and ran out resources in SQLServer (error 8623).

The query processor ran out of internal resources and could not produce a query plan. This is a rare event and only expected for extremely complex queries or queries that reference a very large number of tables or partitions. Please simplify the query. If you believe you have received this message in error, contact Customer Support Services for more information.

I'm aware that this is not a good practice to send in large set in IN clause.

As the results are paginated, partitioning and aggregating the results are not possible.

TypedQuery<T> typedQuery = entityManager.createQuery(criteriaQuery)
        .setLockMode(LockModeType.NONE)
        .setHint(QueryHints.READ_ONLY, true)
        .setFirstResult(0)
        .setMaxResults(10);

I'm looking for possible solutions and suggestions to overcome this limit.

Sivaram Kumar
  • 321
  • 3
  • 11
  • Use a table type parameter. – Thom A Apr 17 '23 at 08:00
  • @Thom A There is no support for table valued functions in Hibernate – Sivaram Kumar Apr 17 '23 at 08:17
  • You could try taking a look at this? https://stackoverflow.com/questions/68481838/limit-of-in-clause-in-jpa and the associated https://stackoverflow.com/questions/52363704/jpa-and-1000-id-use-in-oracle-in-operator/52363760#52363760 – experiment unit 1998X Apr 17 '23 at 08:24
  • If there's no support for TTP's then you'll probably want to use a temporary table; `INSERT` all the values into the table and then use an `IN`/`EXISTS` against that temporary table instead. If the table is likely to contain thousands of values, then ensuring you create an `INDEX` on that temporary table probably isn't a bad idea either. – Thom A Apr 17 '23 at 08:29
  • Oracle does not support IN clause with more than 1000 items, then you can split your set of items in 999-item subsets and forge a query as `attr IN (subset1) OR attr IN (subset2) OR ...` – Pierre Demeestere Apr 17 '23 at 14:39

0 Answers0