Uploaded image for project: 'Nuxeo Platform'
  1. Nuxeo Platform
  2. NXP-9381

Improve SQL response time for big result by adding an explicit LIMIT

    XMLWordPrintable

    Details

    • Type: Improvement
    • Status: Resolved
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s: 5.5
    • Fix Version/s: 5.6-RC1, 5.6
    • Component/s: Core VCS

      Description

      Prepared statement with result set of type TYPE_SCROLL_INSENSITIVE don't use a database cursor (at least under PostgreSQL).
      This means that all results are returned at the JDBC level even if we gets only few of them.

      When returning thousands of results, this impacts the traffic and memory footprint.

      Forcing a cursor usage (TYPE_FORWARD_ONLY + setFetchSize) is not a good option because:

      • PostgreSQL will use named statement that may have less optimized query plan and use more
        database resource
      • Using a limit can give a better query plan

      The drawback is that we don't have anymore a total count if the number of results is greater than the limit.
      This may be acceptable or not, this improvement should be optional.

        Attachments

          Issue Links

            Activity

              People

              • Assignee:
                bdelbosc Benoit Delbosc
                Reporter:
                bdelbosc Benoit Delbosc
                Participants:
              • Votes:
                0 Vote for this issue
                Watchers:
                0 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved: