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

Откуда: Запорожье
Сообщений: 54129
я в MSSQL новичок, прошу прощения за вопросы, которые другим покажутся простыми
   SELECT tha.[ActivityId],
          tha.ParentID,
          max(tha.ActivityStartDate) ActualStartDate,
          max(tha.ActivityEndDate) ActualEndDate
          myUdf('Некий Параметр', max(tha.ActivityStartDate), max(tha.ActivityEndDate))
     FROM [table_hand_action] tha
     join [table_service_request] tsr on tsr.id = tha.ParentID and tsr.status = N'Закрыто'
    where tha.id_header = 435
      and tsr.id_header = 435                        
    group by
          tha.[ActivityId],
          tha.ParentID
был план merge join и запрос работал пару сек, а после добавления myUdf скатился в NL и стал работать часами

ну я временно вставил inner merge join, и план стал прежний и скорость приемлемая
     FROM [table_hand_action] tha
     inner merge join [table_service_request] tsr on tsr.id = tha.ParentID and tsr.status = N'Закрыто'


раньше работал с Ораклами и смены плана в таких ситуациях никогда не наблюдал

кто-то поможет советом, как с этими UDF правильно работать в таких случаях?



на показанных таблицах индексов нет, кроме PK
select count(*)                      FROM [table_hand_action]      -- 1440282
select count(*)                      FROM [table_service_request]  -- 2386219
select count(*)                      FROM [table_hand_action]      where id_header = 435 -- 7691
select count(*)                      FROM [table_service_request]  where id_header = 435 -- 15790
select count(distinct id_header)     FROM [table_hand_action]      -- 126
select count(distinct id_header)     FROM [table_service_request]  -- 133
похоже, индексы по id_header создавать уже пора однозначно
30 окт 17, 18:20    [20913161]     Ответить | Цитировать Сообщить модератору
 Re: Использование скалярной UDF в select_expression поменяло план  [new]
TaPaK
Member

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

скалярные функции это тёмный ящик для оптимизатора, вот он и берёт более понятный вариант. Пользуйте Inline function
30 окт 17, 18:23    [20913169]     Ответить | Цитировать Сообщить модератору
 Re: Использование скалярной UDF в select_expression поменяло план  [new]
andreymx
Member

Откуда: Запорожье
Сообщений: 54129
TaPaK,

да он в плане ее как раз красиво расписал
я даже индекс для внутреннего запроса переделал по его совету, чуть помогло, но не в этом случае
30 окт 17, 18:25    [20913175]     Ответить | Цитировать Сообщить модератору
 Re: Использование скалярной UDF в select_expression поменяло план  [new]
TaPaK
Member

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

даже не знаю, читайте 20913169
30 окт 17, 18:26    [20913182]     Ответить | Цитировать Сообщить модератору
 Re: Использование скалярной UDF в select_expression поменяло план  [new]
AlanDenton
Member [скрыт]

Откуда:
Сообщений: 1004
При использовании скалярных функций вы также не получите паралелльный план выполнения. Относительно того что план поменялся - разумеется. Иногда оптимизатор пропихивает выполнение скаляров до агрегации (может это Ваш случай).

Можете так попробовать:

IF OBJECT_ID('tempdb.dbo.#table') IS NOT NULL
    DROP TABLE #table

SELECT tha.[ActivityId]
     , tha.ParentID
     , MAX(tha.ActivityStartDate) AS ActualStartDate
     , MAX(tha.ActivityEndDate) AS ActualEndDate
INTO #table
FROM [table_hand_action] tha
JOIN [table_service_request] tsr ON tsr.Id = tha.ParentID AND tsr.Status = N'Закрыто'
WHERE tha.id_header = 435
    AND tsr.id_header = 435
GROUP BY tha.[ActivityId]
       , tha.ParentID

SELECT *, myUdf('Некий Параметр', ActivityStartDate, ActivityEndDate)
FROM #table

и на 99% скалярные функции зло. Используейте InlineTF
30 окт 17, 18:42    [20913222]     Ответить | Цитировать Сообщить модератору
 Re: Использование скалярной UDF в select_expression поменяло план  [new]
andreymx
Member

Откуда: Запорожье
Сообщений: 54129
Мой удафчик на основании корпоративных календарей считает количество рабочего времени между двумя произвольными таймстемпами, и таких запросов много для разных таблиц.
Куда здесь впихнуть табличные функции, пока не чувствую.
30 окт 17, 19:01    [20913274]     Ответить | Цитировать Сообщить модератору
 Re: Использование скалярной UDF в select_expression поменяло план  [new]
