Вопрос:

Как рекурсивное коррелированное выражение ускоряет различные запросы?

sql sql-server performance tsql

560 просмотра

2 ответа

2894 Репутация автора

Я нашел этот пост о ускорении различных запросов:

Сверхбыстрый DISTINCT с использованием рекурсивного CTE:

USE     tempdb;
GO
DROP    TABLE dbo.Test;
GO
CREATE  TABLE 
        dbo.Test 
        (
        data            INTEGER NOT NULL,
        );
GO
CREATE  CLUSTERED INDEX c ON dbo.Test (data);
GO
-- Lots of duplicated values
INSERT  dbo.Test WITH (TABLOCK)
        (data)
SELECT  TOP (5000000)
        ROW_NUMBER() OVER (ORDER BY (SELECT 0)) / 117329
FROM    master.sys.columns C1,
        master.sys.columns C2,
        master.sys.columns C3;
GO



SET     STATISTICS TIME ON;

-- 1591ms CPU
SELECT  DISTINCT 
        data
FROM    dbo.Test;

- 15 мс процессора

WITH    RecursiveCTE
AS      (
        SELECT  data = MIN(T.data)
        FROM    dbo.Test T
        UNION   ALL
        SELECT  R.data
        FROM    (
                -- A cunning way to use TOP in the recursive part of a CTE Smile
                SELECT  T.data,
                        rn = ROW_NUMBER() OVER (ORDER BY T.data)
                FROM    dbo.Test T
                JOIN    RecursiveCTE R
                        ON  R.data < T.data
                ) R
        WHERE   R.rn = 1
        )
SELECT  *
FROM    RecursiveCTE
OPTION  (MAXRECURSION 0);

SET     STATISTICS TIME OFF;
GO
DROP    TABLE dbo.Test;

Рекурсивный CTE в 100 раз эффективнее :-) Этот тип ускорения был бы чрезвычайно полезен для моего текущего проекта, но я не уверен, в каких случаях этот подход выгоден.

Если честно: я не понимаю, почему это так сильно ускоряет запрос и почему база данных не может выполнить эту оптимизацию сама. Можете ли вы объяснить, как это работает и почему это так эффективно?


Редактировать: я вижу аналогичный эффект на sybase, поэтому этот подход не подходит только для sql-сервера.

Подвопрос: полезна ли рекурсивная CTE для других систем баз данных?

Автор: Beginner Источник Размещён: 14.03.2017 09:19

Ответы (2)


6 плюса

24123 Репутация автора

Решение

Пол Уайт подробно объяснил этот «трюк» в своем посте « Настройка производительности плана полного запроса» в разделе « Поиск отличительных значений ».

Почему база данных не может сделать эту оптимизацию сама?

Полезна ли рекурсивная CTE для других систем баз данных?

Оптимизатор не идеален, и он не реализует все возможные методы. Люди просили Microsoft реализовать это. См. Этот элемент подключения. Осуществить индекс пропустить сканирование . Он был закрыт как не исправит, но это не значит, что он не будет решен в будущем. Другие СУБД, возможно, реализовали это (элемент Connect говорит, что Oracle реализует эту оптимизацию). Если этот вид оптимизации реализован в движке СУБД, то этот «трюк» не нужен, и оптимизатор выберет оптимальный метод расчета результата на основе доступной статистики.

Я не понимаю, почему это так сильно ускоряет запрос.

Я не уверен, в каких случаях этот подход выгоден

Простой DISTINCTзапрос сканирует весь индекс. «Сканирование» означает, что он читает каждую страницу индекса с диска и собирает значения в памяти (или tempdb), чтобы получить список различных значений.

Если вы знаете, что в таблице много строк, но только несколько разных значений, чтение всех этих повторяющихся значений - пустая трата времени. Рекурсивный CTE заставляет сервер искать индекс для первого отдельного значения, затем искать индекс для второго значения и так далее. «Поиск» означает, что сервер использует двоичный поиск в индексе для поиска значения. Обычно один запрос требует чтения только нескольких страниц с диска. «Индекс» - это сбалансированное дерево.

Если таблица имеет только несколько отдельных значений, поиск быстрее выполняется несколько раз, чем чтение всех страниц индекса. С другой стороны, если имеется много разных значений, то будет быстрее читать все страницы последовательно, чем искать каждое последующее значение. Это должно дать вам представление о том, в каких случаях этот подход выгоден.

Очевидно, что если таблица небольшая, ее быстрее сканировать. Только когда таблица становится «достаточно большой», вы начинаете видеть разницу в производительности.


На dba.se есть связанный вопрос: возможно ли получить параллельный план на основе поиска для отдельного / группового?

Автор: Vladimir Baranov Размещён: 17.03.2017 09:06

0 плюса

3799 Репутация автора

Заметный момент, когда вышеуказанный скрипт запускается на моей машине.

Отдельный запрос = кластерное сканирование составляет 94%

Рекурсивный запрос = кластерное сканирование составляет 14%

Это главная причина

Disctinct запрос

Время процессора = 920 мс, прошедшее время = 211 мс.

Рекурсивный запрос

Время процессора = 0 мс, прошедшее время = 64 мс.

В этом примере рекурсивный вариант может показаться хорошим.

но в реальных примерах получение отличного результата с помощью рекурсии может быть не очень хорошей идеей.

Поэтому вы не можете сказать: « Сверхбыстрый DISTINCT с использованием рекурсивного CTE: »

Автор: KumarHarsh Размещён: 21.03.2017 06:31
Вопросы из категории :
32x32