PythonでSQL実行が遅いときの対応【パフォーマンス改善】

こんにちはゆゆう（@yuyuublog）です。

Pythonで大量データのCSVを読み込み、

SQLを発行しデータベースにUPDATEする機会がありました。

その際に注意しないと処理速度が遅かったので情報の共有です。

Contents

速度比較
修正前のコード
修正後のコード
説明
まとめ

速度比較

CSVファイル 1,000万件のレコードで検証。

改善前：約41時間

改善後：約30分

サンプルソースはGitHubからも取得できます。

https://github.com/yuyuu-dev/example_python_executemany

修正前のコード

#!/user/bin/env python3
#-*- conding: utf-8 -*-
import cx_Oracle
import csv
import sys
import os

print("script start.")

TARGET_HOST = "ホスト名"
PORT = "ポート番号"
SERVICE_NAME = "サービス名"
SCHEME_NAME = "スキーマ名"
USERNAME = "ユーザー名"
PASSWORD = os.environ.get('MY') #環境変数から読み込み

file_name = sys.argv #コマンドライン引数から実行ファイル名を受け取る

tns = cx_Oracle.makedsn(TARGET_HOST, PORT, service_name = SERVICE_NAME)
batch_size = 1000
data = []
c = 0

# DBに接続
with cx_Oracle.connect(USERNAME, PASSWORD, tns) as connect:
  cursor = connect.cursor()

  with open(file_name, 'r') as f:
    reader = csv.reader(f)
    for row in reader:
      c += 1
      cursor.execute('''update SAMPLE_TABLE set COL1 = :arg1 where COL2 = :arg2''', arg1=row[0], arg2=row) #updateの実行

      #1000件更新ごとにコミット
      if c % batch_size == 0:
        print("commit:{0}".format(c), flush=True)
        connect.commit() #コミット
        cursor.close() #コミット確定
        cursor = connect.cursor() #カーソルの再オープン

  connect.commit() #最後にコミット
  cursor.close()

print("script end.")

修正後のコード

#!/user/bin/env python3
#-*- conding: utf-8 -*-
import cx_Oracle
import csv
import sys
import os

print("script start.")

TARGET_HOST = "ホスト名"
PORT = "ポート番号"
SERVICE_NAME = "サービス名"
SCHEME_NAME = "スキーマ名"
USERNAME = "ユーザー名"
PASSWORD = os.environ.get('MY') #環境変数から読み込み

file_name = sys.argv #コマンドライン引数から実行ファイル名を受け取る

tns = cx_Oracle.makedsn(TARGET_HOST, PORT, service_name = SERVICE_NAME)
batch_size = 1000
data = []
c = 0

# DBに接続
with cx_Oracle.connect(USERNAME, PASSWORD, tns) as connect:
  cursor = connect.cursor()
  cursor.setinputsizes(10, 20) # COL1とCOL2の最大桁数

  with open(file_name, 'r') as f:
    reader = csv.reader(f)
    sql = "update SAMPLE_TABLE set COL1 = :arg1 where COL2 = :arg2"
    for row in reader:
      c += 1
      data.append((row[0], row)) #CSVの内容をdataリストに追加

      if c % batch_size == 0: #dataの長さが1000で割り切れるようになった場合
        cursor.executemany(sql, data) #updateの実行
        data = [] #dataリストを初期化

        print("commit:{0}".format(c), flush=True)
        connect.commit() #コミット
        cursor.close() #コミット確定
        cursor = connect.cursor() #カーソルの再オープン
        cursor.setinputsizes(10, 20) # COL1とCOL2の最大桁数

    if data: #dataリストが残っている場合（1000で割り切れなかった分）
      cursor.executemany(sql, data) #updateの実行

  connect.commit() #最後にコミット
  cursor.close()

print("script end.")