Andy_OLAP
Member

Откуда: я знаю, что Хапоэль Беэр-Шева - чемпион
Сообщений: 3151
andreymx
Мой удафчик на основании корпоративных календарей считает количество рабочего времени между двумя произвольными таймстемпами, и таких запросов много для разных таблиц.
Куда здесь впихнуть табличные функции, пока не чувствую.

Скорее не между 2 произвольными таймстемпами, а между 2 произвольными датами. Ну а раз так - пишется процедура, которая разово заполняет таблицу типа [параметр_выбора_календаря] [int] NOT NULL, [дата_начала_разумная] [datetime] NOT NULL, [дата_окончания_разумная] [datetime] NOT NULL, далее из tha получаете MAX(convert(date,ActivityStartDate) и соединяете left join с подзапросом
(select * from [таблица_расчета_для_календарей] where [параметр_выбора_календаря] = 'Некий Параметр') t2 on t2.дата_начала_разумная = MAX(convert(date,ActivityStartDate) and t2.дата_окончания_разумная = MAX(convert(date,ActivityStartDate) , чтобы не было inner join, если дата за пределами того, что у Вас в предрасчетной таблице.
30 окт 17, 19:30    [20913356]     Ответить | Цитировать Сообщить модератору
 Re: Использование скалярной UDF в select_expression поменяло план  [new]
Andy_OLAP
Member

Откуда: я знаю, что Хапоэль Беэр-Шева - чемпион
Сообщений: 3151
andreymx, ну и конечно после каждого изменения "удафчика" - разово прогоняете процедуру, которая перезаливает предрасчетную таблицу. И все.
Зачем ломиться лбом в кирпичный забор, если рядом в нем открыта калитка.
30 окт 17, 19:32    [20913361]     Ответить | Цитировать Сообщить модератору
 Re: Использование скалярной UDF в select_expression поменяло план  [new]
andreymx
Member

Откуда: Запорожье
Сообщений: 54129
Имелись в виду именно произвольные таймстемпы, с точностью до секунд
30 окт 17, 19:50    [20913393]     Ответить | Цитировать Сообщить модератору
 Re: Использование скалярной UDF в select_expression поменяло план  [new]
Andy_OLAP
Member

Откуда: я знаю, что Хапоэль Беэр-Шева - чемпион
Сообщений: 3151
andreymx
Имелись в виду именно произвольные таймстемпы, с точностью до секунд

Ой-вей! А в соседней теме как раз обсуждали, как бы переделать UDF.
https://www.sql.ru/forum/1274265/cross-apply-clr-table-valued-function-i-parallelizm?hl=inline udf
И Дмитрий Степанов нашел кошерный способ, как обманывать SQL на этапе вычисления плана.
Но Гавриленко предложил для того конкретного примера "Уж лучше начать с QUERYTRACEON 8649", что тоже можно опробовать
30 окт 17, 19:55    [20913406]     Ответить | Цитировать Сообщить модератору
 Re: Использование скалярной UDF в select_expression поменяло план  [new]
andreymx
Member

Откуда: Запорожье
Сообщений: 54129
AlanDenton
При использовании скалярных функций вы также не получите паралелльный план выполнения. Относительно того что план поменялся - разумеется. Иногда оптимизатор пропихивает выполнение скаляров до агрегации (может это Ваш случай)


ЗЫ:
+
мой 15-летний оракловый опыт подсказывает, что UDF меняет план запроса, когда этот самый UDF в предикатах или джойнах, и практически никогда, если в он в select_expression
Ну что ж, буду дальше учиться
30 окт 17, 23:54    [20913920]     Ответить | Цитировать Сообщить модератору
 Re: Использование скалярной UDF в select_expression поменяло план  [new]
Сон Веры Павловны
Member

Откуда:
Сообщений: 5930
andreymx
мой 15-летний оракловый опыт подсказывает, что UDF меняет план запроса, когда этот самый UDF в предикатах или джойнах, и практически никогда, если в он в select_expression

В данном случае этот оракловый опыт неприменим. И еще: даже если скалярная UDF не поменяет план запроса, всё равно в общем случае запрос будет более ресурсозатратным, более медленным, итд - даже если скалярная UDF внутри не содержит SQL-кода, а просто делает некие вычисления.
31 окт 17, 09:31    [20914345]     Ответить | Цитировать Сообщить модератору
 Re: Использование скалярной UDF в select_expression поменяло план  [new]
andreymx
Member

Откуда: Запорожье
Сообщений: 54129
Сон Веры Павловны
даже если скалярная UDF не поменяет план запроса, всё равно в общем случае запрос будет более ресурсозатратным, более медленным, итд - даже если скалярная UDF внутри не содержит SQL-кода, а просто делает некие вычисления.
да, спсб, это ясно
31 окт 17, 09:44    [20914396]     Ответить | Цитировать Сообщить модератору
 Re: Использование скалярной UDF в select_expression поменяло план  [new]
Minamoto
Member

Откуда: Москва
Сообщений: 1162
andreymx
Имелись в виду именно произвольные таймстемпы, с точностью до секунд
И что?

create table #WorkCalendar
(
    WDate date,
    StartTime time,
    EndTime time
);

insert into #WorkCalendar
        (WDate, StartTime, EndTime)
values  ('20171030', '10:00:00', '14:00:00'),
         ('20171030', '15:00:00', '19:00:00'),
         ('20171031', '10:00:00', '14:00:00'),
         ('20171031', '15:00:00', '19:00:00');


create table #table_hand_action
(
    ActivityId int,
    ActivityStartDate datetime, 
    ActivityEndDate datetime
);

insert into #table_hand_action
        (ActivityId, ActivityStartDate, ActivityEndDate)
values  (1, '20171030 11:15:10', '20171031 17:18:15');


select  tha.ActivityId
    ,   sum(
        datediff(ss, 
                 case when tha.ActivityStartDate > cast(wc.WDate as datetime) + cast(wc.EndTime as datetime) then cast(wc.WDate as datetime) + cast(wc.EndTime as datetime)
                      when tha.ActivityStartDate > cast(wc.WDate as datetime) + cast(wc.StartTime as datetime) then tha.ActivityStartDate
                      else cast(wc.WDate as datetime) + cast(wc.StartTime as datetime)
                  end,
                 case when tha.ActivityEndDate < cast(wc.WDate as datetime) + cast(wc.StartTime as datetime) then cast(wc.WDate as datetime) + cast(wc.StartTime as datetime)
                      when tha.ActivityEndDate < cast(wc.WDate as datetime) + cast(wc.EndTime as datetime) then tha.ActivityEndDate
                      else cast(wc.WDate as datetime) + cast(wc.EndTime as datetime)
                  end)) as datediff_in_seconds
from    #table_hand_action as tha
        inner join #WorkCalendar as wc on wc.WDate >= cast(tha.ActivityStartDate as date) and wc.WDate < tha.ActivityEndDate
group by tha.ActivityId

drop table #WorkCalendar;
drop table #table_hand_action;
31 окт 17, 12:02    [20915017]     Ответить | Цитировать Сообщить модератору
 Re: Использование скалярной UDF в select_expression поменяло план  [new]
andreymx
Member

Откуда: Запорожье
Сообщений: 54129
Коллеги, всем спасибо, всё стало летать

после
UPDATE STATISTICS table_hand_action
UPDATE STATISTICS table_service_request

:)
31 окт 17, 13:34    [20915368]     Ответить | Цитировать Сообщить модератору
 Re: Использование скалярной UDF в select_expression поменяло план  [new]
Cristiano_Rivaldo
Member

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

Привет ! ) Ну как тебе MS SQL после Оракла ?))
1 ноя 17, 09:55    [20918081]     Ответить | Цитировать Сообщить модератору
 Re: Использование скалярной UDF в select_expression поменяло план  [new]
