Uploaded image for project: 'MariaDB Connector/J'
  1. MariaDB Connector/J
  2. CONJ-1300

Implement prepareThreshold to Reduce Unnecessary Server-Side Prepared Statements

    XMLWordPrintable

Details

    • New Feature
    • Status: Closed (View Workflow)
    • Major
    • Resolution: Won't Do
    • None
    • N/A
    • Other
    • None

    Description

      Current Implementation

      The driver currently uses an LRU cache (size: 256) to store prepared statements. When useServerPrepStmts is enabled, every SQL statement immediately triggers a PREPARE command on the PostgreSQL server, regardless of execution frequency. This approach creates significant overhead:

      Extra processing and memory consumption on the server side
      Additional network roundtrips for each prepared statement
      Resource allocation for queries that may execute only once or twice

      Proposed Solution: prepareThreshold Implementation

      Implement a threshold-based approach option (default: 5 executions).

      Mechanism:

      1. Track execution count for each unique SQL statement
      2. Send PREPARE command only after the statement has been executed N times
      3. Use client-side execution until the threshold is reached
      4. Cache prepared statements in the existing LRU cache once the threshold is met

      Benefits:

      • Server-side preparation limited to frequently executed statements by default
      • Fewer roundtrips for infrequently-used queries

      Configuration Options
      The prepareThreshold parameter provides flexible control:

      • 0: Always prepare immediately (equivalent to current useServerPrepStmts=true behavior)
      • 1-N: Prepare after N executions (default: 5, recommended)
      • -1: Never auto-prepare (equivalent to current useServerPrepStmts=false behavior)

      replacing useServerPrepStmts option completly, (even if alias will be keeped for compatibility)

      Attachments

        Activity

          People

            diego dupin Diego Dupin
            diego dupin Diego Dupin
            Votes:
            0 Vote for this issue
            Watchers:
            2 Start watching this issue

            Dates

              Created:
              Updated:
              Resolved:

              Git Integration

                Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.