加速Azure SQL数据库存储过程批量插入数据的方案咨询

针对你逐行调用存储过程插入数据速度慢的问题，以下是几种高效的优化方案：

1. 使用executemany批量执行存储过程 #

把DataFrame的行数据整理成参数列表，一次性批量执行，同时只在最后提交一次事务（原代码每次循环都提交是核心性能瓶颈之一）。

示例代码：

# 整理参数列表
params = [(row.Model, row.output, row.batchid) for index, row in df.iterrows()]

# 批量执行存储过程
cursor.executemany('exec demo_Insertdatainto_table ?, ?, ?', params)
cursor.commit()

2. 改用pandas的to_sql配合fast_executemany #

如果不需要依赖现有存储过程，直接用pandas内置的批量插入功能，开启fast_executemany可大幅提升速度（适用于pyodbc驱动）。

示例代码：

from sqlalchemy import create_engine

# 替换为你的Azure SQL连接信息
conn_str = 'mssql+pyodbc://<用户名>:<密码>@<服务器名>.database.windows.net/<数据库名>?driver=ODBC+Driver+17+for+SQL+Server'
engine = create_engine(conn_str, fast_executemany=True)

# 批量插入DataFrame到目标表
df.to_sql('demo_output', engine, schema='dbo', if_exists='append', index=False)

3. 修改存储过程支持表值参数（TVP） #

创建自定义表类型，让存储过程接收批量数据，一次插入多行，这是SQL Server批量操作的最优方案之一。

步骤1：创建表值类型 #

CREATE TYPE dbo.DemoOutputType AS TABLE (
    model int,
    output float,
    batchid int
);

步骤2：修改存储过程 #

CREATE PROCEDURE demo_BulkInsertIntoTable
@BatchData dbo.DemoOutputType READONLY
AS
BEGIN
    INSERT INTO [dbo].[demo_output](model, output, batchid)
    SELECT model, output, batchid FROM @BatchData;
END

步骤3：Python端调用批量插入 #

from pyodbc import TableValuedParam

# 直接将DataFrame转换为表值参数格式
tvp = TableValuedParam('dbo.DemoOutputType', df.itertuples(index=False, name=None))
cursor.execute("EXEC demo_BulkInsertIntoTable ?", tvp)
cursor.commit()

4. 紧急优化：减少事务提交次数 #

如果暂时无法修改存储过程或代码结构，仅把commit()移到循环外就能显著提升速度：

for index, row in df.iterrows():
    cursor.execute('exec demo_Insertdatainto_table  ?,?,?', [row.Model, row.output, row.batchid])
# 所有行插入完成后统一提交事务
cursor.commit()

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