Uploaded image for project: 'Hibernate ORM'
  1. HHH-11260

Apply fetch-size optimization for result streaming with MySQL

    Details

    • Type: Improvement
    • Status: Resolved
    • Priority: Major
    • Resolution: Rejected
    • Affects Version/s: 5.2.0
    • Fix Version/s: None
    • Component/s: query-hql
    • Labels:
      None
    • Environment:
      MySQL, Forward-only resultsets, Read-only statements
    • Last commented by a user?:
      true

      Description

      Hibernate 5.2 introduced Query.stream to consume results as stream. A user wanting to benefit from iterative streaming is required to set Query.setFetchSize(Integer.MIN_VALUE) otherwise the ResultSet is entirely stored in memory:

      Quote from MySQL JDBC docs:

      By default, ResultSets are completely retrieved and stored in memory. In most cases this is the most efficient way to operate and, due to the design of the MySQL [...] If you are working with ResultSets that have a large number of rows [...] you can tell the driver to stream the results back one row at a time.

      stmt = conn.createStatement(java.sql.ResultSet.TYPE_FORWARD_ONLY,java.sql.ResultSet.CONCUR_READ_ONLY);
      stmt.setFetchSize(Integer.MIN_VALUE);
      

      Users could participate from this optimization if the query yields to a Read-Only cursor and Forward-Only result set by defaulting to Query.setFetchSize(Integer.MIN_VALUE) (on query level) if no other fetch size settings are applied.

      Doing so imposes a caveat:

      There are some caveats with this approach. You must read all of the rows in the result set (or close it) before you can issue any other queries on the connection, or an exception will be thrown.

      Reference

        Attachments

          Activity

            People

            • Votes:
              1 Vote for this issue
              Watchers:
              3 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved: