如何提升SQL Server的BULK INSERT性能？Python CSV导入优化咨询

一、提升现有BULK INSERT的性能 #

你的当前代码里FIELDTERMINATOR设为空值，这是明显的错误——CSV文件必然有字段分隔符（比如逗号、制表符），空分隔符会导致数据库解析逻辑混乱，直接拖慢导入速度，先修正这个参数是基础。除此之外，还可以通过以下手段优化：

• 启用大容量日志模式：如果数据库当前是完整恢复模式，切换到大容量日志模式可以让BULK INSERT采用最小日志记录，大幅降低IO开销：

  ALTER DATABASE YourDatabaseName SET RECOVERY BULK_LOGGED;
  

  导入完成后记得切回原恢复模式（比如完整模式）。

• 添加TABLOCK提示：在WITH子句中加入TABLOCK，让BULK INSERT获取表级锁，减少锁竞争带来的性能损耗：

  sql = "BULK INSERT " + str(tbl_name) + " FROM '" + fl_path + "' WITH (FIRSTROW = 2, FIELDTERMINATOR=',', ROWTERMINATOR = '\r\n', CODEPAGE = '65001', TABLOCK);"
  

• 临时禁用索引和约束：导入前禁用目标表的非聚集索引、外键约束，避免导入过程中频繁更新索引和校验约束；导入完成后再重建索引、启用约束：

  -- 导入前执行
  ALTER INDEX ALL ON " + str(tbl_name) + " DISABLE;
  ALTER TABLE " + str(tbl_name) + " NOCHECK CONSTRAINT ALL;
  
  -- 导入后执行
  ALTER INDEX ALL ON " + str(tbl_name) + " REBUILD;
  ALTER TABLE " + str(tbl_name) + " CHECK CONSTRAINT ALL;
  

• 优化文件存储：确保CSV文件和SQL Server实例在同一本地存储设备（避免用网络共享路径），如果必须用网络存储，使用高带宽的网络环境（如10Gbps以太网）。

二、比BULK INSERT更快的替代方案 #

• 使用bcp命令行工具：这是SQL Server原生的批量导入工具，性能优于BULK INSERT。Python中可以通过subprocess调用：

  import subprocess
  
  bcp_command = [
      "bcp", f"{db_name}.dbo.{tbl_name}", "in", fl_path,
      "-S", "your_server_name", "-U", "username", "-P", "password",
      "-c", "-t,", "-r\n", "-F2", "-C65001"
  ]
  # 执行命令并检查结果
  result = subprocess.run(bcp_command, capture_output=True, text=True)
  if result.returncode != 0:
      print(f"导入失败: {result.stderr}")
  

• 使用pyodbc的fast_executemany：如果需要在Python中灵活处理数据后再导入，开启fast_executemany=True可以让批量插入性能接近原生工具：

  import pyodbc
  import pandas as pd
  
  conn_str = "DRIVER={ODBC Driver 17 for SQL Server};SERVER=your_server;DATABASE=your_db;UID=user;PWD=pwd"
  conn = pyodbc.connect(conn_str)
  cursor = conn.cursor()
  cursor.fast_executemany = True  # 关键优化
  
  # 分块读取CSV并插入
  for chunk in pd.read_csv(fl_path, chunksize=20000, skiprows=1):
      cols = ", ".join(chunk.columns)
      placeholders = ", ".join(["?"] * len(chunk.columns))
      insert_sql = f"INSERT INTO {tbl_name} ({cols}) VALUES ({placeholders})"
      cursor.executemany(insert_sql, chunk.values.tolist())
      conn.commit()
  
  cursor.close()
  conn.close()
  

• SQL Server Integration Services (SSIS)：如果是定期的批量导入任务，SSIS的批量数据流动任务经过高度优化，性能是所有方案中最顶尖的。可以直接配置SSIS包，或者通过Python调用已部署的SSIS包执行导入。

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