cursor.execute() with many bound parameters (~2000) is ~14x slower than pyodbc #500
Description
Describe the bug
When executing a multi-row INSERT with ~2000 bound parameters (the kind SQLAlchemy's insertmanyvalues generates), cursor.execute() is about 14x slower than the equivalent call through pyodbc.
cursor.executemany() is much closer between drivers (~1.6x), so the issue is specific to single cursor.execute() calls with large parameter counts.
To reproduce
import time
import mssql_python
import pyodbc
N = 100_000
COLS = 2
# ~1049 rows per INSERT, ~2098 params per call, ~95 calls total
ROWS_PER_BATCH = 2099 // COLS
SQL = "INSERT INTO bench (id, name) VALUES " + ",".join(
[f"(?, ?)"] * ROWS_PER_BATCH
)
PARAMS = [None] * (ROWS_PER_BATCH * COLS)
# --- mssql-python ---
conn = mssql_python.connect(
"SERVER=localhost;DATABASE=master;UID=sa;PWD=YourPassword;"
"Encrypt=yes;TrustServerCertificate=yes;"
)
cursor = conn.cursor()
cursor.execute(
"IF OBJECT_ID('bench') IS NOT NULL DROP TABLE bench;"
"CREATE TABLE bench (id INT, name VARCHAR(50))"
)
conn.commit()
t0 = time.perf_counter()
for _ in range(N // ROWS_PER_BATCH):
cursor.execute(SQL, PARAMS)
conn.commit()
ms_time = time.perf_counter() - t0
cursor.close()
conn.close()
# --- pyodbc ---
conn2 = pyodbc.connect(
"DRIVER={ODBC Driver 18 for SQL Server};"
"SERVER=localhost;DATABASE=master;UID=sa;PWD=YourPassword;"
"Encrypt=yes;TrustServerCertificate=yes;"
)
cursor2 = conn2.cursor()
cursor2.execute("TRUNCATE TABLE bench")
conn2.commit()
t0 = time.perf_counter()
for _ in range(N // ROWS_PER_BATCH):
cursor2.execute(SQL, PARAMS)
conn2.commit()
py_time = time.perf_counter() - t0
cursor2.close()
conn2.close()
print(f"mssql-python: {ms_time:.2f}s ({N / ms_time:,.0f} rows/s)")
print(f"pyodbc: {py_time:.2f}s ({N / py_time:,.0f} rows/s)")Output
mssql-python: 11.87s (8,427 rows/s)
pyodbc: 0.77s (130,000 rows/s)
Context
This came up while working on SQLAlchemy integration. SA 2.x uses insertmanyvalues by default, which generates batched multi-row INSERTs with ~2098 parameters per call (limited by SQL Server's 2100 parameter cap).
For reference, cursor.executemany() is much faster on this driver (~50K rows/s) and cursor.bulkcopy() is excellent (~390K rows/s). It's really just the many-parameter cursor.execute() path that's slow.
Environment
- mssql-python: 1.4.0
- pyodbc: 5.2.0
- SQL Server: 2022
- Python: 3.12
- OS: Ubuntu 24.04