SQL Serverでのパフォーマンスについて

Question

知りたいこと

処理に大幅な時間がかかるSQLをチューニングしたのですが、処理速度が改善した理由を知りたいです。

処理内容

exeで作成したSQLをサーバーに投げて結果をexeで処理しています。
SQLの結果が数件～数百万件になる可能性があります。
結果の件数が少ない時は問題ないのですが、数百万件返ってくるときはデータ量が多すぎて処理落ちしてしまいます。

そのため、row_numberを使い結果を範囲指定して百万件ずつに分けて取得するようにしました。
SQLのWHERE句で以下の記述をしています。
「WHERE rownum BETWEEN 1 AND 1000000」
このSQLを実行すると結果が返ってくるまでに7分程度かかります。
百万件で取得しきれなかった場合はexeで投げるSQLを以下に変更して実行しています。
「WHERE rownum BETWEEN 1000001 AND 2000000」

実行時間短縮のため、TOPを使うことで処理時間を大幅に減らせました。
SELECT句に
「TOP 1000000」
WHERE句に下記を記述しました。
「WHERE rownum >= 1」
このSQLを実行すると1秒で結果が返ってきます。
2回目以降のSQLではWHERE句を以下のように変更しています。
「WHERE rownum >= 1000001」

試したこと

その他、以下のSQLを試してみました。
「WHERE rownum BETWEEN 1 AND 100」・・・処理時間50秒程度
「WHERE rownum 1 >= AND rownum <= 1000000」・・・処理時間7分程度
「WHERE rownum <= 1000000」・・・処理時間7分程度

この結果を見ると、範囲指定をすると処理に大幅に時間がかかるような気がしているのですが、ネットで検索してみてもそのような事例は見つからなかったです。
順位付けをしたカラムを範囲指定すると遅くなるようなことはあり得るのでしょうか？

Answer 1

範囲指定をすると処理に大幅に時間がかかるような気がしている

その理由は、

row_numberを使い結果を範囲指定して百万件ずつに分けて取得するようにしました。

row_number()による連番を算出する時間に比例します。
番号が大きくなればなるほど、番号算出の為に読み込む件数が増えるので当然です。
どういった部分に時間が掛かっているかなどは、実行計画などで確認が出来ます。

結果の件数が少ない時は問題ないのですが、数百万件返ってくるときはデータ量が多すぎて処理落ちしてしまいます。

単に読み込むだけではなく、更新も行っているであれば、大量のトランザクションが発生するとその分メモリーも大量に消費するので、処理を分割すると、使用するリソースが抑えられ結果的に処理時間が短縮されます。

多量のデータを処理する場合には、分割コミットの検討は必要ですね。

処理するデータ件数を分割する場合、最も効果的なのはインデックス項目を使用する事です。
分割した先頭のキーが分かればそこから、件数を限定するのが最も効率的です。
但し、分割した先頭のキーを求める部分は全件を対象にするので、ここがチューニングのポイントになります。

または、ページングのようにOFFSET FETCHを利用する方法もあります。

件数という事ではなく、例えば部門ごとになどで適切な件数に分割することが出来れば、チューニングは不要になります。

Comments

[YosukeAizawa]

ご丁寧にありがとうございます。
実行計画を確認したら時間がかかっている箇所が分かりました。
内容を精査してみたいと思います。