Ошибка времени ожидания запроса скалярной функции в SQL Server при использовании в хранимой процедуре

Question

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

ALTER FUNCTION [dbo].[FNGETMULIPLEASSIGNESS_NEW2]
(
  @TIMELINEID INT,
  @MILSTONEID INT,
  @TASKID INT
)

RETURNS varchar(max)
AS
  BEGIN
        DECLARE @Assignees varchar(max)='', @isExists bit=0
   if(@TASKID=0)
        BEGIN
        Select  @Assignees = @Assignees+ FIRSTNAME +' ' + LASTNAME+', '  
            FROM CASETIMELINEPEOPLE 
            INNER JOIN USERDETAIL  ON 
            CASETIMELINEPEOPLE.PEOPLEUSERID=USERDETAIL.USERID
            WHERE (CASETIMELINEID= @TIMELINEID) AND 
            (TEMPLATEMILESTONEID=@MILSTONEID) AND 
            (TEMPLATETASKID is null) and CASETIMELINEPEOPLE.isdeleted=0
    END

  else

        BEGIN
            Select  @Assignees = @Assignees+ FIRSTNAME +' ' + LASTNAME+','  
            FROM CASETIMELINEPEOPLE 
            INNER JOIN USERDETAIL  ON 
            CASETIMELINEPEOPLE.PEOPLEUSERID=USERDETAIL.USERID
            WHERE (CASETIMELINEID= @TIMELINEID) AND 
            (TEMPLATEMILESTONEID=@MILSTONEID) AND 
            (TEMPLATETASKID=@TASKID) and CASETIMELINEPEOPLE.isdeleted=0

       END

        SELECT @Assignees=SUBSTRING(@Assignees, 0,LEN(@Assignees))  
        RETURN @Assignees
END

Answer 1

Использование встроенной табличной функции повысит производительность.

Ссылка:

• Когда функция SQL не является функцией? "Если он не встроенный, то это мусор". - Роб Фарли
• Встроенные скалярные функции - Ицик Бен-Ган
• Скалярные функции, вставка и производительность: занимательное название для скучного поста - Адам Мачаник
• Пользовательские функции TSQL: десять вопросов, которые вы стеснялись задавать - Роберт Шелдон

---

Вот версия встроенной табличной функции вашей скалярной функции, которая используетstuff() сselect ... for xml path ('') метод конкатенации строк.:

create function dbo.fn_get_multiple_assigness_itvf (
    @timelineid int
  , @milstoneid int
  , @taskid int
) returns table as return (
select Assignees = stuff((
    select ',' + firstname + ' ' + lastname
    from casetimelinepeople ctp
      inner join userdetail ud
        on ctp.peopleuserid=ud.userid
    where casetimelineid = @timelineid
      and templatemilestoneid = @milstoneid
      and (templatetaskid = @taskid 
        or (@taskid = 0 and templatetaskid is null)
        )
      and ctp.isdeleted=0
    for xml path (''), type).value('.','nvarchar(max)')
  ,1,1,'')
)
go

демонстрационный ролик: http://rextester.com/UZTJS46485

Испытательная установка:

create table casetimelinepeople (
    casetimelineid int
  , peopleuserid int
  , templatemilestoneid int
  , templatetaskid int
  , isdeleted bit not null default 0
);
insert into casetimelinepeople values
 (1,1,1,null,0)
,(1,2,1,null,0)
,(1,3,1,null,0)
,(1,2,1,1,0)
,(1,3,1,1,0)

create table userdetail (
    userid int not null
  , firstname varchar(32) not null
  , lastname varchar(32) not null);
insert into userdetail values
 (1, 'Some', 'One')
,(2, 'Avinash', 'Raikwar')
,(3, 'Sql','Zim');
go

И запрос встроенной табличной функции следующим образом:

select * 
from dbo.fn_get_multiple_assigness_itvf(1,1,0) 

возвращается

+----------------------------------+
|            Assignees             |
+----------------------------------+
| Some One,Avinash Raikwar,Sql Zim |
+----------------------------------+

---

select * 
from dbo.fn_get_multiple_assigness_itvf(1,1,1)

возвращает:

+-------------------------+
|        Assignees        |
+-------------------------+
| Avinash Raikwar,Sql Zim |
+-------------------------+

---

С помощьюcross apply() чтобы вызвать функцию для каждой строки в запросе:

select *
from casetimelinepeople ctp
  cross apply dbo.fn_get_multiple_assigness_itvf(
      ctp.casetimelineid
    , ctp.templatemilestoneid
    , ctp.templatetaskid
    ) x

возвращает:

+----------------+--------------+---------------------+----------------+-----------+----------------------------------+
| casetimelineid | peopleuserid | templatemilestoneid | templatetaskid | isdeleted |            Assignees             |
+----------------+--------------+---------------------+----------------+-----------+----------------------------------+
|              1 |            1 |                   1 | NULL           | False     | Some One,Avinash Raikwar,Sql Zim |
|              1 |            2 |                   1 | NULL           | False     | Some One,Avinash Raikwar,Sql Zim |
|              1 |            3 |                   1 | NULL           | False     | Some One,Avinash Raikwar,Sql Zim |
|              1 |            2 |                   1 | 1              | False     | Avinash Raikwar,Sql Zim          |
|              1 |            3 |                   1 | 1              | False     | Avinash Raikwar,Sql Zim          |
+----------------+--------------+---------------------+----------------+-----------+----------------------------------+