Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 Inlined function c типом параметра user-defined table type (+)  [new]
Сон Веры Павловны
Member

Откуда:
Сообщений: 5975
Ситуация: имеется инлайновая UDF вида
create function pdist.uf_worktimes(@year_n smallint)
returns table
as
return (
  with cte as (
    select row_number() over(partition by line_id order by fd, td) id,
    line_id, fd, td, type from pdist.uf_downtimes(@year_n)
  ), cte1 as (
...

где вызываемая в CTE функция - просто обычная табличная функция. В некоторых ситуациях выборка из этой табличной функции мне нужна еще раз после вызова вышеприведенной функции (pdist.uf_worktimes). Для этого я сделал псевдоперегрузку вида
create function pdist.uf_worktimes_x(@year_n smallint, @t_downtimes pdist.ut_line_dates readonly)
returns table
as
return (
  with cte as (
    select row_number() over(partition by line_id order by fd, td) id,
    line_id, fd, td, type from @t_downtimes
    .....

где тип pdist.ut_line_dates определен как
create type pdist.ut_line_dates as table (
  line_id int,
  fd datetime,
  td datetime,
  type smallint
)

Объем выборки, помещаемый в табличный тип, совсем маленький - 10-20 строк; объем результирующей выборки чуть побольше - ~50 строк.
В результате
select line_id, fd, td, type from pdist.uf_worktimes(@year_n)

выполняется за доли секунды;
declare
  @t_downtimes pdist.ut_line_dates;
insert into @t_downtimes
  select line_id, fd, td, type from pdist.uf_downtimes(@year_n) order by line_id, fd, td
select line_id, fd, td, type from pdist.uf_worktimes_x(@year_n, @t_downtimes);

выполняется не знаю сколько - я прождал около получаса, и прервал запрос. Плана выполнения я тоже так и не дождался.
Заглянул в блокировки - там примерно такая картина:

DBNameObjNameIndIdTypeMode
tempdbsysrscols0TABIX
tempdbsysrscols1KEYX
tempdbsysschobjs1KEYX
tempdbsyscolpars1KEYX
tempdbsyscolpars2KEYX
db_logosysobjvalues1KEYS
db_logoTT_ut_line_dates_46B27FE20TABSch-S
db_logouf_worktimes_x0TABSch-S
db_logouf_worktimes_x0TABX

P.S.
Если код функции просто выполнять в блоке вида
declare
  @t_downtimes pdist.ut_line_dates;
insert into @t_downtimes
  select * from pdist.uf_downtimes(@year_n);
  ;with cte as (
    select row_number() over(partition by line_id order by fd, td) id,
    line_id, fd, td, type from @t_downtimes
    .....
всё выполняется практически мгновенно.
Сервер: Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (X64) Apr 2 2010 15:48:46 Copyright (c) Microsoft Corporation Enterprise Edition (64-bit) on Windows NT 6.0 <X64> (Build 6002: Service Pack 2) .
20 ноя 12, 15:32    [13501138]     Ответить | Цитировать Сообщить модератору
 Re: Inlined function c типом параметра user-defined table type (+)  [new]
Сон Веры Павловны
Member

Откуда:
Сообщений: 5975
И еще P.S.: в
declare
  @t_downtimes pdist.ut_line_dates;
insert into @t_downtimes
  select line_id, fd, td, type from pdist.uf_downtimes(@year_n) order by line_id, fd, td
select line_id, fd, td, type from pdist.uf_worktimes_x(@year_n, @t_downtimes);

всё вешается именно на выборке из pdist.uf_worktimes_x.
20 ноя 12, 15:34    [13501164]     Ответить | Цитировать Сообщить модератору
 Re: Inlined function c типом параметра user-defined table type (+)  [new]
pkarklin
Member

Откуда: Москва (Муром)
Сообщений: 74925
автор
Заглянул в блокировки - там примерно такая картина


Не хватает статуса (GRANTED, CONVERT, WAIT), чтоб понять, на чем висит.

Сообщение было отредактировано: 20 ноя 12, 15:41
20 ноя 12, 15:41    [13501230]     Ответить | Цитировать Сообщить модератору
 Re: Inlined function c типом параметра user-defined table type (+)  [new]
Сон Веры Павловны
Member

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

DBNameObjNameIndIdTypeModeStatus
tempdbsysallocunits1KEYXGRANT
tempdbsysschobjs2KEYXGRANT
tempdbsysschobjs3KEYXGRANT
tempdbsyscolpars2KEYXGRANT
tempdbsysidxstats1KEYXGRANT
tempdbsysidxstats2KEYXGRANT
db_logoTT_ut_line_dates_46B27FE20TABSch-SGRANT
db_logouf_worktimes_x0TABSch-SGRANT
db_logouf_worktimes_x0TABXGRANT

- содержимое выборки по блокировкам время от времени меняется (например, из нее пропадает блокировка по TT_ut_line_dates_хххх), но вот статус всегда и везде GRANT.
20 ноя 12, 15:49    [13501311]     Ответить | Цитировать Сообщить модератору
 Re: Inlined function c типом параметра user-defined table type (+)  [new]
Glory
Member

Откуда:
Сообщений: 104760
Сон Веры Павловны
Плана выполнения я тоже так и не дождался.

Даже предварительного ?
20 ноя 12, 15:50    [13501321]     Ответить | Цитировать Сообщить модератору
 Re: Inlined function c типом параметра user-defined table type (+)  [new]
Сон Веры Павловны
Member

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

да, даже предварительного - показывает кусочек плана из первой инструкции в батче - которая use db_logo - и повисает.
20 ноя 12, 15:52    [13501363]     Ответить | Цитировать Сообщить модератору
 Re: Inlined function c типом параметра user-defined table type (+)  [new]
invm
Member

Откуда: Москва
Сообщений: 9396
Пропатчить сервер для начала не пробовали? А то у вас RTM.
20 ноя 12, 15:59    [13501451]     Ответить | Цитировать Сообщить модератору
 Re: Inlined function c типом параметра user-defined table type (+)  [new]
Сон Веры Павловны
Member

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

это вне моей компетенции и возможностей. Разве что можно попробовать перенести данные и объекты на локальный сервер - он 10.50.4260.0 developer edition (правда, на x86), и проверить, повторится ли такое там.
20 ноя 12, 16:07    [13501555]     Ответить | Цитировать Сообщить модератору
 Re: Inlined function c типом параметра user-defined table type (+)  [new]
Glory
Member

Откуда:
Сообщений: 104760
Сон Веры Павловны
Glory,

да, даже предварительного - показывает кусочек плана из первой инструкции в батче - которая use db_logo - и повисает.

Если виснет составление плана, то никаких блокировок вы и не увидите. Кроме собственно текстов объектов. Потому что никакого выполнения не происходит
20 ноя 12, 16:11    [13501617]     Ответить | Цитировать Сообщить модератору
 Re: Inlined function c типом параметра user-defined table type (+)  [new]
Сон Веры Павловны
Member

Откуда:
Сообщений: 5975
Видимо, это действительно баг рабочего сервера (который 10.50.1600.1 и RTM). Вот такое:
create function pdist.uf_test(@data pdist.ut_line_dates readonly)
returns table as
return (
  with cte as (
    select row_number() over(partition by line_id order by fd, td) id,
    line_id, fd, td, type from @data
  )
  select id, line_id,fd, td, type from cte
)

declare
  @data pdist.ut_line_dates;
insert into @data
  select number%4, dateadd(hh, number,getdate()), dateadd(hh, number+5,getdate()), number%2
  from master..spt_values where type='P' and number between 0 and 16;
select * from pdist.uf_test(@data)

на нем себя ведет аналогично, а на локальном (который 10.50.4260.0) отрабатывает вполне нормально и мгновенно.
20 ноя 12, 16:17    [13501668]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить