Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 MSSQL 2005, запросы с рекурсией и сортировкой  [new]
NFG
Member

Откуда:
Сообщений: 4
Всем доброго здравия!
Подскажите пожалуйста с таким вопросом.

Есть две таблицы.

Первая - Папки
1. id uniqueidentifier
2. pid uniqueidentifier идентификатор родителя
3. name nvarchar

Вторая - Документы
1. id uniqueidentifier
2. pid uniqueidentifier идентификатор папки
3. tested bit проверен/нет

Одна хранит древовидную структуру папок путём указания у каждого элемента ключа родителя из этой же таблицы.
Вторая хранит список файлов связанных с указанными папками.
У файла есть атрибут проверки (проверен или нет).

Была поставлена задача: для корневых папок сосчитать количество всех проверенных внутренних документов и вывести в отсортированном виде 'Имя корневой папки', 'Количество проверенных'.

Первая попытка была такой:

Через рекурсию (WITH и т.д.) формируется список корневых папок со всеми их дочерними.
Эта часть выполняется быстро. Но когда к данной CTE подключаем JOIN таблицу документов запрос начинает выполняться долго. Когда делаем тот же JOIN напрямую к таблице папок, всё быстро.

Почему так происходит? CTE не использует уже информацию об индексах?

Вторая попытка:

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

Запрос стал выполняться быстро. Но появилась другая проблема. Если в рекурсию включать не только идентификаторы папок но и их имена, то потом при группировке для выполнения SUM(), что бы подсчитать количество проверенных документов, запрос начинает выполняться долго.

Третья попытка:

Исключили названия папок из рекурсии.
Выполняем запрос из CTE, группируем по ID, считаем SUM(), к этому результату JOIN подключаем таблицу папок для получения имён.

Запрос выполняется быстро. Количество строк в результате не больше 100.

Когда добавляем ORDER BY имя папки снова резкое замедление запроса, хотя строк не более 100.
Почему?

Четвёртая попытка:

Создаем запрос №1, которые выбирает корневые папки из таблицы папок и сортирует их

Создаём запрос №2 из CTE, группируем корневые папки по ID, считаем SUM() документов

Объединяем запрос №1 JOIN запрос №2 по id папок. Для этого, что бы в первом запросе можно было выполнить ORDER BY добавляем в него инструкцию TOP.

Всё работает быстро и сортирует. Но опять есть нюанс.

Если в TOP указывать просто значение (1000000 заведомо превышающее число корневых папок), то всё формируется нормально. Если же указать TOP 100 PERCENT то сортировка в итоговом результате не происходит.

И снова вопрос почему? :(

Вот такие изыскания.

Извините, если что-то не правильно написал.

Если нужны будут примеры запросов, покажу.

Спасибо!
11 ноя 15, 17:56    [18401887]     Ответить | Цитировать Сообщить модератору
 Re: MSSQL 2005, запросы с рекурсией и сортировкой  [new]
Glory
Member

Откуда:
Сообщений: 104751
NFG
Почему так происходит? CTE не использует уже информацию об индексах?

CTE не есть отдельный запрос. CTE есть лишь синтаксический сахар.

NFG
И снова вопрос почему? :(

Анализируйте план выполнения для каждого случая.
11 ноя 15, 17:59    [18401902]     Ответить | Цитировать Сообщить модератору
 Re: MSSQL 2005, запросы с рекурсией и сортировкой  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 8828
У Вас неэффективная структура хранения по отношению к бизнес-требованиям.
Дерево хорошо получать рекурсией для одного корня, но множественных корней - плохо.
В вашем случае или храните ссылки на родителя во всех ветках или используйте тип данных hierarchyid .
11 ноя 15, 18:07    [18401944]     Ответить | Цитировать Сообщить модератору
 Re: MSSQL 2005, запросы с рекурсией и сортировкой  [new]
NFG
Member

Откуда:
Сообщений: 4
Спасибо!

Glory
CTE не есть отдельный запрос. CTE есть лишь синтаксический сахар.


А если рекурсию делать не через CTE а через курсоры и временные таблицы? Будет ли разница? Или это лучше проверить тестами?
11 ноя 15, 18:19    [18402000]     Ответить | Цитировать Сообщить модератору
 Re: MSSQL 2005, запросы с рекурсией и сортировкой  [new]
NFG
Member

Откуда:
Сообщений: 4
Владислав Колосов
В вашем случае или храните ссылки на родителя во всех ветках

Спасибо. Пока, видимо, будем перерабатывать базу в этом направлении.

Владислав Колосов
или используйте тип данных hierarchyid .

Да. Это хорошая штука. К сожалению у нас MSSQL 2005 :( Там такого нет. Приходится извращаться.

Жаль что с CTE не получается красиво при JOIN :(
11 ноя 15, 18:31    [18402081]     Ответить | Цитировать Сообщить модератору
 Re: MSSQL 2005, запросы с рекурсией и сортировкой  [new]
Glory
Member

Откуда:
Сообщений: 104751
NFG
А если рекурсию делать не через CTE а через курсоры и временные таблицы? Будет ли разница? Или это лучше проверить тестами?

Что лучше - красное или соленое ?

NFG
Жаль что с CTE не получается красиво при JOIN :(

Дело не в СТЕ. А вашем непонимание, что разные запросы выполняются по-разному.
А вовсе не слева-направо, как вы написали.
11 ноя 15, 19:54    [18402423]     Ответить | Цитировать Сообщить модератору
 Re: MSSQL 2005, запросы с рекурсией и сортировкой  [new]
NFG
Member

Откуда:
Сообщений: 4
Glory
NFG
А если рекурсию делать не через CTE а через курсоры и временные таблицы? Будет ли разница? Или это лучше проверить тестами?

Что лучше - красное или соленое ?


Согласен, глупость написал :(

Glory
NFG
Жаль что с CTE не получается красиво при JOIN :(

Дело не в СТЕ. А вашем непонимание, что разные запросы выполняются по-разному.
А вовсе не слева-направо, как вы написали.


С CTE в плане рекурсии первый раз сталкиваюсь.

Я так понимаю, что при формировании запросов система опирается на индексы и связи между таблицами.
А при формировании рекурсивного запроса CTE результат рекурсии строится в памяти и для объединения системе приходится ещё раз просматривать записи, т.к. нет уже индексов по набору результата рекурсии.

Если ошибаюсь, прошу объясните.

Спасибо.
13 ноя 15, 13:35    [18411946]     Ответить | Цитировать Сообщить модератору
 Re: MSSQL 2005, запросы с рекурсией и сортировкой  [new]
Glory
Member

Откуда:
Сообщений: 104751
NFG
Я так понимаю, что при формировании запросов система опирается на индексы и связи между таблицами.
А при формировании рекурсивного запроса CTE результат рекурсии строится в памяти и для объединения системе приходится ещё раз просматривать записи, т.к. нет уже индексов по набору результата рекурсии.

Вы неправильно понимамете
В MSSQL никакие запросы не выполняются слева-направо сверху-вниз по тексту запроса.
В MSSQL оптимизитор разбирает весь текст запроса и анализирует возможные способы его выполнения в поисках наиболее дешевого способа.

Для того, чтобы понять, как выполняется конкретный запрос, смотрят его конкретный план выполнения
13 ноя 15, 13:43    [18411999]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить