В процессе реализации механизма поддержки оконных функций в системе SQL Server можно выделить три основные вехи. В версии SQL Server 2005 были реализованы агрегатные оконные функции без фрейма и ранжирующие оконные функции. В версии SQL Server 2012 появились агрегатные оконные функции с фреймом, а также оконные функции смещения и статистические оконные функции. В версии SQL Server 2016 была впервые предусмотрена возможность использования агрегатного оконного оператора пакетного режима, что позволило значительно повысить скорость выполнения большинства оконных функций. Работе с этим оператором и посвящена данная серия статей.
Изначально я планировал заниматься написанием очередной статьи из серии выполнения логических запросов Logical Query Processing, но как раз в это время была выпущена версия SQL Server 2016, и я подумал, что надо бы подготовить серию из трех статей, посвященную замечательному новому средству — агрегатному оконному оператору пакетного режима. И только выполнив эту задачу, я вернусь к работе над серией о Logical Query Processing. Итак, некоторые полагают, что оконные функции полезны только при решении специализированных задач, связанных с анализом данных. На практике же эти функции играют гораздо более заметную и важную роль — они предоставляют нам особую парадигму для решения задач по обработке запросов, альтернативную традиционной схеме на базе предикатов. Иными словами, вместо того чтобы решать задачи по обработке запросов с использованием классических канонов на базе объединений и подзапросов с предикатами в предложениях ON и WHERE, вы можете воспользоваться оконными функциями. Главное преимущество этого способа состоит в том, что оконные функции часто позволяют получать более изящные и эффективные решения, чем те, что дают традиционные средства. Вот уже более десяти лет я рассматриваю такие решения в своих статьях и буду продолжать заниматься этим в дальнейшем. На мой взгляд, нужно только набраться терпения, и мы увидим, как все новые и новые специалисты будут осознавать несомненные достоинства оконных функций, а эти функции будут все чаще включаться в программы пользователей. Ведь с течением времени более эффективные инструментальные средства приходят на смену устаревающим, это естественный закон эволюции.
Стандарт SQL, а также другие платформы баз данных содержат множество мощных оконных функций с богатым потенциалом, и будем надеяться, что в дальнейшем разработчики SQL Server еще реализуют средства для их использования. В качестве примера можно назвать стандартные вложенные оконные функции и блок с фреймом окна RANGE с разделителями на базе INTERVAL, разработанную специалистами Teradata конструкцию RESET WHEN, используемое в продуктах Oracle предложение MATCH_RECOGNIZE и т. д.
Отмечу, кстати, для тех, кто хотел бы иметь возможность запускать опубликованные в данной статье примеры кода: вам потребуется доступ к системам SQL Server 2016 редакций Enterprise, Enterprise Evaluation или Developer.
Обработка в пакетном режиме и специальный прием для ее использования применительно к данным в формате rowstore
Исторически сложилось так, что в системе SQL Server использовалась модель исполнения на базе строк. Это означает, что операторы в плане выполнения запроса обрабатывают данные строка за строкой. Системе SQL Server приходится оценивать сведения о метаданных построчно. Все внутренние функции, вызываемые оператором, применяются построчно. В версии SQL Server 2012 был реализован новый исполнительный механизм, который при использовании совместимых с ним операторов обрабатывает в каждый момент не одну строку, а целый пакет строк. В версиях SQL Server 2014 и 2016 возможности пакетной обработки были заметно расширены. На рисунке 1 представлена схема пакета строк.
.jpg) |
| Рисунок 1. Пакет строк |
Пакет состоит из 900 строк для каждого столбца плюс вектор квалифицирующих строк, показывающий, какие строки с логической точки зрения все еще являются частью данного пакета или были удалены (скажем, при помощи фильтра). Операторы, обеспечивающие функционирование в пакетном режиме, одновременно обрабатывают целый пакет данных. Это означает, что они оценивают метаданные только попакетно и используют пакеты в качестве единицы измерения входящих данных для вызываемых ими функций. Пакетная обработка позволяет значительно сократить число необходимых циклов процессора и использует кэш процессора куда более эффективно. При работе с объектами, содержащими большое количество строк, метод пакетной обработки позволяет повысить производительность обработки запросов на несколько порядков. В процессе анализа планов выполнения запросов вы можете определить, должен ли оператор использовать пакетный режим и был ли этот режим фактически задействован, просмотрев свойства Estimated Execution Mode и Actual Execution Mode данного оператора.
По состоянию на сегодня в различных вариантах SQL Server (в версиях 2012, 2014 и 2016) предусмотрено следующее требование: чтобы пакетная обработка данных стала возможной, необходимо иметь по крайней мере один индекс columnstore в одной из опрашиваемых таблиц. И опять-таки на сегодня индексы columnstore поддерживаются только в редакции Enterprise системы SQL Server. Технология columnstore предусматривает возможность пакетной обработки, поскольку предполагает хранение данных в сжатом формате, ориентированном на столбцы; однако наряду с этим пакетная обработка позволяет значительно повышать скорость обработки запросов в ситуациях, когда данные извлекаются из традиционного формата rowstore. Извлекая отдельные строки из кучи rowstore или двоичного дерева, SQL Server дает возможность конвертировать эти строки в пакеты и затем обрабатывать полученные данные с помощью операторов, позволяющих переходить в пакетный режим.
В версии SQL Server 2016 реализован ряд усовершенствований, которые могут значительно повысить быстродействие выполнения запросов с использованием оконных функций. Я имею в виду, в частности, сверхскоростной пакетный агрегатный оконный оператор, пакетный оператор сортировки и даже средства для обеспечения режима пакетной обработки в последовательных планах.
Если представление данных в формате columnstore в любом случае облегчает обработку почти всех ваших запросов, модернизация системы до уровня 2016 позволит выполнять их намного быстрее. А если работа с запросами облегчается в случае представления данных в формате rowstore и может выполняться с большей эффективностью в режиме пакетной обработки? Скажем, если при обработке запросов производятся упорядоченные вычисления вроде тех, что выполняются оконными функциями? Тогда, извлекая упорядоченные данные из индекса двоичного дерева, мы можем избежать затрат на сортировку, без которой не обойтись в случае использования индекса columnstore. Надо признать, что зачастую запросы оказываются слишком сложными, предусматривающими объединения, а также другие элементы запросов, чтобы можно было предварительно упорядочивать данные с помощью индекса двоичного дерева. Но в ряде случаев они достаточно просты, и такое упорядочение возможно.
Одно из решений состоит в следующем. Мы сохраняем строчное (rowstore), а также столбцовое (columnstore) представление данных, а право определять, какое из них больше подходит для того или иного запроса, оставляем за оптимизатором. В системе SQL Server 2016 можно в качестве основного представления данных использовать обновляемый кластеризованный индекс columnstore и в дополнение к этому иметь некластеризованные индексы двоичного дерева по той же таблице; с другой стороны, вы можете в качестве основного представления данных использовать кластеризованный индекс двоичного дерева хранилища строк rowstore и иметь к тому же обновляемый некластеризованный индекс columnstore по той же таблице. Иными словами, два упомянутых представления данных могут сосуществовать.
Но если вам необходимо иметь только строчное представление данных и вы в то же время хотите воспользоваться преимуществами пакетной обработки, можете предпринять простой обходной маневр…
«Код скрыт в тумблерах. Перевод тумблера в одну позицию снимает блокировку. В другой позиции он открывает одну из этих дверей».
Сераф, «Матрица»
В версии SQL Server 2016 реализованы фильтрованные некластеризованные индексы columnstore. Это означает, что мы можем создать в таблице фиктивный некластеризованный индекс columnstore просто для того, чтобы сделать возможным использование операторов пакетного режима в планах по запросам к данной таблице. Приведу пример. Допустим, у нас имеется таблица T1 с целочисленным столбцом col1. Мы можем создать следующий индекс:
CREATE NONCLUSTERED COLUMNSTORE INDEX idx_cs_dummy ON dbo.T1(col1) WHERE col1 = -1 AND col1 = -2;
SQL Server обнаружит противоречие между выражениями col1 = -1 и col1 = -2 и с помощью оператора Constant Scan создаст в плане индекс, препятствуя фактическому доступу к данным. Таким образом, создание индекса не повлечет за собой никаких затрат и никак не скажется на быстродействии операций записи. Более того, при этом не потребуется вносить изменения в существующие запросы. Когда такая операция будет признана уместной, SQL Server преобразует отдельные строки в пакеты и задействует в планах запросов операторы пакетного режима. В результате запросы начнут выполняться быстрее, что я и продемонстрирую далее.
Я упомянул об этом обходном маневре в разговоре с коллегой Нико Нойгебауэром, имеющим статус Data Platform MVP, который заметил, что пользуется другим приемом для достижения того же эффекта. Он использует левое внешнее объединение с фальшивым условием с пустой таблицей, располагающей индексом columnstore. Предположим, например, что у нас имеется следующий запрос к таблице T1, которая в данный момент использует построчное представление данных:
SELECT col1, col2, col3, SUM(col3) OVER(PARTITION BY col1) AS col1total FROM dbo.T1;
Мы создаем пустую таблицу Dummy с кластеризованным индексом columnstore:
CREATE TABLE dbo.Dummy (dummy INT NOT NULL, INDEX idx_cs CLUSTERED COLUMNSTORE);
Затем мы изменяем запрос следующим образом:
SELECT col1, col2, col3, SUM(col3) OVER(PARTITION BY col1) AS col1total FROM dbo.T1 LEFT OUTER JOIN dbo.Dummy ON 1 = 2;
Процедура выявления противоречий уведомляет систему SQL Server о том, что последней нет необходимости фактически обращаться к таблице Dummy, но наличие таблицы с индексом columnstore позволяет осуществлять пакетную обработку. Основная проблема при использовании этого метода состоит в том, что необходимо внести изменение в запрос.
Между тем достоинства применения фильтрованного индекса сводятся к тому, что его создание незатратно, а применение не снижает быстродействия операций записи и не требует внесения изменений в запросы.
Демонстрационные данные
В качестве демонстрационных данных я использую таблицу Transactions, состоящую из 10 000 000 строк с транзакциями между банковскими счетами. Для создания и заполнения таблицы Transactions используйте код листинга 1.
Столбец actid содержит идентификаторы счетов, столбец tranid — идентификаторы транзакций, а столбец val — суммы транзакций (положительные для депозитов и отрицательные для операций снятия со счетов). В этой таблице используется традиционное построчное представление данных двоичного дерева с помощью кластеризованного индекса с ключевым списком (actid, tranid). Поэтому всякий раз, когда у меня возникнет желание продемонстрировать запросы к данным в формате rowstore в построчном режиме обработки, я буду использовать эту таблицу.
Для демонстрации запросов к данным, представленным в формате columnstore с полной поддержкой пакетного режима, я воспользуюсь таблицей TransactionsCS (где аббревиатура CS означает columnstore) с кластеризованным индексом columnstore. Для создания таблицы TransactionsCS и заполнения ее данными из таблицы Transactions используйте код листинга 2.
Чтобы продемонстрировать запросы к данным в формате rowstore с операторами пакетного режима, задействованными благодаря уловке с фиктивным пустым некластеризованным индексом columnstore, я воспользуюсь третьей таблицей TransactionsDCS (где аббревиатура DCS означает dummy columnstore). Для создания и заполнения этой таблицы используйте код листинга 3.
Эта таблица имеет тот же кластеризованный индекс двоичного дерева, что и таблица Transactions, и в дополнение к нему фиктивный индекс columnstore.
На момент подготовки статьи при получении запроса к таблице Transactions система SQL Server 2016 предусматривает возможность обработки этого запроса только в построчном режиме из-за отсутствия в таблице индекса columnstore. Когда же мы направим запрос к таблице TransactionsCS или к таблице TransactionsDCS, SQL Server рассмотрит возможность обработки запроса в пакетном режиме. Если же, направляя запрос к таблице TransactionsCS или к таблице TransactionsDCS в целях поиска неисправностей, вы хотите, чтобы SQL Server не рассматривал возможность применения пакетной обработки, вы можете использовать флаг трассировки 9453, добавив в конце запроса следующий фрагмент кода: OPTION (QUERYTRACEON 9453).
Агрегатные оконные функции без фрейма
Агрегатные оконные функции без фрейма были реализованы в версии SQL Server 2005. Они позволяют вычислять агрегаты, подобные тем, что вычисляются в групповых запросах, но без сокрытия деталей. Это означает, что мы можем применять вычисления, в которых смешиваются детали и агрегаты, такие как процент от текущего значения в строке суммы по группе.
Для проверки быстродействия агрегатной оконной функции без фрейма применительно к таблице Transactions (обработка в построчном режиме данных в формате rowstore) я воспользуюсь следующим примером (назовем его Query 1).
SELECT actid, tranid, val, SUM(val) OVER(PARTITION BY actid) AS acttotal FROM dbo.Transactions;
Заметьте, что во всех своих тестах производительности я указываю для запроса SSMS параметр Discard results after execution, чтобы система не принимала в расчет время, необходимое для вывода 10 000 000 выходных строк. Для выбора этого параметра правой кнопкой мыши щелкните на пустом пространстве в окне запроса, выберите в контекстном меню пункт Query Options и установите нужный флажок в разделе Results, Grid, как показано на приведенном экране.
.jpg) |
| Экран. Назначение параметра Discard results after execution для запроса SSMS |
Теперь вы получаете как детали относительно каждой транзакции (идентификатор счета, идентификатор транзакции, текущее значение транзакции), так и текущую сумму на счете (которая представляет баланс счета). Как уже отмечалось, система обычно выдает более подробно представленные вычисления, в которых детали перемежаются с агрегатами, такими как отношение между значением в текущей транзакции и средним значением для счета и типа транзакции: 100.0 * val/AVG (val) OVER (PARTITION BY actid, SIGN (val)) — 100.00. Я исхожу из того, что вы уже знакомы с вариантами использования и с вычислениями, необходимыми для достижения этой цели. Поскольку моя задача — рассказать о том, как повысить быстродействие системы SQL Server 2016, я буду оперировать упрощенными, иногда искусственными формами оконных функций, не помещая их в более проработанные вычислительные конструкции.
План выполнения запроса Query 1 представлен на рисунке 2.
.jpg) |
| Рисунок 2. План для запроса Query 1 (построчный режим, применяемый к данным в формате rowstore) |
Верхняя ветвь плана представляет собой упорядоченную проверку кластеризованного индекса. В ходе этой проверки данные сегментируются по значению actid (разделяющий элемент), а строки записываются в каталог на диске (временная таблица). Для каждого сегмента внутренняя часть верхнего цикла активирует среднюю и нижнюю ветви плана. Средняя часть плана предусматривает считывание данных из каталога с целью вычисления общей суммы (агрегата). Нижняя ветвь плана также предусматривает считывание данных из каталога для представления детального уровня данных. Средний оператор Nested Loops объединяет агрегатный и детализированный уровни данных.
Я собрал следующую статистику по выполнению данного запроса на своей системе: продолжительность — 47 секунд, процессор — 57 секунд, операции логического считывания — 20 Мбайт, запись — 40 Кбайт. На мой взгляд, этот план неэффективный. 47 секунд — это довольно много. Неэффективность плана отражается прежде всего в записи и считывании данных в дисковый каталог и из него. Обратите внимание: объем считанных данных составляет 20 Мбайт, а записанных данных — 40 Кбайт. Речь в первую очередь идет об упомянутом каталоге. Но у этого плана есть два достоинства: во-первых, его можно строить на порядке индекса (отпадает необходимость в проведении явной операции сортировки), а во-вторых, это план параллельный. Но имеющиеся достоинства не компенсируют его недостатки. Естественно, если бы наш запрос был более сложным и включал в себя объединения, а также другие элементы и если бы при этом не было возможности создать вспомогательный индекс, нам пришлось бы пойти на дополнительные затраты по осуществлению сортировки средствами обработки в построчном режиме.
Чтобы протестировать быстродействие выполнения того же запроса средствами обработки в пакетном режиме данных в формате columnstore, нужно просто взять в качестве целевой таблицу TransactionsCS, вот таким образом (назовем этот запрос Query 2):
SELECT actid, tranid, val, SUM(val) OVER(PARTITION BY actid) AS acttotal FROM dbo.TransactionsCS;
План данного запроса показан на рисунке 3.
.jpg) |
| Рисунок 3. План для запроса Query 2 (пакетный режим, применяемый к данным в формате columnstore) |
Прежде чем мы углубимся в рассмотрение этого плана, обратите внимание на его восхитительную простоту по сравнению с предыдущим. Здесь мы впервые сталкиваемся с новым оператором пакетного режима Window Aggregate в действии. Этот план параллельный, и он использует средства обработки в пакетном режиме данных в формате columnstore. Он сканирует данные из индекса columnstore; отсюда и небольшое число операций считывания (всего 6 Кбайт). Однако, поскольку данные columnstore не являются упорядоченными, для их упорядочения в соответствии с содержимым разделяющего столбца (actid) приходится использовать оператор Sort. Для смягчения удара разработчики SQL Server 2016 применяют новый оператор пакетного режима Sort.
Почти все магические превращения происходят с появлением нового оператора пакетного режима Window Aggregate. Он заменяет всю старую логику операторами Repartition Streams, Segment, Spool, Aggregate и операторами объединений. Как разъясняют члены группы разработчиков SQL, данный оператор выполняется параллельно (хотя может выполняться и последовательно, что я продемонстрирую ниже) и производит упорядоченные вычисления «в потоковом режиме» в виде процесса, осуществляемого в несколько проходов (для сохранения высокой степени параллелизма). Этот прием известен тем, что в нем используется упорядоченная обработка на графическом процессоре GPU (такая же, как для обработки видеопотока). Одна из важнейших особенностей процесса — отмена необходимости использования старого каталога за счет сохранения в памяти «оконных строк» и выполнения сверхбыстрой обработки в этом окне с применением выделенного пути к коду для каждой функции. Перефразируя слова персонажа из фильма «Матрица», нет никакого каталога! Данный оператор демонстрирует значительно усовершенствованное (по сравнению со старыми вычислениями в построчном режиме) масштабирование параллелизма с использованием DOP n вместо DOP 1.
Далее план предусматривает применение оператора Compute Scalar для возвращения значения NULL из агрегатной функции, когда счетчик строк имеет значение 0, и, наконец, оператора Gather Streams для сбора нескольких потоков в один с последующим возвращением его источнику вызова.
Приведу статистические данные по выполнению этого запроса: продолжительность — 7 секунд, процессор — 13 секунд, логические операции считывания — 6 Кбайт, записи — 0. Время выполнения запроса сократилось с 47 до всего лишь 7 секунд! Значительно уменьшилась и интенсивность использования процессора. Количество операций считывания снизилось, объем данных не 20 Mбайт, а всего лишь 6 Kбайт. Количество операций записи упало до 0, поскольку данный план не предусматривал использования дискового каталога.
Самый дорогостоящий компонент этого плана — операция сортировки. Как уже отмечалось, поскольку в формате columnstore упорядоченность данных не сохраняется, возникает необходимость использования оператора Sort для обеспечения упорядочения данных в соответствии с содержимым разделяющего столбца (actid) с помощью оператора Window Aggregate operator.
Если у вас возникает вопрос, можно ли сочетать достоинства построчного индекса двоичного дерева, отменяющего необходимость явной сортировки в случаях, когда это допускается в связи с простотой запроса, с преимуществами пакетной обработки, могу сказать — да, это возможно. Однако напомню: система SQL Server 2016 исходит из того, что в опрашиваемых таблицах должен наличествовать хотя бы один индекс columnstore. Вспомните, что в число тестовых данных для этой статьи входит таблица TransactionsDCS, аналогичная таблице Transactions (те же данные, тот же построчный кластеризованный индекс двоичного дерева), но дополненная фиктивным пустым фильтрованным некластеризированным индексом columnstore, который позволит оптимизатору рассмотреть возможность использования операторов пакетного режима. Чтобы протестировать производительность выполнения того же запроса в пакетном режиме обработки данных в формате rowstore, используйте в качестве целевой таблицу TransactionsDCS следующим образом (назовем этот запрос Query 3):
SELECT actid, tranid, val, SUM(val) OVER(PARTITION BY actid) AS acttotal FROM dbo.TransactionsDCS;
План выполнения данного запроса показан на рисунке 4.
.jpg) |
| Рисунок 4. План для запроса Query 3 (пакетный режим, применяемый к данным в формате rowstore) |
Если вы пришли к заключению, что предыдущий план прост, то что же вы скажете об этом плане?! Он предусматривает извлечение данных с помощью сканирования в порядке режима построчного индекса, преобразование их в пакеты и выполнение всей оставшейся работы с помощью операторов пакетного режима Window Aggregate и Compute Scalar. Выполнять явные операции сортировки не требуется.
Вот статистика по выполнению данного запроса: продолжительность — 7 секунд, процессор — 7 секунд, логические операции считывания — 31 Кбайт, записи — 0.
Любопытно, что, несмотря на исключение операций явной сортировки, на выполнение этого запроса уходит столько же времени, сколько на выполнение запроса Query 2. Понятно, что в ходе сканирования индекса двоичного дерева потребляется больше ресурсов, чем при сканировании индекса columnstore, что видно из сравнения числа операций считывания данных (31 Kбайт против 6 Kбайт), но в обоих случаях сканирование составляет лишь малую часть времени выполнения запроса. Главный недостаток данного плана — отсутствие паралеллизма. По состоянию на данный момент SQL Server не имеет возможности извлекать упорядоченные данные из индекса двоичного дерева и распределять их постепенно и динамично по рабочим потокам способом, оптимальным для оператора Window Aggregate, без такого посредника, как пакетный оператор Sort. Для того чтобы обеспечить выполнение этих действий без посредника, разработчикам Microsoft нужно будет дополнительно поработать над процессором SQL Server. Будем надеяться, что такая работа будет выполнена в дальнейшем. А пока оптимизатору приходится делать выбор между последовательным планом без привлечения оператора Sort и параллельным планом с использованием этого оператора. Как видите, он склонился к первому варианту, поскольку счел его оптимальным. Чтобы реализовать пример второго варианта в рамках поиска неисправностей, можете форсировать выполнение параллелльного плана с помощью флага трассировки 8649 следующим образом (назовем этот запрос Query 4):
SELECT actid, tranid, val, SUM(val) OVER(PARTITION BY actid) AS acttotal FROM dbo.TransactionsDCS OPTION (QUERYTRACEON 8649);
План выполнения этого запроса показан на рисунке 5.
.jpg) |
| Рисунок 5. План для запроса Query 4 (пакетный режим, применяемый к данным в формате rowstore с форсированным параллелизмом) |
Как вы можете убедиться, этот план предусматривает применение параллельного сканирования индекса двоичного дерева со свойством Ordered: False (порядок индекса значения не имеет) и далее использование оператора Sort пакетного режима.
На рисунке 6 показано время выполнения различных запросов по агрегатным оконным функциям без фрейма.
.jpg) |
| Рисунок 6. Время выполнения агрегатных оконных функций без фрейма |
Что дальше
Реализованный в версии SQL Server 2016 новый оператор пакетного режима Window Aggregate operator значительно повышает скорость выполнения большинства оконных функций. В данный момент возможность применения операторов пакетного режима рассматривается лишь в тех случаях, когда в одной из опрашиваемых таблиц имеется по крайней мере один индекс columnstore. Однако я показал, как это ограничение можно обойти, создав фиктивный пустой фильтрованный индекс columnstore. Правда, пока создавать индексы columnstore могут только пользователи редакции Enterprise. Но будем надеяться, что в дальнейшем специалисты Microsoft обеспечат возможность использовать операторы пакетного режима и без наличия в таблицах индексов columnstore. И тогда мы сможем пользоваться обладающим более высоким быстродействием процессором пакетного выполнения, не прибегая к созданию фиктивного индекса columnstore. А может быть, такая возможность будет предоставлена и пользователям редакции Standard.
В этой статье мы рассмотрели агрегатные оконные функции без фрейма. В следующих статьях серии речь пойдет о ранжирующих и статистических оконных функциях, об агрегатных оконных функциях с фреймом и об оконных функциях смещения.
-- testwindow database SET NOCOUNT ON; IF DB_ID(N'testwindow') IS NULL CREATE DATABASE testwindow; GO USE testwindow; GO -- GetNums helper function DROP FUNCTION IF EXISTS dbo.GetNums; GO CREATE FUNCTION dbo.GetNums(@low AS BIGINT, @high AS BIGINT) RETURNS TABLE AS RETURN WITH L0 AS (SELECT c FROM (SELECT 1 UNION ALL SELECT 1) AS D(c)), L1 AS (SELECT 1 AS c FROM L0 AS A CROSS JOIN L0 AS B), L2 AS (SELECT 1 AS c FROM L1 AS A CROSS JOIN L1 AS B), L3 AS (SELECT 1 AS c FROM L2 AS A CROSS JOIN L2 AS B), L4 AS (SELECT 1 AS c FROM L3 AS A CROSS JOIN L3 AS B), L5 AS (SELECT 1 AS c FROM L4 AS A CROSS JOIN L4 AS B), Nums AS (SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS rownum FROM L5) SELECT TOP(@high - @low + 1) @low + rownum - 1 AS n FROM Nums ORDER BY rownum; GO -- Transactions table with 10M rows (200 accounts x 50000 transactions per act) -- Traditional rowstore B-tree index DROP TABLE IF EXISTS dbo.Transactions; CREATE TABLE dbo.Transactions ( actid INT NOT NULL, tranid INT NOT NULL, val MONEY NOT NULL, CONSTRAINT PK_Transactions PRIMARY KEY(actid, tranid) ); GO DECLARE @num_partitions AS INT = 200, @rows_per_partition AS INT = 50000; INSERT INTO dbo.Transactions WITH (TABLOCK) (actid, tranid, val) SELECT NP.n, RPP.n, (ABS(CHECKSUM(NEWID())%2)*2-1) * (1 + ABS(CHECKSUM(NEWID())%5)) FROM TSQLV3.dbo.GetNums(1, @num_partitions) AS NP CROSS JOIN TSQLV3.dbo.GetNums(1, @rows_per_partition) AS RPP; GO
-- TransactionsCS -- Clustered columnstore index DROP TABLE IF EXISTS dbo.TransactionsCS; SELECT * INTO dbo.TransactionsCS FROM dbo.Transactions; CREATE CLUSTERED COLUMNSTORE INDEX idx_cs ON dbo.TransactionsCS;
-- TransactionsDCS -- Traditional rowstore B-tree index -- + dummy empty filtered nonclustered columnstore index -- to enable using batch mode operators DROP TABLE IF EXISTS dbo.TransactionsDCS; SELECT * INTO dbo.TransactionsDCS FROM dbo.Transactions; ALTER TABLE dbo.TransactionsDCS ADD CONSTRAINT PK_TransactionsDCS PRIMARY KEY(actid, tranid); CREATE NONCLUSTERED COLUMNSTORE INDEX idx_cs_dummy ON dbo.TransactionsDCS(actid) WHERE actid = -1 AND actid = -2;