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