共通テーブル式またはロシアの一般化されたテーブル式は、MS SQL Server 2005に登場した技術であり、あるSQLクエリの結果を別のSQLクエリで再利用する形式の1つです。
Web開発では、多くの場合、選択を行い、それをページに分割して、いずれかのページに属するテーブルの行を表示する必要があります。 一般的なアプローチの1つは、サンプル全体を抽出し、必要なページに属する行番号を計算して、必要な行を含む出力htmlを生成することです。 残りのデータは使用されず、無駄に取得されます。
CTEは、このようなデータ抽出の効率を高めることができます。 一番下の行は通常、特定のページを表示するために必要な行を決定するために、キーフィールドとソートを実行するフィールドが必要ですが、必ずしも抽出する必要はありません。 また、ページを生成するには、通常、より多くの列が必要ですが、行の数が少ない必要があります。 これは、特定のページの行を決定するために小さくて高速な非クラスター化インデックスを使用し、単一ページの行を抽出するために、行数の少ないクラスター化インデックスを使用するためです。
最適化の前にフォーラムで使用されたクエリの例を次に示します。
select * from forummessages where TopicID=310 order by messageid
実行中に、7815の論理読み取りが行われました。
そして、これがCTEを使用したリクエストの例です
declare @pagenumber int, @pagesize int
set @pagesize=20
set @pagenumber=10
;with rowpaging
as
(select ROW_NUMBER() over(order by messageid) as rn,messageid from forummessages where TopicID=310)
select * from ForumMessages as m JOIN rowpaging as r ON m.MessageID=r.MessageID
where rn between @pagesize*(@pagenumber-1)+1 and @pagesize*@pagenumber
order by m.messageid
実行中に68の論理読み取りが行われました。
その結果、1ページを表示するためのデータ抽出のパフォーマンスがほぼ115倍に向上し、プログラムコードで目的のページに属する行を計算する必要がなくなりました。
UPD：
Skip（）およびTake（）メソッドを使用して、LINQクエリがどのように機能するかを見てみましょう。
同じ10行を抽出するlinqコードの例を次に示します。
DBM dbm = new DBM();
var items = (from m in dbm.Context.ForumMessages
where m.TopicID == 310
orderby m.MessageID
select m).Skip(200).Take(10);
string r = "";
foreach (var x in items)
{
r += x.Body;
}
SQL Serverで実行されるもの（SQLプロファイルを使用してキャプチャされたもの）
SELECT TOP (10)
[Filter1].[MessageID] AS [MessageID],
[Filter1].[TopicID] AS [TopicID],
[Filter1].[UserID] AS [UserID],
[Filter1].[Body] AS [Body],
[Filter1].[CreationDate] AS [CreationDate],
[Filter1].[Visible] AS [Visible],
[Filter1].[IPAddress] AS [IPAddress],
[Filter1].[Rating] AS [Rating],
[Filter1].[Deleted] AS [Deleted],
[Filter1].[WhoDelete] AS [WhoDelete]
FROM ( SELECT [Extent1].[MessageID] AS [MessageID], [Extent1].[TopicID] AS [TopicID], [Extent1].[UserID] AS [UserID], [Extent1].[Body] AS [Body], [Extent1].[CreationDate] AS [CreationDate], [Extent1].[Visible] AS [Visible], [Extent1].[IPAddress] AS [IPAddress], [Extent1].[Rating] AS [Rating], [Extent1].[Deleted] AS [Deleted], [Extent1].[WhoDelete] AS [WhoDelete], row_number() OVER (ORDER BY [Extent1].[MessageID] ASC) AS [row_number]
FROM [dbo].[ForumMessages] AS [Extent1]
WHERE 310 = [Extent1].[TopicID]
) AS [Filter1]
WHERE [Filter1].[row_number] > 200
ORDER BY [Filter1].[MessageID] ASC
このクエリの結果、4889の論理読み取り値が得られます。これは、CTEを使用した場合の約72倍であり、ページに分割されていないサンプルのすべての行を取得する場合の1.5倍です。