启用fast参数后，Pandas写入MS SQL Server仍过慢的问题排查

尝试将约20万条记录的Pandas DataFrame写入Azure Synapse，先后使用PYODBC、SQLAlchemy等多种方法，包括启用SQLAlchemy.engine的fast_executemany选项、cursor的executemany方法及turbodbc，但写入速度始终仅约10条/秒，甚至插入1000条数据耗时约130秒。DataFrame包含11列，除一列外其余均为float类型。根据文档，fast_executemany应将整个DataFrame加载至内存，但操作期间电脑内存占用无变化。

代码示例 #

import pandas as pd
import sqlalchemy
import pyodbc
from sqlalchemy.engine import URL, create_engine

conn = pyodbc.connect('Driver=ODBC Driver 17 for SQL Server;'
                      'Server=SERVERNAME;'
                      'Database=DATABASENAME;'
                      'MARS_Connection=yes;'
                      'UID=USER;'
                      'PWD=PASS;')

connstring = "Driver={ODBC Driver 17 for SQL Server};Server=SERVERNAME;Database=DATABASENAME;UID=USER;PWD=PASS;"

conurl = URL.create("mssql+pyodbc", query={"odbc_connect":connstring,'autocommit':'True'})

dbEngine = sqlalchemy.create_engine(conurl, fast_executemany=True)

# 示例仅保留2列
d = {'userid': ['21005395', '20101499'], 'col1': [0.1, 0.25]}
df = pd.DataFrame(data=d)

# 插入1000条数据耗时约130秒
df.head(1000).to_sql(
                    name = 'databaseTable'
                    , con = dbEngine
                    , schema = 'schema'
                    , method = None
                    , index = False
                    , chunksize = 500
                    , dtype = {
                        'userid' : sqlalchemy.types.NVARCHAR(10)
                    }, if_exists='replace')

环境信息 #

• Anaconda 2.6.3
• Python: 3.10.14
• pyodbc: 5.1.0
• pandas: 2.2.3
• sqlalchemy: 2.0.34

原因分析 #

1. fast_executemany未真正生效：SQLAlchemy 2.x版本中，autocommit=True的连接参数会干扰批量写入优化逻辑，导致fast_executemany配置失效；同时if_exists='replace'的表重建操作虽有额外开销，但核心问题仍在于批量写入机制未启动。
2. 网络或Synapse资源瓶颈：本地到Azure Synapse的公网高延迟会放大小批量写入的耗时；若Synapse的DWU（数据仓库单位）配置过低，也会限制写入吞吐量。
3. 数据类型转换开销：DataFrame列类型与Synapse表类型不匹配，尤其是字符串类型的列，在未启用批量优化时，逐行类型转换会大幅拖慢速度。
4. chunksize设置不合理：当fast_executemany失效时，chunksize=500会让写入操作拆分为多次低效的普通executemany执行，进一步降低速度。

解决办法 #

1. 确保fast_executemany正确生效
   • 调整连接配置，移除autocommit的直接设置，改用SQLAlchemy的事务隔离级别控制：

     conurl = URL.create("mssql+pyodbc", query={"odbc_connect": connstring})
     dbEngine = sqlalchemy.create_engine(conurl, fast_executemany=True, isolation_level="AUTOCOMMIT")
     

   • 开启SQLAlchemy日志验证：查看执行语句是否为批量参数化插入，而非逐行生成INSERT语句。
2. 使用原生批量写入或Synapse专属方案
   • 直接用pyodbc原生fast_executemany执行写入：

     cursor = conn.cursor()
     cursor.fast_executemany = True
     sql = "INSERT INTO schema.databaseTable (userid, col1) VALUES (?, ?)"
     params = df.head(1000).values.tolist()
     cursor.executemany(sql, params)
     conn.commit()
     

   • 超大数据量推荐使用COPY INTO：将DataFrame保存为Parquet/CSV上传至Azure Blob Storage，再通过Synapse执行COPY INTO命令从Blob导入数据，这是Synapse官方最优的批量写入方案。
3. 优化网络与资源配置
   • 检查本地到Azure的网络延迟，必要时使用Azure虚拟网络 peering 或专用链接减少公网传输损耗。
   • 临时提升Synapse的DWU配置，完成写入后再调回，避免资源不足限制写入速度。
4. 匹配数据类型与调整参数
   • 确保DataFrame列类型与Synapse表类型完全一致，例如纯数字字符串列可改用VARCHAR而非NVARCHAR，减少转换开销。
   • 增大chunksize至10000以上，减少批量写入的次数，提升整体效率。

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