「n件目からm件だけを取り出したい」場合のSQLクエリの書き方

Webアプリケーションで何らかのデータの一覧を表示する際に、ページング処理(Pagenation)を行うのはほとんど当たり前の事になっている。

EntityFrameworkでLINQを使って「100件スキップしてから20件だけ取得する」というクエリーを書きたい場合は、例えば下の様な書き方になると思う。

var list = db.Books
.OrderBy(itm => itm.id)
.Skip(100)
.Take(20);

これを実行すると実際にはどのようなSQLに変換されているのかを調べてみたい。

＊ただし、以下の内容はMicrosoft SQLサーバーやOracleなど、分析関数が使用可能なデータベースサーバーを使っている場合に限られた話になると思う。


Microsoft SQLサーバーを使っている場合は、クライアントとデータベースの間でやりとりされているデータの内容を確認するには、「SQL Server Profiler」を使うのが便利だ。

「SQL Server Profiler」でキャプチャを開始する前の設定画面

キャプチャされたSQLの内容

実際にデータベースに送られたSQL文を少し読み易く書き直すと、下の様な感じになる。

SELECT TOP 20 *
FROM (
SELECT *, ROW_NUMBER() OVER (ORDER BY [bs_id] ASC) AS [row_number]
FROM [dbo].[Bookshelves]
) AS e
WHERE e.[row_number] > 100
ORDER BY e.[bs_id] ASC

なるほど、ROW_NUMBER() という関数を使って一旦全レコードに行番号を付けて、そこから100行目以降の20件だけを取り出すというロジックになっているのか。。。

多分、この様にサブクエリを使って書くとSQLサーバーの内部で最適化が行われて、実際には全件アクセスはせずに最小のコストで必要な部分だけを返す事が出来るのではないだろうか。

この書き方は自分でSQLを書くときにも使えそうだ。一つのひな型として覚えておこうと思う。








.