(495) 925-0049, ITShop интернет-магазин 229-0436, Учебный Центр 925-0049
  Главная страница Карта сайта Контакты
Поиск
Вход
Регистрация
Рассылки сайта
 
 
 
 
 

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

Источник: habrahabr

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

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


 Распечатать »
 Правила публикации »
  Написать редактору 
 Рекомендовать » Дата публикации: 09.11.2011 
 

Магазин программного обеспечения   WWW.ITSHOP.RU
Microsoft 365 Business Basic (corporate)
Microsoft Office для дома и учебы 2019 (лицензия ESD)
Microsoft Office 365 Профессиональный Плюс. Подписка на 1 рабочее место на 1 год
Microsoft Office 365 для Дома 32-bit/x64. 5 ПК/Mac + 5 Планшетов + 5 Телефонов. Подписка на 1 год.
Microsoft 365 Business Standard (corporate)
 
Другие предложения...
 
Курсы обучения   WWW.ITSHOP.RU
 
Другие предложения...
 
Магазин сертификационных экзаменов   WWW.ITSHOP.RU
 
Другие предложения...
 
3D Принтеры | 3D Печать   WWW.ITSHOP.RU
 
Другие предложения...
 
Новости по теме
 
Рассылки Subscribe.ru
Информационные технологии: CASE, RAD, ERP, OLAP
Безопасность компьютерных сетей и защита информации
Новости ITShop.ru - ПО, книги, документация, курсы обучения
Программирование на Microsoft Access
CASE-технологии
Новые материалы
Новые программы для Windows
 
Статьи по теме
 
Новинки каталога Download
 
Исходники
 
Документация
 
 



    
rambler's top100 Rambler's Top100