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

    Подробнее
    Реклама

    Комментарии 15

      +3
      А зачем извлекать все данные? Можно же сразу определить сколько взять.
      Вот например: ASP.NET MVC: PagedList
      Или в LINQ напрямую использовать Skip & Take
        0
        Добавил ответ на ваш вопрос в текст топика, в UPD.
          0
          Интересно, а как применить CTE в LINQ? или только чистый SQL?
            0
            в linq наверное не получиться использовать CTE, по крайней мере не нашел способа
        +1
        Даже не думал, что кто-то недавно открыл для себя ROW_NUMBER(), а до этого читал все данные и тащил их на клиента.
          +1
          в MSSQL2011 (aka «Denali») появилось ORDER BY OFFSET х ROWS FETCH NEXT у ROWS ONLY (MSDN). Может кому пригодится, когда сервер собственно выпустят в продакшен…
            0
            1. А где «повторное использования результатов одного SQL запроса в другом». Однократное CTE позволяют, давая TE запросу, который следует за ним, но где повторное использование?
            2. Зачастую SET ROWCOUNT оказывается гораздо эффективней.
            3. Почему не использовать ROW_NUMBER без CTE?
            4. Количество чтений совершенно не означает, что именно столько будет загружено на клиент. Своевременно закрытый IDataReader останавливает поток из TDS.
              0
              Не совсем понял, в чем именно SET ROWCOUNT эффективнее.
              Что вы имеете в виду, говоря о том чтобы использовать Row_number без CTE, можете пример запроса привести?
              Совершенно согласен, что количество чтений это не количество данных, которые придут на клиент. на клиент придет то, что извлечет второй запрос при использовании CTE, а количество чтений — это сколько 8к страниц данных SQL сервер прочитает с диска или из кэша, чем больше это число, тем больше ресурсов потребляет SQL Server и тем медленнее работает запрос к БД.
              Вообще, проверить, насколько каждый SQL запрос эффективен, довольно просто: запустите запрос в SSMS, добавив перед ним SET STATISTICS IO ON, а после выполнения посмотрите количество логических чтений. так же можно посмотреть план выполнения.
                0
                Немного промахнулся кнопкой, ответ ниже.
              0
              Тем, что он не занимается расчетом ROW_NUMBER. А учитывая что в большинстве случаев пользователь дальше первых 2-3 страниц не уходит, ROW_NUMBER может стать из пушки по воробьям. Если же вы тестировали ROW_NUMBER внимательно, то должны знать, что на больших объемах данных он работает очень медленно.

              Имею ввиду создание, на самом деле повторно используемых представлений, использование простых вложенных запросов.

              MSSQL не буферизует все данные, которые собирается отдавать. Поэтому если TDS будет закрыт после некоторой порции данных, то чтение остальных осуществляться не будет.

              Если вы запустите запрос в SMSS, то проанализируете процесс чтения в таком виде, в каком его предполагает SSMS. То есть от начала до конца или до момента, когда его принудительно прервут. А я говорю о совершенно другой стратегии извлечения данных.
                0
                лучше, конечно, разобрать конкретный пример и посмотреть как он работает. так и не понял как воспользоваться SET ROWCOUNT. (насколько помню, он возвращает количество извлеченных запросом строк)

                Насколько мне известно SSMS все же выдает фактическую информацию об операциях чтения. Если мы остановим обработку в DataReader, то SQL Server все равно выполнит у себя в памяти и в своих процессах запрос полностью, может быть, использование TOP N позволит ограничить операции чтения.

                Что касается вложенных запросов, то часто эти запросы выполняются многократно, тем самым генерируя излишние операции чтения, CTE в этом плане хорош тем, что запрос CTE (первый в примере) выполняется только один раз, результаты сохраняет в опреативной памяти и затем обрабатывает в следующим запросе.
                  0
                  Да чтож я сегодня все промахиваюсь)) Ответ ниже.
                0
                Возвращает @@ROWOCUNT, а SET ROWCOUNT лимитирует количество записей.

                Что значит выполнить запрос полностью? Если я сделаю select * from table, где table — терабайтная таблица, то он зарезервирует терабайт RAM? На самом деле все несколько сложнее. Клиент может отправить так называемый Attention request, чтобы инициировать остановку выполнения запроса. Правда сделать он это может только после получения TDS-пакета полностью и вполне могут проскочить еще пакеты до получения Attention aknowledgement, так что остановится он инертно (мера инертности обычно пропорционально настройкам сетевых буферов). Но остановится.

                Вложенные запросы и CTE — просто разный языковой синтаксис одного и того же. В плане запроса мы увидим привычные сканы и в большинстве примеров дадут абсолютно одинаковые планы запросов, что в CTE, что во вложенных запросах. Фраза «запросы выполняются многократно» — это вообще что-то из области мистики.
                  0
                  @@ROWCOUNT возвращает количество строк предыдущей операции, использовать его внутри запроса для получения номеров строк не получается.

                  По поводу многократно выполняющихся подзапросов — такое можно увидеть, если просматривать план выполнения запросов. Например, такое бывает при JOINах методом Nested Loop
                    0
                    Третий раз повторяю. SET ROWCOUNT. Не @@ROWCOUNT. Нужно записи с 50 по 100 — сделали SET ROWCOUNT 100, 50 первых записей скипнули. Вообще, это азбука, обсуждается со времен древнего ASP.

                    Вы хотите сказать, что планировщик, увидев CTE магическим образом развернет join+nested loop в один скан? И оптимизатор в случае с вложенным запросом пропустит такую фантастическую возможность? Очень бы хотелось видеть пример.

                Только полноправные пользователи могут оставлять комментарии. Войдите, пожалуйста.

                Самое читаемое