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

Откуда: Киев
Сообщений: 444
Пишется сложный запрос. Для удобства и производительности выбрана реализация в виде inline table function. Вместо временных таблиц используются CTE. Проблема в том, что при просмотре плана запроса видно, что обращения к CTE каждый раз заменяются телом CTE, такая себе макроподстановка. Отчего план запроса состоит из сплошных повторяющихся веток и запрос работает очень долго. А хотелось бы, чтобы один раз посчитанное множество переиспользовалось. Ожидалось, что появятся table spool что-ли.

Как можно форсировать переиспользование подвыборок, которые я оформил в виде CTE?
14 окт 12, 13:44    [13315562]     Ответить | Цитировать Сообщить модератору
 Re: Замена временных таблиц на CTE. Но CTE работает как макроподстановка.  [new]
aleks2
Guest
Только временные таблицы и табличные переменные.
14 окт 12, 14:46    [13315657]     Ответить | Цитировать Сообщить модератору
 Re: Замена временных таблиц на CTE. Но CTE работает как макроподстановка.  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31948
web_fox
Для удобства и производительности выбрана реализация в виде inline table function. Вместо временных таблиц используются CTE.
Удобсто действительно выше, но для производительности нужно не отказываться от использования временных таблиц и табличных переменных (всегда переходить на них тоже неправильно, поскольку тут придётся самому выбирать стратегию выполнения запроса, вместо предоставления выбора сиквелу, и может получиться так, что при разных значениях параметров эта стратегия должна меняться)
14 окт 12, 15:15    [13315706]     Ответить | Цитировать Сообщить модератору
 Re: Замена временных таблиц на CTE. Но CTE работает как макроподстановка.  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6724
web_fox
Как можно форсировать переиспользование подвыборок, которые я оформил в виде CTE?
Упростить запрос.

То что вы выбрали ITF и CTE +100500 вам. Но это всё только прелюдия и следствие, а не причина и средство.

Притом не ведитесь на вариант - "Мужик забей, не включай голову, сразу впендёрь времянку".

Повторное использование, да иногда они не глупость разраба, это или использование в нескольких независимых запросах (и то не всегда) и в редко вывернутых, чаще в костылях, ну и да - в тех случаях о которых вы возможно говорите.
Тогда надо обязательно голосовать тута за управлением спулом. Точнее надо так уточняющий коммент забацать.

Вот вы приведите здесь случай когда надо.
14 окт 12, 17:56    [13316049]     Ответить | Цитировать Сообщить модератору
 Re: Замена временных таблиц на CTE. Но CTE работает как макроподстановка.  [new]
web_fox
Member

Откуда: Киев
Сообщений: 444
Коллеги, спасибо за ответы.

Итого:
1. Нашёл топики с обсуждением этой проблемы:
Есть ли в SQL Server аналог ораклового хинта "+ materialize" ?
CTE. Вычисляется каждый раз заново

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

2. Написал аналогичную табличную функцию с табличными переменными. 3 CTE вынес в виде табличных переменных с кластерними ключами.
MSDN говорит(http://msdn.microsoft.com/en-us/library/ms175010.aspx): Do not use table variables to store large amounts of data (more than 100 rows). table variables are not supported in the SQL Server optimizer's cost-based reasoning model. У меня сотни тысяч записей,
поэтому пригодилась статья http://blogs.msdn.com/b/psssql/archive/2010/08/24/query-performance-and-table-variables.aspx. Удалось ускорить в 2 раза, но поддерживать такую функцию уже плохо, ведь мы сами за оптимизатор разбили её по частям и приходится ручками набивать обьявления табличных переменных.

Однако, в ходе отладки были найдено пару узких мест с inner join, которые были заменены на hash join, что привело также к ускорению и исходной функции. Выигрышь переписанной нивелировался. В итоге, на худшем варианте inline работала за 30 сек, а с табличными переменными - за 28 сек.
Однако ~90% запросов идёт с WHERE, которые дают сильное ускорение для inline-функции.

В итоге, хоть inline-функция и осталась с огромным дублирующимся планом в 217 строк, но выполняется в худшем варианте как и переписанная без дубликатов, не стал уже выяснять почему, время на оптимизацию истекло.
14 окт 12, 20:02    [13316336]     Ответить | Цитировать Сообщить модератору
 Re: Замена временных таблиц на CTE. Но CTE работает как макроподстановка.  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31948
web_fox
Прямого решения этой проблемы в MSSQL по сути нет. Переход с табличной функции на процедуру для использования временных таблиц не рассматриваю в принципе. Придётся попробовать переписывать на табличные переменные.
Правильнее сказать: прямое решение есть, но я его не рассматриваю - просто не нравится оно мне :-)
14 окт 12, 20:11    [13316366]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить