Common Table Expressions и деление таблиц на страницы

Common Table Expressions или по русский обобщенные табличные выражения - это технология, которая появилась в MS SQL Server 2005 и представляет собой одну из форм повторного использования результатов одного SQL запроса в другом.

 В веб разработке довольно часто бывает нужно сделать выборку, разделить ее на страницы и отобразить строки таблицы, которые относятся к одной из страниц. Один из распространенных подходов заключается в том, что извлекается вся выборка, затем вычисляются номера строк, которые относятся к нужной нам странице и генерируется выходной 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 логических чтений.

 В итоге производительность извлечения данных для отображения одной страницы увеличилось почти в 115 раз и при этом не нужно в программном коде вычислять, какие строки относятся к нужной странице.

UPD:
 Давайте рассмотрим, как работает запрос на LINQ с использованием методов Skip() и Take().

 Вот пример кода на linq для извлечения тех же 10 строк:

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 Profile)

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 логических чтений, что почти в 72 раза больше чем в случае использования CTE и в полтора раза меньше чем извлечение всех строк выборки, неразделенной на страницы.

Ссылки по теме

• Скидка 35% на Visual Studio 2010 Professional with MSDN и 15% на Visual Studio 2010 Premium with MSDN
• Microsoft Visual Studio LightSwitch 2011 скидка 33 %