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

Откуда:
Сообщений: 748
Нужно поместить сложный запрос во view. Это ограничение сторонней системы.
  • 1. Можно сделать табличную функцию, где заполняется нескольких табличных переменных и происходит join. Такая функция сначала построит весь набор данных и view будет фильтроваться по нему. Так работает быстро на небольшом числе записей, но их количество скоро вырастет.
  • 2. Можно сделать inline функцию или cte из нескольких частей внутри view. Но select из такого view уже выполняется долго даже на малом числе записей.

    Как хинтами или ещё как-то указать оптимизатору, что нужно нечто вроде spool на каждую часть cte для эмуляции варианта №1 внутри варианта №2? Чтобы эту конструкцию поместить во view и фильтры во where могли бы ограничивать хотя бы частично число исходных данных для join, а не готовый результат.
  • 29 авг 18, 11:12    [21657288]     Ответить | Цитировать Сообщить модератору
     Re: Принудительный spool  [new]
    Rankatan
    Member

    Откуда:
    Сообщений: 250
    tunknown
    Как хинтами или ещё как-то указать оптимизатору, что нужно нечто вроде spool на каждую часть cte для эмуляции варианта №1 внутри варианта №2? Чтобы эту конструкцию поместить во view и фильтры во where могли бы ограничивать хотя бы частично число исходных данных для join, а не готовый результат.

    Рекурсией из одного шага
    29 авг 18, 12:13    [21657446]     Ответить | Цитировать Сообщить модератору
     Re: Принудительный spool  [new]
    aleks222
    Member

    Откуда:
    Сообщений: 854
    Правильно написанный вариант 1 будет работать всегда и быстро.

    Это следствие тривиального умозаключения.
    29 авг 18, 13:16    [21657538]     Ответить | Цитировать Сообщить модератору
     Re: Принудительный spool  [new]
    tunknown
    Member

    Откуда:
    Сообщений: 748
    Rankatan
    Рекурсией из одного шага
    Не могу догадаться как :-(

    aleks222
    Правильно написанный вариант 1 будет работать всегда и быстро.
    Если функция собирает миллион записей, хотя нужно всего 10 по условию, то как она будет быстрой? Или оптимизатор умеет пробрасывать внешнее условие в неинлайновую функцию?
    29 авг 18, 16:09    [21657848]     Ответить | Цитировать Сообщить модератору
     Re: Принудительный spool  [new]
    TaPaK
    Member

    Откуда: Kiev
    Сообщений: 6794
    tunknown,

    умеет
    29 авг 18, 16:14    [21657856]     Ответить | Цитировать Сообщить модератору
     Re: Принудительный spool  [new]
    tunknown
    Member

    Откуда:
    Сообщений: 748
    TaPaK
    умеет
    Где почитать? У меня не выходит написать неинлайновую функцию, чтобы внешний параметр учитывался внутри неё.

    select * from dbo.F() -- возвращает 442 записи
    select * from dbo.F() where id=1 -- возвращает 1 запись
    

    Все показатели в профилере различаются не более, чем на 0.03% включая reads.
    29 авг 18, 16:31    [21657873]     Ответить | Цитировать Сообщить модератору
     Re: Принудительный spool  [new]
    TaPaK
    Member

    Откуда: Kiev
    Сообщений: 6794
    tunknown,
    извинямс, то я неправильно прочтиал :) Так конечно не умеет
    29 авг 18, 16:34    [21657876]     Ответить | Цитировать Сообщить модератору
     Re: Принудительный spool  [new]
    Rankatan
    Member

    Откуда:
    Сообщений: 250
     with x as (
    	select 'text' a
     ),
     z as
     (
    	 select x.*,null tmp from x
    	 union all
    	 select * from z where tmp is not null
     )
     select * from z
    
    29 авг 18, 16:38    [21657879]     Ответить | Цитировать Сообщить модератору
     Re: Принудительный spool  [new]
    tunknown
    Member

    Откуда:
    Сообщений: 748
    Rankatan
     with x as (
    	select 'text' a
     ),
     z as
     (
    	 select x.*,null tmp from x
    	 union all
    	 select * from z where tmp is not null
     )
     select * from z
    
    Спасибо, помогло. По крайней мере запрос не обёрнутый во view ускорился существенно. Хотя в документации spool в CTE упоминается только относительно remote server.
    29 авг 18, 16:51    [21657896]     Ответить | Цитировать Сообщить модератору
     Re: Принудительный spool  [new]
    invm
    Member

    Откуда: Москва
    Сообщений: 9122
    tunknown,

    Если вам непременно нужна фильтрация до соединения, то это делается гораздо проще и без хинтов и манипуляций с рекурсией:
    select
     ...
    from
     (select top (9223372036854775807) ... from MyTable where ...) t join ...
    
    29 авг 18, 17:20    [21657939]     Ответить | Цитировать Сообщить модератору
     Re: Принудительный spool  [new]
    tunknown
    Member

    Откуда:
    Сообщений: 748
    tunknown
    По крайней мере запрос не обёрнутый во view ускорился существенно. Хотя в документации spool в CTE упоминается только относительно remote server.
    Обёрнутый во view запрос с CTE тормозит. Все Eager Spool вернулись в Lazy Spool. Однако, обёрнутый в инлайн-функцию сохранил Eager Spool в плане, фильтрует по внешнему условию в самом начале и работает быстро.

    Не может ли такое (недокументированное?)поведение измениться в очередном SP/CU? У меня 12.0.5557.0 (X64)
    29 авг 18, 17:30    [21657956]     Ответить | Цитировать Сообщить модератору
     Re: Принудительный spool  [new]
    tunknown
    Member

    Откуда:
    Сообщений: 748
    invm

    Если вам непременно нужна фильтрация до соединения, то это делается гораздо проще и без хинтов и манипуляций с рекурсией
    Мне нужна не только фильтрация, но и Eager Spool. Запрос достаточной развестый, работал быстро только в процедуре или в неинлайновой функции с табличными переменными.

    top (9223372036854775807) попробую на всякий случай.
    29 авг 18, 17:33    [21657962]     Ответить | Цитировать Сообщить модератору
     Re: Принудительный spool  [new]
    invm
    Member

    Откуда: Москва
    Сообщений: 9122
    tunknown
    Мне нужна не только фильтрация, но и Eager Spool
    Не нужен вам Eager Spool. С точки зрения дальнейшего потребления данных другими итераторами, он ничем не отличается от Lazy.
    План нужно анализировать и искать причину тормозов.
    29 авг 18, 17:49    [21657979]     Ответить | Цитировать Сообщить модератору
     Re: Принудительный spool  [new]
    tunknown
    Member

    Откуда:
    Сообщений: 748
    invm
    Не нужен вам Eager Spool. С точки зрения дальнейшего потребления данных другими итераторами, он ничем не отличается от Lazy.
    План нужно анализировать и искать причину тормозов.
    В запросе несколько вложенных CTE, join на самих себя, по некоторым делается сложение текста через xml и т.д. Естественно, он тормозит без "материализации".
    29 авг 18, 17:54    [21657987]     Ответить | Цитировать Сообщить модератору
     Re: Принудительный spool  [new]
    invm
    Member

    Откуда: Москва
    Сообщений: 9122
    tunknown,

    CTE не материализуется в том смысле, который вы вкладываете в это понятие. Сколько раз оно упомянуто в запросе, столько и будет выполнено. Короче, это просто синтаксический сахар.
    29 авг 18, 18:24    [21658017]     Ответить | Цитировать Сообщить модератору
     Re: Принудительный spool  [new]
    Rankatan
    Member

    Откуда:
    Сообщений: 250
    invm
    tunknown,

    CTE не материализуется в том смысле, который вы вкладываете в это понятие. Сколько раз оно упомянуто в запросе, столько и будет выполнено. Короче, это просто синтаксический сахар.

    Там можно добиться, чтобы формировался spool через рекурсию и потом в остальных запросах обращение происходило именно к этому сформированному spool, но сейчас сходу не смог сделать пример, надо экспериментировать.
    30 авг 18, 11:46    [21658619]     Ответить | Цитировать Сообщить модератору
     Re: Принудительный spool  [new]
    aleks222
    Member

    Откуда:
    Сообщений: 854
    tunknown
    invm
    Не нужен вам Eager Spool. С точки зрения дальнейшего потребления данных другими итераторами, он ничем не отличается от Lazy.
    План нужно анализировать и искать причину тормозов.
    В запросе несколько вложенных CTE, join на самих себя, по некоторым делается сложение текста через xml и т.д. Естественно, он тормозит без "материализации".


    И так же естественно, что select into - это не для тредстартера.
    Тредстартер мечтает о ms sql c телепатической системой управления...
    30 авг 18, 14:00    [21658858]     Ответить | Цитировать Сообщить модератору
     Re: Принудительный spool  [new]
    Руслан Дамирович
    Member

    Откуда: Резиновая нерезиновая
    Сообщений: 940
    Rankatan
    Там можно добиться, чтобы формировался spool через рекурсию и потом в остальных запросах обращение происходило именно к этому сформированному spool, но сейчас сходу не смог сделать пример, надо экспериментировать.

    это было бы достойно отдельной статьи!
    30 авг 18, 14:45    [21658951]     Ответить | Цитировать Сообщить модератору
     Re: Принудительный spool  [new]
    Shakill
    Member

    Откуда: мск
    Сообщений: 1870
    Rankatan
    Там можно добиться, чтобы формировался spool через рекурсию и потом в остальных запросах обращение происходило именно к этому сформированному spool, но сейчас сходу не смог сделать пример, надо экспериментировать.

    ни разу такого не видел, очень интересно посмотреть
    30 авг 18, 15:14    [21659009]     Ответить | Цитировать Сообщить модератору
     Re: Принудительный spool  [new]
    tunknown
    Member

    Откуда:
    Сообщений: 748
    Rankatan
    Там можно добиться, чтобы формировался spool через рекурсию и потом в остальных запросах обращение происходило именно к этому сформированному spool, но сейчас сходу не смог сделать пример, надо экспериментировать.
    При внешней фильтрации по такой "материализуемой" функции Eager Spool выживает не всегда. Иногда сваливается в Lazy, видимо, в зависимости от совпадения фильтра с индексами. При этом сваливании производительность хотя и падает, но остаётся приличной.

    Кстати, откуда такой метод? Личные эксперименты, документация или чей-то опыт?
    31 авг 18, 09:10    [21659661]     Ответить | Цитировать Сообщить модератору
     Re: Принудительный spool  [new]
    Владислав Колосов
    Member

    Откуда:
    Сообщений: 7399
    tunknown,

    Вы добьетесь только того, что при очередном обновлении сервера Ваши конструкции улетят в корзину. Пересмотрите архитектуру данных.
    31 авг 18, 12:06    [21660015]     Ответить | Цитировать Сообщить модератору
     Re: Принудительный spool  [new]
    invm
    Member

    Откуда: Москва
    Сообщений: 9122
    tunknown
    При внешней фильтрации по такой "материализуемой" функции Eager Spool выживает не всегда. Иногда сваливается в Lazy, видимо, в зависимости от совпадения фильтра с индексами.
    Продолжаете упорствовать?

    Читайте до полного понимания - Eager Spool и Lazy Spool.
    31 авг 18, 13:11    [21660169]     Ответить | Цитировать Сообщить модератору
     Re: Принудительный spool  [new]
    tunknown
    Member

    Откуда:
    Сообщений: 748
    Владислав Колосов
    tunknown,

    Вы добьетесь только того, что при очередном обновлении сервера Ваши конструкции улетят в корзину. Пересмотрите архитектуру данных.
    Возможность ломающего обновления меня огорчает. Но другого решения мне не видно.

    invm
    Продолжаете упорствовать?

    Читайте до полного понимания
    Прочитал. Lazy спулит только необходимые записи, Eager весь указанный набор данных, в моём случае- каждую вложенную CTE. Такой костыль в моём случае превращает синтаксический сахар в физическую "материализацию". План+профилер подтверждают многократное ускорение.

    Работа с основной таблицей не предусматривает update/delete, только нечастые insert. Предполагаю, что упомянутые по ссылкам проблемы в моём случае имеют низкий приоритет.
    31 авг 18, 16:22    [21660477]     Ответить | Цитировать Сообщить модератору
     Re: Принудительный spool  [new]
    invm
    Member

    Откуда: Москва
    Сообщений: 9122
    tunknown
    Прочитал. Lazy спулит только необходимые записи, Eager весь указанный набор данных
    Плохо прочитали. Спулят они одно и то же, только eager все сразу, а lazy постепенно.
    tunknown
    План+профилер подтверждают многократное ускорение.
    Если план отличается только типом конкретного спула, ни о каком многократном ускорении речи быть не может.
    31 авг 18, 17:14    [21660573]     Ответить | Цитировать Сообщить модератору
     Re: Принудительный spool  [new]
    tunknown
    Member

    Откуда:
    Сообщений: 748
    invm
    Плохо прочитали. Спулят они одно и то же, только eager все сразу, а lazy постепенно.
    Это и нужно.

    invm
    Если план отличается только типом конкретного спула, ни о каком многократном ускорении речи быть не может.
    План развесистый, он отличается кроме Lazy<->Eager некоторыми перестановками операторов.
    31 авг 18, 17:53    [21660639]     Ответить | Цитировать Сообщить модератору
    Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
    Все форумы / Microsoft SQL Server Ответить