如何提升Python向Azure SQL插入DataFrame的速度并解决连接错误？

问题 #

将最终DataFrame导入Azure SQL分为两步：1. 从Azure下载数据集并使用Python转换；2. 将转换后的数据上传至Azure再插入SQL。下载、转换及上传耗时仅5分钟，但SQL插入操作耗时极长。

用户使用的插入代码如下：

server = 'XXXX.database.windows.net' 
database = 'XXX' 
username = 'XXX' 
password = 'XXXX' 
driver= '{ODBC Driver 17 for SQL Server}' 
cnxn = pyodbc.connect('DRIVER={ODBC Driver 17 for SQL Server};SERVER='+server+';DATABASE='+database+';UID='+username+';PWD='+ password)

params = urllib.parse.quote_plus('DRIVER='+driver+
                      ';SERVER='+server+
                      ';PORT=1433;DATABASE='+database+
                      ';UID='+username+
                      ';PWD='+ password)
    
engine = sa.create_engine("mssql+pyodbc:///?odbc_connect={}".format(params),fast_executemany=True)
conn = engine.connect()

with engine.connect() as connection:
    try:  
         df_copy.to_sql('XXXX',connection,if_exists = 'append',index=False,chunksize=500)
    except SQLAlchemyError as e:
     error = str(e.__dict__['orig'])
     print(error)
    
conn.close()

最终DataFrame包含97000行、127列，Azure SQL配置为10 DTUs、250GB存储。插入时出现以下错误：

Exception has occurred: OperationalError
(pyodbc.OperationalError) ('08S01', '[08S01] [Microsoft][ODBC Driver 17 for SQL Server]TCP Provider: An existing connection was forcibly closed by the remote host.\r\n (10054) (SQLExecute); [08S01] [Microsoft][ODBC Driver 17 for SQL Server]Communication link failure (10054)')

即使在create_engine中设置connect_args={'connect_timeout': 2400}，仍会在40-50分钟后触发相同错误。用户认为97000条记录耗时50分钟过长，询问是否有优化方法，以及在Jenkins上部署测试是否能提升性能。

优化方案 #

1. 调整批量插入参数 #

已开启的fast_executemany=True是正确的优化方向，但当前chunksize=500偏小，建议调整为1000-2000（因列数较多，避免单批次数据量过大），减少批次提交的网络交互次数，降低耗时。

2. 临时禁用索引与约束 #

大量数据插入时，目标表的非聚集索引、外键约束会大幅增加写入开销。可以在插入前临时禁用，完成后再恢复：

-- 插入前执行：禁用索引和约束
ALTER INDEX ALL ON [XXXX] DISABLE;
ALTER TABLE [XXXX] NOCHECK CONSTRAINT ALL;

-- 插入完成后执行：重建索引和启用约束
ALTER INDEX ALL ON [XXXX] REBUILD;
ALTER TABLE [XXXX] CHECK CONSTRAINT ALL;

3. 临时提升Azure SQL资源配置 #

当前10 DTUs的配置对于127列、近10万行的批量插入来说资源不足，DTU瓶颈会导致写入缓慢甚至连接超时。可以临时升级到更高DTU层级（如50 DTU），插入完成后再降级，平衡性能与成本。

4. 改用Blob存储+SQL批量导入 #

这是Azure SQL批量插入的最优方案，比Python逐批插入效率高得多：

• 将DataFrame导出为CSV/Parquet文件，上传到Azure Blob存储
• 在SQL中执行BULK INSERT直接读取Blob数据：

BULK INSERT [XXXX]
FROM 'https://your-blob-account.blob.core.windows.net/your-container/data.csv'
WITH (
    FORMAT = 'CSV',
    FIRSTROW = 2, -- 跳过表头
    FIELDTERMINATOR = ',',
    ROWTERMINATOR = '\n',
    MAXERRORS = 0
);

5. 清理冗余代码 #

当前代码中cnxn = pyodbc.connect(...)创建的连接未被使用，属于冗余代码，建议删除，避免不必要的资源占用。

Jenkins部署是否能提升性能？ #

Jenkins本身不会直接提升插入性能，核心瓶颈是Azure SQL的资源配置、插入方式的效率，而非代码执行环境。但如果Jenkins部署在Azure虚拟机（与Azure SQL同区域），或服务器网络带宽优于本地，可能会小幅降低网络延迟，但这种提升远不如前面的优化方案显著，优先解决SQL端和插入方式的问题才是关键。

内容的提问来源于stack exchange，提问作者aredla revanth reddy