Hacktoberfest 2026：维护者为十月标记出来的 issue，仍然开放、适合新手。 浏览 Hacktoberfest issue

cursor.execute() with many bound parameters (~2000) is ~14x slower than pyodbc

未关闭
  • 哪里不对？
#500 2 条评论 2 个 reaction 已指派 1 人 在 GitHub 查看

维护者通常 2 天内回复

@bewithgaurav 已经在做这个了。

开始于 2026年4月9日。

  • #759 来自 @bewithgaurav —— 未关闭
  • #761 来自 @bewithgaurav —— 未关闭

评估

这个 Issue 还没有评估数据。

描述

area: performance triage done under development
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
主要语言
Python
星标
478
派生
62
平均合并
3 天 19 分钟
30 天内合并 PR
28

环境准备

在 Codespaces 中打开

在浏览器里用你自己的 GitHub 账号启动这个项目的开发容器。

从这里开始

  1. 先读完整个 Issue，再读项目的贡献指南。
  2. 在 Issue 下留言说明你要接手 —— 这能避免两个人做同样的事。
  3. Fork 仓库，在一个分支上完成修改。
  4. 提交 Pull Request，并在描述里引用这个 Issue 编号。

microsoft/mssql-python 的其他 Issue

查看 microsoft/mssql-python 的全部 Issue

相似的 Issue

更多 Python Issue

把新 issue 发到你的邮箱

精选适合新手参与的 GitHub issue 摘要。