Python调用SQL Server 2017存储过程耗时超SSMS3倍的优化问询

针对你遇到的SSMS运行存储过程仅需5小时，但Python调用耗时是其3倍的问题，我整理了几个无需把循环逻辑迁移到Python的优化方向，帮你定位和解决瓶颈：

1. 对齐事务设置，和SSMS保持一致 #

SSMS默认用的是READ COMMITTED隔离级别，而Python的pyodbc连接可能因为默认设置不同，导致性能差异。你可以在连接时显式指定隔离级别，确保和SSMS环境一致：

import pyodbc
from pyodbc import SQL_TXN_READ_COMMITTED

# 建立连接时显式配置隔离级别和自动提交
conn = pyodbc.connect(
    f"DRIVER={{ODBC Driver 17 for SQL Server}};SERVER={server};DATABASE={db};Trusted_Connection=yes;",
    autocommit=False
)
conn.set_attr(pyodbc.SQL_ATTR_TXN_ISOLATION, SQL_TXN_READ_COMMITTED)

另外，如果你的存储过程内部已经处理了事务（比如有BEGIN TRANSACTION和COMMIT语句），那Python代码里的conn.commit()其实是多余的，反而会增加额外开销——试试去掉这一行，看看性能会不会提升。

2. 优化存储过程的事务策略，避免大事务拖垮性能 #

你怀疑提交机制是瓶颈，这点很可能命中了问题核心。如果存储过程把20000个场景的操作都塞进一个大事务里，会导致日志文件压力陡增、锁持有时间过长，自然跑的慢。你可以修改存储过程，改成分批提交的方式：

DECLARE @BatchSize INT = 1000; -- 每处理1000个场景就提交一次
DECLARE @Counter INT = 0;

-- 用FAST_FORWARD游标，比默认游标性能更高
DECLARE your_scene_cursor CURSOR FAST_FORWARD FOR 
-- 这里放你原来的游标查询语句

OPEN your_scene_cursor;
FETCH NEXT FROM your_scene_cursor INTO @scene_param1, @scene_param2; -- 替换成你的参数

WHILE @@FETCH_STATUS = 0
BEGIN
    -- 处理当前场景的插入逻辑
    INSERT INTO target_table (...) VALUES (...); -- 你的插入语句

    SET @Counter += 1;
    -- 每到批次大小就提交一次
    IF @Counter % @BatchSize = 0
    BEGIN
        COMMIT TRANSACTION;
        BEGIN TRANSACTION;
    END

    FETCH NEXT FROM your_scene_cursor INTO @scene_param1, @scene_param2;
END

-- 提交最后一批未完成的操作
IF @Counter % @BatchSize != 0
BEGIN
    COMMIT TRANSACTION;
END

CLOSE your_scene_cursor;
DEALLOCATE your_scene_cursor;

同时，把游标改成FAST_FORWARD类型（只读、单向），比默认的静态游标效率高不少。

3. 调整Python连接的配置细节 #

• 用最新的ODBC驱动：确保你用的是ODBC Driver 17 for SQL Server，旧版本的驱动可能存在性能缺陷，拖慢调用速度。
• 禁用MARS（如果开了的话）：如果连接字符串里有MARS_Connection=Yes，改成MARS_Connection=No——MARS主要是为了多结果集场景优化，单一长运行存储过程反而可能因为它增加开销。
• 用游标对象执行存储过程：虽然差异不大，但试试用游标执行代替直接用连接执行，可能更稳定：

  cursor = conn.cursor()
  cursor.execute("exec dbo.sp_loop_cursor_execute_insert")
  # 存储过程内部已提交的话，这里不用commit
  cursor.close()
  

4. 从SQL Server端排查瓶颈 #

有时候问题不在Python，而是SQL Server在处理Python调用时出现了额外的阻塞或资源瓶颈：

• 查看等待统计：运行SELECT * FROM sys.dm_os_wait_stats ORDER BY wait_time_ms DESC;，重点看有没有LOG_WRITE（日志写入慢）、PAGEIOLATCH_*（磁盘IO瓶颈）这类等待，针对性优化（比如扩容日志文件、提升磁盘性能）。
• 用Extended Events或SQL Server Profiler对比SSMS和Python调用时的执行差异，看看是不是Python调用时出现了额外的锁、阻塞情况。

内容的提问来源于stack exchange，提问作者sqlnewbie1979