Hibernate Pagination: HQL vs. Native SQL Performance Trade-off

Answer

For most workloads, use Hibernate’s setFirstResult() and setMaxResults(); native SQL pagination only yields a modest performance edge and can break session caching and entity identity.

Likely explanation

Hibernate translates setFirstResult/setMaxResults into a sub‑query with LIMIT/OFFSET. The database must materialize the full result set before applying the offset, so the cost grows with the offset value regardless of query origin. Native SQL can embed LIMIT or OFFSET directly, avoiding the sub‑query wrapper, but still suffers from the same scan‑and‑discard cost for large offsets. The theoretical ceiling difference is therefore small—both approaches converge on the same OFFSET‑driven performance cliff, and native SQL may shave a few milliseconds on very large result sets.

Confirmed facts

• HQL pagination adds an extra ORM layer that materializes rows before slicing, increasing CPU and memory pressure.
• Native SQL bypasses that layer, allowing the optimizer to use index‑only scans when the LIMIT clause matches an indexed order.
• Both methods degrade sharply when the OFFSET exceeds a few thousand rows; the database must read and discard many rows.
• Hibernate 5.2+ and 6.x standardized the translation of setFirstResult/setMaxResults, making version‑specific bugs unlikely, but the fundamental OFFSET limitation remains.

Steps for this case

1. Enable Hibernate SQL logging and capture the generated query for both HQL and native SQL on the same dataset.
2. Run EXPLAIN ANALYZE with increasing OFFSET values to measure the scan cost for each approach.
3. Benchmark latency and connection‑pool usage across page numbers 1, 10, 100, 1000 using a representative data set.
4. Verify the Hibernate version and dialect class to confirm that setFirstResult/setMaxResults are correctly translated.

Missing diagnostic

Please provide the exact Hibernate version and the database dialect you are using, as this determines whether the standard pagination API is fully reliable and whether native SQL can safely bypass caching.