Boost」など、さまざまなカンファレンスを企画・運営しています。BOOKS(コードジン・ブックス)は、CodeZineの連載からカットアップした、開発現場の課題解決に役立つ書籍シリーズです。ROWCOUNTとオプティマイザのちょっとした癖を利用することで、ページングを大きく改善する方法を紹介します。一時テーブルやテーブル変数を使わなくても、大量の結果セットを効率的にページングできるため、データベースの負荷軽減にも役立ちます。ポストはじめにクエリ結果を効率的にページングするという問題に対しては、驚くほどのサイクルとハードウェアとブレインウェアが投入されています。2000で大量の結果セットを効率的にページングする方法』では、テーブルからデータの特定の「ページ」を返すストアドプロシージャを考察していました。Scottの方法を調べてみたところ、まだ改善の余地があることが分かりました(本稿はScottの例を基にしているので、Scottの記事を読んでいない方は、そちらを先にお読みください)。Scottの方法ではテーブル変数を使って、行カウンタとして機能する補助的なIDを生成していました。ページが要求されるたびに、ページング対象のテーブル内の全データを読み取ってテーブル変数に挿入し、そこで補助的なIDを生成したうえで、目的の範囲内のIDを持つレコードだけをSELECTステートメントで返すという方法でした。Scottの方法は、単純にすべてのレコードを返すよりは速いのですが、ROWCOUNTを使用すれば、読み取ってテーブル変数に挿入するレコードの数を大幅に減らすことができるので、パフォーマンスをさらに改善することができます。本稿ではScottの方法を改善する2つの方法を説明します。ROWCOUNTコマンドを使用します。ROWCOUNTをもっとうまく使っています。従って、最初の方法よりもずっと効率的になっています。ROWCOUNTを使用することです。SETオプションは現在のセッションの特定の動作の処理を変更します。Serverに対して、指定の行数を処理し終わったらクエリ結果の処理を停止するよう指示します。Query』を参照してください。Griffithsが作成したものです。ROWCOUNTを使用することで、現在のページの最終行になることが既に分かっているレコードまでをテーブル変数に挿入します。Serverは、設定された行数を処理するとテーブルへの挿入を停止するため、テーブル変数に入れるデータ量を最小限に抑えることができます。Serverのリソースに影響を与えません。0によって行制限を解除し、現在のセッションを既定の動作モードに戻します。Serverのオプティマイザを利用するこのシナリオでは、1つの変数に対して潜在的な値リストを代入すると、そのリストの最後の項目の値が割り当てられるというオプティマイザの「癖」を利用しています。次のSQLスクリプトでは、テーブル変数を作成し、そこに100個のレコード(1~100)を挿入した後で、テーブル全体のval列の値を2種類のソートを使ってローカル変数に代入しています。[Sort]これらのSELECTステートメントの結果は次のようになります。Serverのオプティマイザは、このクエリの遂行に必要な行は1つだけであり、そのレコードだけを読み取ればよいと把握していることが分かります。この操作をSQLプロファイラで調べてみると、オプティマイザがたった6回の読み取りで最終結果を得ていることが分かります(もしテーブル内のレコードを1つずつ読み取っていたら100回以上の読み取りが必要になります)。Serverは実際にはテーブルからすべてのレコードを1つずつ取得してローカル変数に代入するわけではなく、クエリの最後のレコードだけを検索して、その結果を変数に代入するのです。このオプティマイザの癖を、大量の結果セットをページングするという問題にどう応用したらいいのでしょうか。ROWCOUNTを組み合わせれば、一時テーブルやテーブル変数を使わなくても、大量の結果セットを効率的にページングすることができます。以下に、ScottとDavidのストアドプロシージャをもっと効率的にしたものを示します。ROWCOUNTを利用することで、要求されたページ内の最初のEmployeeIDを、開始位置を表すローカル変数に格納しています。ROWCOUNTを使用して、ROWCOUNTを@maximumRowsに指定された最大レコード数へと設定します。これにより、はるかに効率的なやり方で結果セットをページングすることが可能になります。この方法では、ローカルに作成したテーブルではなく、ベーステーブルに直接関連するテーブルの既存のインデックスを利用しています。正確さという点では少々難がありますが、これをScottの方法と比較した結果を次に示します。1000ページ目を呼び出すという状況はまず考えられないアプリケーションの場合は、このオプティマイザの癖を利用することがそれほど重要だとは思わないでしょうが、負荷の大きなデータベースを想定しているのであれば、tempdb内のスペースとブロッキングが問題になるでしょう(一時テーブルまたはテーブル変数に書き込みを行うときは常にtempdbを操作することになります)。tempdbの使用を最小限に抑えるためにできることは何でもすべきです。すればアプリケーションの速度が向上します。ROWCOUNTのみを使用して読み取り数を30%減らすという方法も有効です。ROWCOUNTとオプティマイザのちょっとした癖を利用することで、ページングを大きく改善することができます。ROWCOUNTはテーブル変数と共に使用できますし、いろいろなソートパラメータを簡単に使用できます。動的SQLを使用して各種のソートオプションでページの順序付けをすることも可能ですし(具体的な方法は、機会があれば別の記事で紹介します)、オプティマイザを利用するのもよいと思いますが、そうすると非常に複雑になることがあります。1999年9月にオープンした、日本初のネットビジネス専門ニュースサイト。最新記事を日本語に翻訳して掲載するとともに、日本独自のネットビジネス関連記事やレポートを配信。NEW1DMM.comの施策から見る、事業をむしばむ「技術負債」への処方箋──リファクタリングの「言語化」でインシデントを予防2企業の7割超が「2025年の崖」を理解していないことが明らかに。NEW1DMM.comの施策から見る、事業をむしばむ「技術負債」への処方箋──リファクタリングの「言語化」でインシデントを予防2企業の7割超が「2025年の崖」を理解していないことが明らかに。NEW1DMM.comの施策から見る、事業をむしばむ「技術負債」への処方箋──リファクタリングの「言語化」でインシデントを予防2企業の7割超が「2025年の崖」を理解していないことが明らかに。NEW新着記事一覧を見るイベント今後の予定を見るCodeZineは、株式会社翔泳社が運営するソフトウェア開発者向けのWebメディアです。「デベロッパーの成長と課題解決に貢献するメディア」をコンセプトに、現場で役立つ最新情報を日々お届けします。イラストの無断転載を禁じます。記載されているロゴ、システム名、製品名は各社及び商標権者の登録商標あるいは商標です。