andreymx
Member

Откуда: Запорожье
Сообщений: 54129
Cristiano_Rivaldo
andreymx,

Привет ! ) Ну как тебе MS SQL после Оракла ?))
изучать и изучать
а так пока, одни непечатные слова
Но это от отсутствия опыта в т.ч.
1 ноя 17, 10:05    [20918092]     Ответить | Цитировать Сообщить модератору
 Re: Использование скалярной UDF в select_expression поменяло план  [new]
Cristiano_Rivaldo
Member

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

Я был удивлен от
неявных транзакций,
отсутствия версионности по дефолту,
select не генерирует исключение на no_data_found,too_many_rows,
необходимость писать везде SET XACT_ABORT ON,
1 ноя 17, 10:27    [20918156]     Ответить | Цитировать Сообщить модератору
 Re: Использование скалярной UDF в select_expression поменяло план  [new]
andreymx
Member

Откуда: Запорожье
Сообщений: 54129
Cristiano_Rivaldo
andreymx,

Я был удивлен от
неявных транзакций,
отсутствия версионности по дефолту,
select не генерирует исключение на no_data_found,too_many_rows,
необходимость писать везде SET XACT_ABORT ON,
неявные транзакции можно привыкнуть

но вот что insert в таблицу блокирует select count(*)
1 ноя 17, 10:58    [20918235]     Ответить | Цитировать Сообщить модератору
 Re: Использование скалярной UDF в select_expression поменяло план  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6801
