SQLAlchemyで生SQLを発行して大量データの取得を高速化してみた

この記事は公開されてから半年以上経過しています。情報が古い可能性がありますので、ご注意ください。

はじめに

SQLAlchemyを使用した既存システムのエンハンスを担当したときに、大量データを取り扱うシーンに直面しました。
数十万件程度のデータを取得するときに処理時間が長くなり、性能の劣化を懸念していましたが、改善に向けて試行錯誤した内容をブログにまとめました。

なお、タイトルの通りSQLAlchemyを使用していますが、結局は生SQLを発行しています。
処理速度を早くする選択肢の一つとしてご参考になればと思っています。

目次

実行環境

  • requirements.txt
  • DB
    下図のような構成で、employeeテーブルに例として100万件データが存在する状態です。

  • Database

  • Model

通常の取得方法

下記コードのようにsession.queryでデータを取得します。

処理時間:0:00:33.686847(約33秒)

ちなみに検索条件を指定する場合は下記のようにfilterを使用します。

取得結果は下記のように参照します。
ORMですのでモデルのプロパティを指定することで値を取得できます。
ループ部分は内包表記で書くこともできます。

Core機能を使用した取得方法

下記コードのようにselect関数を使用してデータを取得します。
SELECT ステートメントの使用
Core機能を使用しているのでORM機能は利用できません。
そのため、取得結果はタプルのリストとなり、モデルのプロパティで取得することはできなくなります。

処理時間:0:00:22.771209(約22秒)
少し早くなりました!
もしかしたら取得結果とオブジェクトとのマッピングが膨大な件数のため、オーバーヘッドになっていたかもしれませんね。

検索条件を指定する場合は下記のようにwhereを使用します。

取得結果は下記のように列番号を指定して参照します。
こちらもループ部分は内包表記で書くことができます。

生SQLを実行する取得方法

SQLAlchemyでは記述したコードからクエリを自動で作成してくれる機能があります。
Core機能を使用した取得方法ではオブジェクトのマッピングを行わないことで処理速度が速くなりました。
ならば、コード上で生のSQLを指定すればクエリ自動作成の処理時間分早くなるのではないか…?ということで検証してみました。

処理時間:0:00:18.334187(約18秒)
わずかに早くなりました!
直面した問題に対してはこの方法で対応することにしました。

検索条件を指定する場合は下記のようにbindparamを使用します。

取得結果はCore機能と同様にタプルのリストとなりますので、列番号を指定して参照します。
こちらもループ部分は内包表記で書くことができます。

まとめ

実施内容と結果は下記の通りです。

実施内容 処理時間
通常の取得方法 約33秒
Core機能を使用した取得方法 約22秒
生SQLを実行する取得方法 約18秒

生のSQLを実行することで処理時間が早くなりました。
SQLAlchemyを使用しておきながらこのような使い方は申し訳なさがありますが…ライブラリや言語を変更できない状況においては選択肢の一つになるかなと思いました。

投稿者プロフィール

nagura
2021年4月からスカイアーチに中途入社しました。
AWSともっと仲良くなるべく日々勉強中です。