Skip to content

PERF: cursor.execute resets parameter bindings on every call, forcing drivers to re-prepare (sp_prepexec) instead of sp_execute #885

Closed

Description

@Theekshna

Is your feature request related to a problem? Please describe.

Re-executing the same parameterized SQL on a cursor re-prepares the statement on the server on every call, with both msodbcsql 18 and mssql-odbc.

cursor.execute(sql, params) calls SQLFreeStmt(SQL_RESET_PARAMS) twice per execute:

SQL_RESET_PARAMS clears the IPD. Both drivers treat a cleared IPD as a parameter-definition change and drop their prepared handle:

  • msodbcsql: SQLBindParameter compares the new IPD record against a snapshot of the old one (ParamInfoSnapshot::FHasChanged: direction, SQL type, char/binary length, numeric precision/scale). A record re-created after RESET_PARAMS has an empty snapshot, so the driver sets RE_PREPARE.
  • mssql-odbc: StmtState::apply_parameter_definition_changes orphans the prepared handle when the IPD record count or a record's definition changes.

So the cursor sends sp_prepexec (the full SQL text plus the @params declaration) every time, instead of sp_execute against the prepared handle. _soft_reset_cursor keeps the HSTMT specifically to reuse the prepared plan, but the reset defeats that.

Each execute also sets SQL_ATTR_CURSOR_TYPE / SQL_ATTR_CONCURRENCY again. Once the statement is prepared, the DM rejects both with HY011, so these calls are wasted work.

Evidence (local Windows, SQL Server 2022, rpc_completed Extended Events):

  • Python sequence: execute(sql, (1, 10)), (2, 10), (3, 10), then (1000, 10), which changes TINYINT to SMALLINT.

    • Today, both drivers: sp_prepexec, sp_prepexec, sp_prepexec, sp_prepexec.
    • Expected: sp_prepexec, sp_execute, sp_execute, sp_prepexec.
  • A C++ replay of mssql-python's exact ODBC call sequence (1000 int parameters) measured the request size and cost:

    Today (sp_prepexec every op) Without the reset (sp_execute)
    Request size 75–78 KB 27 KB
    Extra cost, n=1000 +1.3 to 1.5 ms/op —
    Extra cost, n=100 about +195 µs/op —

    The overhead is about the same for both drivers. The server compiles only once either way; the cost is client CPU and wire bytes.

Describe the solution you'd like

Reset parameter bindings only when correctness requires it:

  • Keep the bound buffers owned by the statement handle, not a call-local vector. They then stay valid until they are rebound or the statement is freed, and the post-execute RESET_PARAMS is no longer needed.
  • On re-execute of the same prepared SQL, compare the new parameter definitions with the previous binding: direction, C type, SQL type, column size, decimal digits, and data-at-execution.
    • If they are identical, rebind the new values over the existing records. The IPD is unchanged, so the driver keeps its prepared handle and sends sp_execute.
    • Otherwise, reset first, exactly as today.
  • Keep resetting in these cases:
  • Set CURSOR_TYPE / CONCURRENCY once per statement, before it is prepared.

Describe alternatives you've considered

  • Skip only the post-execute reset and leave the APD pointing at freed buffers. Drivers read input buffers only during SQLExecute/SQLParamData, but dangling APD pointers are a latent hazard. Owning the buffers costs nothing extra.
  • Rebind without any definition comparison. Both drivers already re-prepare by themselves when a definition changes. But mssql-python also sets descriptor fields outside SQLBindParameter (numeric APD fields, UDT describe), so resetting on any definition change is the conservative choice.

Additional context

  • Not addressed here: value-dependent integer typing (TINYINT/SMALLINT/INT/BIGINT by value range) still changes the declaration when values cross a range boundary, which forces a re-prepare. That is a separate behaviour change.
  • A PR implementing the proposal will follow.

Activity

  1. github-actions commented on Nov 8, 2026

    @github-actions

    Hi ttk (@Theekshna), thank you for opening this issue!

    Our team will review it shortly. We aim to triage all new issues within 24-48 hours and get back to you.

    If you have additional information to share, please feel free to update the issue.

    Thank you for your patience!

  2. jahnvi480 commented on Nov 12, 2026

    @jahnvi480
    Contributor

    PR has been merged to main closing this issue.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment

Metadata

Metadata

Assignees

No one assigned

    Type

    No type

    Projects

    No projects

      Milestone

      No milestone

      Relationships

      None yet

      Development

      No branches or pull requests

      Issue actions