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

Откуда:
Сообщений: 311
Добрый день.

+ @@version
Microsoft SQL Server 2014 (SP1-CU4) (KB3106660) - 12.0.4436.0 (X64) 
Dec 2 2015 16:09:44
Copyright (c) Microsoft Corporation
Enterprise Edition: Core-based Licensing (64-bit) on Windows NT 6.3 <X64> (Build 9600: )


В CLR реализована табличная ф-я объявленная так:
+
  [DllImport("rpcrt4.dll", SetLastError = true)]
  static extern int UuidCreateSequential(out Guid guid);

  [SqlFunction(
      IsDeterministic = false,
      IsPrecise = false,
      FillRowMethodName = "fill_guid",
      TableDefinition = "[RN] int, [SGuid] uniqueidentifier")]
  public static IEnumerable SequentialId(SqlInt32 Count)
  {}


Вопрос:
Почему запросы следующего вида
+
select *
from dbo.SomeTable
cross apply dbo.SequentialId(1)
-- or
select *, (select [SGuid] from dbo.SequentialId(1))
from dbo.SomeTable


оптимизатор запросов "оптимизирует" так, что dbo.SequentialId() вызывается только один раз (то вверх плана переместит, то table spool перед ф-ей воткнет).

Если сказано же "IsDeterministic = false".

Или я чего то не понимаю ?
9 авг 16, 08:32    [19518571]     Ответить | Цитировать Сообщить модератору
 Re: CLR TVF SqlFunction и странное поведение оптимизатора запросов  [new]
Mike_za
Member

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

Может быть вам нужна скалярная функция?
10 авг 16, 07:34    [19522997]     Ответить | Цитировать Сообщить модератору
 Re: CLR TVF SqlFunction и странное поведение оптимизатора запросов  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31438
Greenhorn
оптимизатор запросов "оптимизирует" так, что dbo.SequentialId() вызывается только один раз (то вверх плана переместит, то table spool перед ф-ей воткнет).

Если сказано же "IsDeterministic = false".
Непонятно, почему сервер должен вызывать функцию не один раз, а несколько, даже если она недетерминированная.
А сколько раз должен? Сколько строк в SomeTable?
А если запрос не такой простой, например? То столько раз, сколько в кросс-джойне всех остальных таблиц? Планы то могут быть разные.
10 авг 16, 09:57    [19523356]     Ответить | Цитировать Сообщить модератору
 Re: CLR TVF SqlFunction и странное поведение оптимизатора запросов  [new]
Greenhorn
Member

Откуда:
Сообщений: 311
alexeyvg
Greenhorn
оптимизатор запросов "оптимизирует" так, что dbo.SequentialId() вызывается только один раз (то вверх плана переместит, то table spool перед ф-ей воткнет).

Если сказано же "IsDeterministic = false".
Непонятно, почему сервер должен вызывать функцию не один раз, а несколько, даже если она недетерминированная.
А сколько раз должен? Сколько строк в SomeTable?
А если запрос не такой простой, например? То столько раз, сколько в кросс-джойне всех остальных таблиц? Планы то могут быть разные.


Добрый день.

При создании ф-ии был расчет на то, что раз ф-я недетерминированная (т.е. результат от вызова к вызову может отличаться),
то SQL при APPLY будет вынужден ее вызывать столько раз сколько строк слева.
Но, не срослось....

Впрочем, причина ясна - "раз в ф-ю поля из набора слева не передаются, то SQL считает, что результат можно и заранее получить".
Понятно, что при этом кол-во вызовов CLR_ки будет меньше (по мнению SQL_я = 1000), а если строк слева будет больше,
то в конечном счете запрос выполнится быстрее ...

Вот только не уверен, что это правильно при недетерминированности.
Хотел увидеть аргументы "ЗА" такое поведение.

PS. Что касается скалярной ф-ии, то не постигнет ли ее та же учесть ?
Как проверить ?
Где гарантии, что SQL не сохранит значение единственного вызова для последующей вставки во все строки (например как GETDATE()) ?
Т.е. как добиться такого же поведения от CLR скалярки, что и NEWID(), например ?
10 авг 16, 16:20    [19525639]     Ответить | Цитировать Сообщить модератору
 Re: CLR TVF SqlFunction и странное поведение оптимизатора запросов  [new]
o-o
Guest
построчно вызываются *скалярные юзерские* функции, а не инлайновые.
и вам нужна скалярная функция.
при чем тут недетерминированность?
NEWID это как раз единственная *встроенная* недетерминированная ф-ция,
вызываемая построчно.
т.е. она как раз исключение, а не правило
10 авг 16, 16:42    [19525771]     Ответить | Цитировать Сообщить модератору
 Re: CLR TVF SqlFunction и странное поведение оптимизатора запросов  [new]
Владислав Колосов
Member

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

если знать, как сиквел обрабатывает признак неопределенности CLR, то можно понять последствия. Может он никак не обрабатывает.
А вы пробовали вызывать с default параметром, а не с 1?
10 авг 16, 16:50    [19525839]     Ответить | Цитировать Сообщить модератору
 Re: CLR TVF SqlFunction и странное поведение оптимизатора запросов  [new]
