大量の結果セットをさらに効率的にページングする方法

はじめに

　クエリ結果を効率的にページングするという問題に対しては、驚くほどのサイクルとハードウェアとブレインウェアが投入されています。先日掲載されたScott Mitchellの記事『SQL Server 2000で大量の結果セットを効率的にページングする方法』では、テーブルからデータの特定の「ページ」を返すストアドプロシージャを考察していました。Scottの方法を調べてみたところ、まだ改善の余地があることが分かりました（本稿はScottの例を基にしているので、Scottの記事を読んでいない方は、そちらを先にお読みください）。

　Scottの方法ではテーブル変数を使って、行カウンタとして機能する補助的なIDを生成していました。つまり、ページが要求されるたびに、ページング対象のテーブル内の全データを読み取ってテーブル変数に挿入し、そこで補助的なIDを生成したうえで、目的の範囲内のIDを持つレコードだけをSELECTステートメントで返すという方法でした。Scottの方法は、単純にすべてのレコードを返すよりは速いのですが、ROWCOUNTを使用すれば、読み取ってテーブル変数に挿入するレコードの数を大幅に減らすことができるので、パフォーマンスをさらに改善することができます。

　本稿ではScottの方法を改善する2つの方法を説明します。最初の方法では、Scottと同様にテーブル変数を使用しますが、読み取ってテーブル変数に挿入するレコードの数を減らすためにSET ROWCOUNTコマンドを使用します。2番目の方法では、SET ROWCOUNTをもっとうまく使っています。従って、最初の方法よりもずっと効率的になっています。

ROWCOUNTでページングを最適化する

　ページングを最適化するための最初のステップは、テーブル変数にレコードを挿入する前にSET ROWCOUNTを使用することです。SETオプションは現在のセッションの特定の動作の処理を変更します。SET ROWCOUNTは、SQL Serverに対して、指定の行数を処理し終わったらクエリ結果の処理を停止するよう指示します。SET ROWCOUNTの詳細については、『Retrieving the First N Records from a SQL Query』を参照してください。

　このストアドプロシージャの例は、4Guysの読者であるDave Griffithsが作成したものです。

CREATE PROCEDURE usp_PagedResults_New_Rowcount
(
   @startRowIndex int,
   @maximumRows int
)
AS

DECLARE @TempItems TABLE
(
   ID int IDENTITY,
   EmployeeID int
)

DECLARE @maxRow int

-- A check can be added to make sure @startRowIndex isn’t > count(1)
-- from employees before doing any actual work unless it is guaranteed
-- the caller won’t do that

SET @maxRow = (@startRowIndex + @maximumRows) - 1

SET ROWCOUNT @maxRow

INSERT INTO @TempItems (EmployeeID)
SELECT EmployeeID
FROM Employees
ORDER BY EmployeeID

SET ROWCOUNT @maximumRows

SELECT e.*, d.[Name] as DepartmentName
FROM @TempItems t
   INNER JOIN Employees e ON
       e.EmployeeID = t.EmployeeID
   INNER JOIN Departments d ON
       d.DepartmentID = e.DepartmentID
WHERE ID >= @startRowIndex

SET ROWCOUNT 0

GO

　ご覧のように、このストアドプロシージャでは、最初にSET ROWCOUNTを使用することで、現在のページの最終行になることが既に分かっているレコードまでをテーブル変数に挿入します。SQL Serverは、設定された行数を処理するとテーブルへの挿入を停止するため、テーブル変数に入れるデータ量を最小限に抑えることができます。この方法は、最初の数ページでは大きな効果を発揮し、かなり先のページまで進まない限りSQL Serverのリソースに影響を与えません。この方法で忘れてはならないのは、最後にSET ROWCOUNT 0をすることです。呼び出し元が同じ接続で何か別の処理をする場合に、ここで設定した行数より多くの行を返すことが必要になるかもしれないので、SET ROWCOUNT 0によって行制限を解除し、現在のセッションを既定の動作モードに戻します。

SQL Serverのオプティマイザを利用する

　このシナリオでは、1つの変数に対して潜在的な値リストを代入すると、そのリストの最後の項目の値が割り当てられるというオプティマイザの「癖」を利用しています。例えば次のSQLスクリプトでは、テーブル変数を作成し、そこに100個のレコード（1〜100）を挿入した後で、テーブル全体のval列の値を2種類のソートを使ってローカル変数に代入しています。

DECLARE @tmp TABLE(
   val int
)

