Python将DataFrame导入非本地SQL Server速度过慢的优化问询

原代码性能问题根因 #

• 虽启用了fast_executemany参数，但每次调用executemany仅传入单条数据，本质仍是逐行插入，完全没有用到批量执行的性能优势
• 每次循环都执行commit()操作，在非本地部署的数据库场景下，会产生大量额外的网络交互开销，延迟被显著放大

可行优化方案 #

方案1：批量VALUES拼接插入 #

该方案将多行数据拼接为单条INSERT语句执行，大幅减少网络交互次数，27000行数据通常可在数秒内完成写入：

records = [str(tuple(x)) for x in predict.values]

insert_ = """
INSERT INTO ml.predictions(account_no, group_company, customer_type, invoice_date, lower, upper) VALUES
"""

def chunker(seq, size):
    return (seq[pos:pos + size] for pos in range(0, len(seq), size))

for batch in chunker(records, 1000):
    rows = ','.join(batch)
    insert_rows = insert_ + rows
    cursor.execute(insert_rows)
    bachelor.commit()

注意：该方案需要确保数据中没有包含单引号、逗号等可能引发SQL语法错误的特殊字符，生产环境建议增加数据转义逻辑。

方案2：参数化批量插入（更推荐） #

该方案利用executemany的原生批量能力，参数化传输避免SQL注入风险，安全性更高，性能与拼接方案相当：

# 全局只需要设置一次fast_executemany即可，不需要循环内重复设置
cursor.fast_executemany = True
insert_sql = "INSERT INTO ML.predictions (account_no,group_company,customer_type,invoice_date,lower, upper) values(?,?,?,?,?,?)"

# 把DataFrame转为参数列表
params = predict.values.tolist()
batch_size = 1000

# 按批次批量插入
for i in range(0, len(params), batch_size):
    cursor.executemany(insert_sql, params[i:i+batch_size])
    bachelor.commit()

提示：可以根据实际网络带宽、数据库负载调整batch_size参数，通常1000~5000行每批能达到最优性能。

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