SomewhereSomehow
Member

Откуда: Moscow
Сообщений: 2480
Блог
Greenhorn,

На тему того, сколько раз и как вызывается скалярная функция была большая дискуссия вот тут:
https://connect.microsoft.com/SQLServer/feedback/details/350485/bug-with-newid-and-table-expressions

Приведу резюме команды разработки оттуда:
Posted by Jim MSFT on 07.07.2008 at 9:27

Hi,

Closing the loop . . . I've discussed this question with the Dev team. And eventually we have decided not to change current behavior, for the following reasons:

1) The optimizer does not guarantee timing or number of executions of scalar functions. This is a long-estabilished tenet. It's the fundamental 'leeway' tha allows the optimizer enough freedom to gain significant improvements in query-plan execution.

2) This "once-per-row behavior" is not a new issue, although it's not widely discussed. We started to tweak its behavior back in the Yukon release. But it's quite hard to pin down precisely, in all cases, exactly what it means! For example, does it a apply to interim rows calculated 'on the way' to the final result? - in which case it clearly depends on the plan chosen. Or does it apply only to the rows that will eventually appear in the completed result? - there's a nasty recursion going on here, as I'm sure you'll agree!

3) As I mentioned earlier, we default to "optimize performance" - which is good for 99% of cases. The 1% of cases where it might change results are fairly easy to spot - side-effecting 'functions' such as NEWID - and easy to 'fix' (trading perf, as a consequence). This default to "optimize performance" again, is long-established, and accepted. (Yes, it's not the stance chosen by compilers for conventional programming languages, but so be it).

So, our recommendations are:

a) Avoid reliance on non-guaranteed timing and number-of-executions semantics.
b) Avoid using NEWID() deep in table expressions.
c) Use OPTION to force a particular behavior (trading perf)

Hope this explanation helps clarify our reasons for closing this bug as "won't fix".

Thanks,

Jim


По поводу CLR, практически им не пользуюсь, так что не могу сказать, как оптимизатор обрабатывает или не обрабатывает такие функции с разными параметрами. При беглом просмотре документации к свойству IsDeterministic выясняется, что там нет ничего про гарантии вызова или не вызова функции для каждой строки, а значит, даже если покопаться и выяснить такие нюансы, то использовать их в реальной системе на постоянной основе все равно будет нельзя, т.к. это не документировано и может поменяться в любой момент. Может быть, я плохо искал, попробуйте сами найти что-то об этом, если не найдете, то лучше не пользуйтесь.

Если вам нужна какая-то последовательность, возьмите штатный sequence, identity. Если нужны непрерывные счетчики, можете поискать по этому форуму, много раз обсуждалось, предлагались разные решения и, насколько я помню, даже обсуждалась их производительность.
10 авг 16, 17:12    [19525940]     Ответить | Цитировать Сообщить модератору
 Re: CLR TVF SqlFunction и странное поведение оптимизатора запросов  [new]
Greenhorn
Member

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

Огромное спасибо за ответ.
Пошел изучать ссылку ...

По сути, в одном проекте понадобился аналог NEWSEQUENTIALID(), но не имеющий ограничений ...

Понятно, что можно заполнить временную табличку DEFAULT(NEWSEQUENTIALID()).
Но очень уж это не удобно. Да еще и кол-во заранее не известно ...

Вот и попробовал изобрести удобный велосипед с квадратными колесами
10 авг 16, 17:28    [19526019]     Ответить | Цитировать Сообщить модератору
 Re: CLR TVF SqlFunction и странное поведение оптимизатора запросов  [new]
SomewhereSomehow
Member

Откуда: Moscow
Сообщений: 2480
Блог
SomewhereSomehow,

В дополнение, не знаю, что вы пытаетесь реализовать, но если вдруг вам нужно просто пронумеровать строки в выводе, то используйте row_number.
10 авг 16, 17:30    [19526029]     Ответить | Цитировать Сообщить модератору
 Re: CLR TVF SqlFunction и странное поведение оптимизатора запросов  [new]
invm
Member

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

Чтобы табличная функция с константным параметром вызывалась для каждой строки таблицы нужно немного схитрить. Например, так:
select *
from dbo.SomeTable a
cross apply dbo.SequentialId(1 + a.SomeField - a.SomeField) b
10 авг 16, 18:30    [19526255]     Ответить | Цитировать Сообщить модератору
 Re: CLR TVF SqlFunction и странное поведение оптимизатора запросов  [new]
Greenhorn
Member

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

Чтобы табличная функция с константным параметром вызывалась для каждой строки таблицы нужно немного схитрить. Например, так:
select *
from dbo.SomeTable a
cross apply dbo.SequentialId(1 + a.SomeField - a.SomeField) b

Спасибо, и так тоже работает.
А так смешнее (и быстрее):
select *
from dbo.SomeTable a
cross apply dbo.SequentialId(1 ^ a.SomeField ^ a.SomeField) b
10 авг 16, 19:00    [19526368]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить