Uploaded image for project: 'Cayenne'
  1. Cayenne
  2. CAY-1225

Controlling JDBC Driver setting "fetchSize" in a SelectQuery

    XMLWordPrintableJSON

Details

    • Improvement
    • Status: Closed
    • Critical
    • Resolution: Fixed
    • 2.0 branch
    • 3.0 beta 1, 3.0
    • Core Library
    • None

    Description

      ResultIterator is a nice API for dealing with large SELECT queries, unfortunately some JDBC drivers that don't automatically use server-side cursors (eg PostgreSQL JDBC driver) wont'give access to the ResultSet (at JDBC level) until the whole query has finished executing and all rows have been returned.

      It basically mean that if you execute a SelectQuery returning 1'500'000 rows, there's a high chance of getting a OutOfMemoryException in the JDBC driver code (Statement.Excecute), before Cayenne even gets a chance to call the ResultSet.Next().

      It makes the ResultIterator API much less efficient than it could be because :
      -Even if rows are processed one by one, they need to be stored in local memory, causing heap size issues.
      -In some scenarios, user code could start working with the resulting rows immediately as they become available, while the select query is still being executed by the database engine.

      To solve this problem, there should be a way to have the SelectQuery object calling Statement.setFetchSize() before it executes.
      A SelectQuery.setFetchSize( int ) method would be perfect because it's often needed to configure this setting on a per-query basis.

      Attachments

        1. SelectAction.java
          7 kB
          Stephane Claret
        2. SelectQuery.java
          21 kB
          Stephane Claret

        Activity

          People

            antidote Andrey Razumovsky
            stefcl Stephane Claret
            Votes:
            0 Vote for this issue
            Watchers:
            0 Start watching this issue

            Dates

              Created:
              Updated:
              Resolved: