SQL-Ex blog

WITH Comments AS 

(

    SELECT

        ROW_NUMBER() OVER

        (

            PARTITION BY

                c.UserId

            ORDER BY 

                c.CreationDate

        ) AS n

    FROM dbo.Comments AS c 

)

SELECT 

    c.*

FROM Comments AS c

WHERE c.n = 0;

Это достаточно простые столбцы для работы с точки зрения чтения и сортировки.

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

Исходная точка

Выборка без дополнительных столбцов:

WITH Comments AS 

(

    SELECT

        ROW_NUMBER() OVER

        (

            PARTITION BY

                c.UserId

            ORDER BY 

                c.CreationDate

        ) AS n

    FROM dbo.Comments AS c

    WHERE c.CreationDate >= '20131201'

)

SELECT 

    c.*

FROM Comments AS c

WHERE c.n = 0;

При втором выполнении запроса после коррекции с обратной связью выделения памяти мы придем к следующему плану:



Он занимает 3 миллисекунды на сканирование индекса поколоночного хранения, и мы получаем 24Мб выделенной памяти. Это хорошо. Мне это нравится.

Штопать строки

Наш второй запрос выглядит так. Мы выбираем все столбцы из таблицы Comments.

WITH Comments AS 

(

    SELECT

        c.Id, 

        c.CreationDate, 

        c.PostId, 

        c.Score, 

        c.Text, 

        c.UserId,

        ROW_NUMBER() OVER

        (

            PARTITION BY

                c.UserId

            ORDER BY 

                c.CreationDate

        ) AS n

    FROM dbo.Comments AS c

    WHERE c.CreationDate >= '20131201'

)

SELECT 

    c.*

FROM Comments AS c

WHERE c.n = 0;

Первый запуск запроса прежде, чем обратная связь по выделению памяти исправит ситуацию, требует 16Гб выделенной памяти. Без наличия этого механизма мы будем продолжать запрашивать то же непродуктивное выделение. Если вы не имеете пакетного режима и Enterprise Edition, вы будете сталкиваться с этим сценарием постоянно.

Когда происходит коррекция выделения памяти, мы приходим к 456Мб выделенной памяти.

Довольно неплохо, не так ли?



Кроме того, сканирование таблицы с параллельными потоками занимает 125 мс, по сравнению с 3 миллисекундами и одним потоком. Конечно, здесь проблема связана, главным образом, со столбцом Text.

Строки были ошибкой.

Без включения строк

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



Если мы захотим сохранить эти метрики, но все же показывать столбец Text, нам потребуется сделать что-то типа этого:

WITH Comments AS 

(

    SELECT

        c.Id, 

        c.CreationDate, 

        c.PostId, 

        c.Score,  

        c.UserId,

        ROW_NUMBER() OVER

        (

            PARTITION BY

                c.UserId

            ORDER BY 

                c.CreationDate

        ) AS n

    FROM dbo.Comments AS c

    WHERE c.CreationDate >= '20131201'

)

SELECT 

    c.*,

    c2.Text

FROM Comments AS c

JOIN dbo.Comments AS c2

    ON c.Id = c2.Id

WHERE c.n = 0;

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

Заключение

Этот шаблон применим не только к оконным функциям, но проблема производительности, которую мне приходится настраивать, довольно часто связана запросами, используемыми для постраничной разбивки.