andreymx
Cristiano_Rivaldo
andreymx,

Я был удивлен от
неявных транзакций,
отсутствия версионности по дефолту,
select не генерирует исключение на no_data_found,too_many_rows,
необходимость писать везде SET XACT_ABORT ON,
неявные транзакции можно привыкнуть

но вот что insert в таблицу блокирует select count(*)


RSCI уже давно
1 ноя 17, 11:04    [20918252]     Ответить | Цитировать Сообщить модератору
 Re: Использование скалярной UDF в select_expression поменяло план  [new]
Cristiano_Rivaldo
Member

Откуда:
Сообщений: 323
TaPaK
andreymx
пропущено...
неявные транзакции можно привыкнуть

но вот что insert в таблицу блокирует select count(*)


RSCI уже давно


"отсутствия версионности по дефолту"

Еще забыл сказать про абсолютно бесполезный на мой взгляд RAISERROR. THROW догадались придумать только в 2012 версии..
1 ноя 17, 11:15    [20918282]     Ответить | Цитировать Сообщить модератору
 Re: Использование скалярной UDF в select_expression поменяло план  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6801
Cristiano_Rivaldo
TaPaK
пропущено...


RSCI уже давно


"отсутствия версионности по дефолту"

Еще забыл сказать про абсолютно бесполезный на мой взгляд RAISERROR. THROW догадались придумать только в 2012 версии..


"отсутствия версионности по дефолту" Azure default RCSI

RAISERROR / THROW все фломастеры не вкусные
1 ноя 17, 11:20    [20918291]     Ответить | Цитировать Сообщить модератору
 Re: Использование скалярной UDF в select_expression поменяло план  [new]
Сон Веры Павловны
Member

Откуда:
Сообщений: 5930
Cristiano_Rivaldo
Я был удивлен от
неявных транзакций,
отсутствия версионности по дефолту,
select не генерирует исключение на no_data_found,too_many_rows,
необходимость писать везде SET XACT_ABORT ON,

А я до сих пор не понимаю, почему в оракле нет инлайновых табличных функций - они нужны хотя бы потому, что оптимизатор часто не может протолкнуть предикаты к месту непосредственного использования. А аналоги cross/outer apply появились только в 12с (а в MSSQL - c 2005-й версии), и в 11g некий отдаленный аналог можно получить только через lateral joins, для чего в каждую сессию после открытия нужно альтерить флагом events '22829 trace name context forever'. И дико бесит, что для промежуточного хранения временной выборки нужно заводить постоянный объект (GTT или табличный тип), т.к. нет табличных переменных. Я тоже могу эту песню продолжать достаточно долго, но это уже будет спецолимпиада.
1 ноя 17, 12:02    [20918462]     Ответить | Цитировать Сообщить модератору
 Re: Использование скалярной UDF в select_expression поменяло план  [new]
andreymx
Member

Откуда: Запорожье
Сообщений: 54129
TaPaK
andreymx,

скалярные функции это тёмный ящик для оптимизатора, вот он и берёт более понятный вариант. Пользуйте Inline function
кстати, послушал тут лекцию
https://www.techdays.ru/videos/6950.html

чел утверждает, что использование скалярных UDF в select_expression не должно менять план (ну кроме того, что параллельность не поддерживается)
8 ноя 17, 17:49    [20937510]     Ответить | Цитировать Сообщить модератору
 Re: Использование скалярной UDF в select_expression поменяло план  [new]
Cammomile
Member

Откуда:
Сообщений: 1214
andreymx

но вот что insert в таблицу блокирует select count(*)


Удивительно, если бы он работал иначе. Если у вас открыта транзакция на вставку, какое по вашему должно возвращаться значение, если транзакция не закреплена?

Если вам "кровь из носу надо прям счаз" то
 Select Count(1) From Table (Nolock) 

Или вот так
SELECT CONVERT(bigint, [rows]) FROM sysindexes WHERE id = OBJECT_ID('TableName') AND indid < 2
8 ноя 17, 18:57    [20937781]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить