python - Pandas to_sql 参数和性能

Question

我目前正在尝试稍微调整一些脚本的性能，似乎瓶颈始终是使用 pandas to_sql 函数实际插入数据库(=MSSQL)。

造成这种情况的一个因素是 mssql 的参数限制为 2100。

我与 sqlalchemy 建立连接(具有 mssql + pyodbc 风格):

engine = sqlalchemy.create_engine("mssql+pyodbc:///?odbc_connect=%s" % params, fast_executemany=True)

插入时我使用 block 大小(因此我保持在参数限制和方法=“multi”以下):

dataframe_audit.to_sql(name="Audit", con=connection, if_exists='append', method="multi",
                               chunksize=50, index=False)

这会导致以下性能(不幸的是极其不一致):

我不知道该怎么看待这个问题:

• 不一致似乎源于数据库服务器本身
• 更大的 block 大小似乎并不能转化为更好的性能(似乎恰恰相反!？)
• 也许我应该从 pyodbc 切换到turbodbc(根据一些帖子，它会产生更好的性能)

有什么想法可以让我的 DataFrame 获得更好的插入性能吗？

Answer 1

如果您使用最新版本的 pyodbc 以及 ODBC Driver 17 for SQL Server 和 fast_executemany=True在你的 SQLAlchemy create_engine 中打电话那么你应该使用 method=None (默认)在您的 to_sql 中称呼。这将允许 pyodbc 使用 ODBC 参数数组并在该设置下为您提供最佳性能。您不会达到 SQL Server 存储过程 2100 个参数的限制(除非您的 DataFrame 有大约 2100 个列)。您将面临的唯一限制是，如果您的 Python 进程在将参数数组发送到 SQL Server 之前没有足够的内存来构建整个参数数组。

method='multi' to_sql 的选项仅在使用不支持参数数组的 ODBC 驱动程序(例如 FreeTDS ODBC)时适用于 pyodbc。在这种情况下fast_executemany=True不会有帮助，实际上可能会导致错误。