0

What makes this query perform differently?

CriteriaBuilder builder;
CriteriaQuery criteria;
Root root;

criteria.where(builder.anyOtherCondition()); // runs at a reasonable speed
criteria.where(builder.isNull(root.get("name"))); // takes years

It's as though hibernate is fetching all the data in the database.

Sally Mwalr
  • 1
  • 2
  • 2
    I'd suggest checking what SQL statement is generated. But some DBMSs are slow with IS NULL. Another thing you could try is putting an index on the field you are running isNull() on. The database may need to table scan to find out which values are null if there's no index. Ie. your problem is probably a misuse of the DB not hibernate itself. – AminM Jan 20 '22 at 17:46
  • Amin, let me try double-checking your recommendation. We of course have a UNIQUE INDEX on that column. And the query is optimal enough when run through PostgreSQL. The query that's generated by hibernate is missing LIMIT clause am wondering if this is being sent through other ways or whether it fetches the entire data and ignoring fetchSingle – Sally Mwalr Jan 28 '22 at 17:02
  • I answered below – AminM Jan 29 '22 at 19:31

0 Answers0