DECLARE @i int, @cnt int, @res int
SELECT @i = 0, @cnt = 100
WHILE @i < @cnt
BEGIN
    SELECT @i = @i + 1
    INSERT INTO @tmp VALUES(@i)

end

SELECT @res = val FROM @tmp ORDER BY val ASC
SELECT @res [Value], ’ASC Sort’ [Sort]

SELECT @res = val FROM @tmp ORDER BY val DESC
SELECT @res [Value], ’DESC Sort’ [Sort]

　これらのSELECTステートメントの結果は次のようになります。

Value    Sort
-----    ----
100      ASC Sort

Value    Sort
-----    ----
1        DESC Sort

　SQL Serverが@tmpテーブルからすべてのレコードを読み取り、各レコードのval列を@resに代入するのではないかと思う人もいるかもしれませんが、このステートメントのクエリプランを見ると、SQL Serverのオプティマイザは、このクエリの遂行に必要な行は1つだけであり、そのレコードだけを読み取ればよいと把握していることが分かります。この操作をSQLプロファイラで調べてみると、オプティマイザがたった6回の読み取りで最終結果を得ていることが分かります（もしテーブル内のレコードを1つずつ読み取っていたら100回以上の読み取りが必要になります）。要するに、このようなクエリが与えられた場合、SQL Serverは実際にはテーブルからすべてのレコードを1つずつ取得してローカル変数に代入するわけではなく、クエリの最後のレコードだけを検索して、その結果を変数に代入するのです。

　では、このオプティマイザの癖を、大量の結果セットをページングするという問題にどう応用したらいいのでしょうか。この知識とSET ROWCOUNTを組み合わせれば、一時テーブルやテーブル変数を使わなくても、大量の結果セットを効率的にページングすることができます。以下に、ScottとDavidのストアドプロシージャをもっと効率的にしたものを示します。

CREATE  PROCEDURE [dbo].[usp_PageResults_NAI]
(
    @startRowIndex int,
    @maximumRows int
)
AS

DECLARE @first_id int, @startRow int
    
-- A check can be added to make sure @startRowIndex isn’t > count(1)
-- from employees before doing any actual work unless it is guaranteed
-- the caller won’t do that

-- Get the first employeeID for our page of records
SET ROWCOUNT @startRowIndex
SELECT @first_id = employeeID FROM employees ORDER BY employeeid

-- Now, set the row count to MaximumRows and get
-- all records >= @first_id
SET ROWCOUNT @maximumRows

SELECT e.*, d.name as DepartmentName
FROM employees e
   INNER JOIN Departments D ON
       e.DepartmentID = d.DepartmentID
WHERE employeeid >= @first_id
ORDER BY e.EmployeeID

SET ROWCOUNT 0

GO

　この例では、オプティマイザの癖とSET ROWCOUNTを利用することで、要求されたページ内の最初のEmployeeIDを、開始位置を表すローカル変数に格納しています。次に、再びSET ROWCOUNTを使用して、ROWCOUNTを@maximumRowsに指定された最大レコード数へと設定します。これにより、はるかに効率的なやり方で結果セットをページングすることが可能になります。また、この方法では、ローカルに作成したテーブルではなく、ベーステーブルに直接関連するテーブルの既存のインデックスを利用しています。

　正確さという点では少々難がありますが、これをScottの方法と比較した結果を次に示します。

　いかがですか。1000ページ目を呼び出すという状況はまず考えられないアプリケーションの場合は、このオプティマイザの癖を利用することがそれほど重要だとは思わないでしょうが、負荷の大きなデータベースを想定しているのであれば、tempdb内のスペースとブロッキングが問題になるでしょう（一時テーブルまたはテーブル変数に書き込みを行うときは常にtempdbを操作することになります）。tempdbの使用を最小限に抑えるためにできることは何でもすべきです。そうすればアプリケーションの速度が向上します。また、入出力を減らすために、1ページ目ではSET ROWCOUNTのみを使用して読み取り数を30%減らすという方法も有効です。

まとめ

　ここで見ていただいたように、SET ROWCOUNTとオプティマイザのちょっとした癖を利用することで、ページングを大きく改善することができます。SET ROWCOUNTはテーブル変数と共に使用できますし、いろいろなソートパラメータを簡単に使用できます。さらに、動的SQLを使用して各種のソートオプションでページの順序付けをすることも可能ですし（具体的な方法は、機会があれば別の記事で紹介します）、オプティマイザを利用するのもよいと思いますが、そうすると非常に複雑になることがあります。

　それでは、ハッピープログラミング！