将50万行SQL查询结果写入CSV过慢，求cx_Oracle优化方案

针对你导出50万行、47列数据耗时15分钟的问题，可从减少数据库交互次数和降低文件IO开销两个核心方向优化，以下是具体方案：

关键优化点 #

• 设置游标批量获取大小：cx_Oracle游标默认arraysize很小（通常为100），逐行拉取会产生大量网络请求。将arraysize设置为10000-50000，一次从数据库批量获取多行数据，大幅减少往返次数。
• 批量写入CSV：替换逐行writerow为批量writerows，减少磁盘IO的系统调用次数，提升写入效率。
• 规范文件写入参数：添加newline=''避免CSV写入时产生额外空行，减少不必要的文件体积和IO操作。

修改后的代码 #

import config
from pathlib import WindowsPath
import csv
import cx_Oracle  # 补充原代码遗漏的cx_Oracle导入

# 建立数据库连接
con = cx_Oracle.connect(f'{config.USER_ODS}/{config.PASS_ODS}@{config.HOST_ODS}:{config.PORT_ODS}/{config.SERVICENAME_ODS}')
sql = 'SELECT * FROM ods.v_hsbc_ucmdb_eim'
cur = con.cursor()

# 设置批量获取行数，根据内存情况调整，建议10000-50000
cur.arraysize = 20000

output = WindowsPath('result.csv')

with output.open('w', encoding="utf-8", newline='') as f:
    writer = csv.writer(f, lineterminator="\n")
    cur.execute(sql)
    # 写入列名
    col_names = [row[0] for row in cur.description]
    writer.writerow(col_names)
    # 批量读取并写入数据
    while True:
        rows = cur.fetchmany()
        if not rows:
            break
        writer.writerows(rows)

# 关闭数据库资源
cur.close()
con.close()

额外优化建议 #

• 调整arraysize值：根据服务器内存和网络带宽调整，过大可能占用过多内存，过小则优化效果有限，建议从20000开始测试。
• 开启预取优化：如果数据库版本支持，可在执行SQL时添加prefetchrows参数：cur.execute(sql, prefetchrows=cur.arraysize)，进一步提升数据读取效率。
• **避免SELECT ***：若不需要全部47列数据，明确指定所需列，减少不必要的数据传输。

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