Single row select query: any configuration to improve performance vs pyodbc?

[Mauro Antonino (MauroAntonino)]

Hi! I've been evaluating whether to adopt mssql-python for the Python services at the company I work for, and built a simple reproducible benchmark to measure our own workload: https://github.com/MauroAntonino/pyodbc-vs-mssql-python

Most scenarios favour mssql-python. The one we don't understand is the one we run most: a single-row SELECT. Can you help us understand if something is misconfigured on our side?

[Tongyi (TongyiDai)]

The benchmark does not look misconfigured. It is measuring a real case where mature pyodbc can be faster: one connection and cursor, one parameterized execute() plus fetchone() at a time, with only one ten-column row returned.

The important clue is the shape of the results, not just the ratio:

• pyodbc: about 0.175 ms/query
• mssql-python 1.14.0: about 0.256 ms/query
• difference: about 81 microseconds per call

The same repository shows mssql-python winning once more rows are amortized per call and when threads are added. That strongly suggests fixed per-execution/per-row driver overhead rather than network, query plan, connection setup, or a missing pooling switch.

The current mssql-python path already reuses the statement handle and prepared plan for repeated identical SQL. However each execute() still resets the statement, binds/executes, calls row-count and column-description APIs, rebuilds description-related state, and fetchone() retrieves/decodes every column into a Python Row. When the SQL Server and client are on the same host and the indexed query itself is extremely cheap, those fixed costs dominate. arraysize cannot help fetchone(), and connection pooling cannot help because your connection is already reused.

Two useful measurements would make this actionable for the maintainers:

1. Time execute() and fetchone() separately for both drivers.
2. Repeat with SELECT id and then add columns one by one. If the gap grows with column count, conversion/metadata is the likely part; if it is already present for one integer, execution/reset/metadata is more likely.

Also test a stored procedure or set-based request that returns several needed point lookups at once. For production, batching/chunking queries or using concurrency will usually save much more than a connection-string option. If the workload truly must remain synchronous one-row RPCs, keeping pyodbc for that path is a reasonable engineering choice; mssql-python's advantages are workload-dependent, not a promise that every DB-API call is faster.

Related maintainers' discussion: #554

[Mauro Antonino (MauroAntonino)]

Thanks! I ran both. Median of 3 runs, 10k iterations each.

Turns out both of your conditions are true at once. With SELECT id the gap is already there (0.038 ms), and by 10 columns it's about double (0.077 ms). Per extra column, execute() grows ~1.3 µs for pyodbc and ~5.6 µs for mssql-python.

Timing the two calls separately, the cost is in execute(), not fetchone(). At 10 columns pyodbc spends 0.156 ms in execute and 0.012 in fetch; mssql-python 0.233 and 0.017. So execute is 94% of the gap, and fetch is only ~7% of total time for both, decoding the columns into a Row doesn't look like the expensive part.

I also tried something on the statement reuse you mentioned: same 10-column query, but with a trailing -- variant N comment so no two calls share the same text. pyodbc went from 0.156 to 0.269 ms (+72%), mssql-python from 0.233 to 0.262 (+12%), and with no text repeating the two end up the same. Made me wonder if mssql-python gets less benefit from repeated SQL text than pyodbc does which would be room to improve rather than a fixed cost.

Numbers and config in scenario 5: https://github.com/MauroAntonino/pyodbc-vs-mssql-python

[Tongyi (TongyiDai)]

Mauro Antonino (@MauroAntonino) Those measurements are very useful. They move the conclusion from “single-row work has fixed overhead” to “the remaining difference is concentrated in the repeated-text execute() path.”

The comment-variant experiment is a strong lead, with one caveat: changing the SQL text changes both any client-side statement reuse and the server-side batch/plan-cache path. It does not yet isolate which layer gives pyodbc the larger repeated-text gain. Your separate execute()/fetchone() timings do rule out row decoding as the primary explanation.

For a maintainer-facing issue, I would report exactly these three observations:

1. the gap is present for SELECT id and grows by about 4.3 microseconds per added column;
2. execute() accounts for about 94% of the ten-column delta;
3. varying only a trailing comment removes most of the pyodbc advantage.

A valuable next control is to compare both drivers with a deliberately non-cacheable server execution mode, then compare the repeated-text and variant-text cases again. That would distinguish client statement reuse from SQL Server plan-cache effects. At this point the benchmark is strong enough to file as a focused performance issue with the scenario-5 link and environment details, rather than looking for a connection-string tuning switch.