Function with order by param using case when slow down the performance

1

Hi All,

I have the custom function for order by as param using case..when as below:-

Alter FUNCTION dbo.GetInternal(@pageNo int, @pageSize int,@orderby nvarchar(max))
RETURNS @results TABLE
(
--select * from dbo.GetInternal(1,50,'MarkMastRecNo')
-- columns returned by the function
MarkMastRecNo int NOT NULL,
IntDataProf_Ref nvarchar(max),
Import_Status nvarchar(max),
IntStatus_No int NULL,
AWFStatus nvarchar(max),
Import_Type nvarchar(max),
IntType_No int NULL,
IP_Address nvarchar(15),
Email nvarchar(max),
Company_No int null,
Organisation nvarchar(max),
Country nvarchar(max),
Country_No int null,
Date_Inserted DateTime,
DateTime_Captured DateTime,
LoginName nvarchar(max),
Saved_At DateTime,
Saved_By nvarchar(max),
Further_Information nvarchar(max),
SiteOfOriginID int null,
SiteOfOrigin nvarchar(max),
Physical_Address nvarchar(max),
MktgDocumentName nvarchar(max),
File_No nvarchar(max),
Download_Referring_Page nvarchar(max),
lastedit bit,
case_ID ntext
)
AS
-- body of the function
BEGIN
declare @offset int
set @offset = @pageSize * (@pageNo - 1)

INSERT @results
select MarkMastRecNo,

DataProfRef,
[ImportStatus]=c.Status,
StatusNo=a.StatusNo,
AWFStatus=a.AWFStatus,
ImportType=b.Type,
a.TypeNo,
a.IPAddress,
a.Email,
a.CompanyNo,
a.Organisation,
Country = d.Country,
CountryNo=d.CountryNo,
a.DateInserted,
a.DateCaptured,
a.LoginName,
a.SavedAt,
a.SavedBy,
a.FurtherInfo,
a.SiteID,
Site=s.Site,
a.Physical_Add,
a.DocumentName,
a.FileNo,
a.DownloadPage,
a.LastEdit,
a.caseID
from xxx a
left join LU_xxx1 b on a.TypeNo = b.TypeNo
left join LU_xxx2 c on a.StatusNo = c.StatusNo
left join LU_xxx3 d on a.CountryNo = d.CountryNo
left join LU_xxx4 s on a.SiteID = s.SiteID
order by
CASE @orderby when 'RecNo' then RecNo
when 'DataProfRef' then DataProfRef
when 'c.Status' then c.Status
when 'a.StatusNo' then a.StatusNo
when 'a.AWFStatus' then a.AWFStatus
when 'b.Type' then b.Type
when 'a.TypeNo' then a.TypeNo
when 'a.IPAddress' then a.IPAddress
when 'a.Email' then a.Email
when 'a.CompanyNo' then a.CompanyNo
when 'a.Organisation' then a.Organisation
when 'd.Country' then d.Country
when 'd.Country_No' then d.Country_No
when 'a.Date_Inserted' then a.Date_Inserted
when 'a.DateTime_Captured' then a.DateTime_Captured
when 'a.LoginName' then a.LoginName
when 'a.SavedAt' then a.SavedAt
when 'a.SavedBy' then a.SavedBy
when 'a.FurtherInfo then a.FurtherInfo
when 'a.SiteID' then a.SiteID
when 's.Site' then s.Site
when 'a.PhysicalAdd' then a.PhysicalAdd
when 'a.DocumentName' then a.DocumentName
when 'a.FileNo' then a.FileNo
when 'a.DownloadPage' then a.DownloadPage
when 'a.LastEdit' then a.LastEdit'
when 'a.CaseID' then a.CaseID
else RecNo
end
offset @offset rows fetch next @pageSize rows only option(recompile)

RETURN
END
GO

This function Execution Plan
Sequence Cost: 5%
Table Valued Function [GetInternal] Cost: 3 %
Table Scan [GetInternal] Cost: 92%
Execution Time: 00:00:05

If I hardcoded the order by static as below
:
:
order by MarkMastRecNo offset @offset rows fetch next @pageSize rows only option(recompile)
This function Execution Plan
Sequence Cost: 5%
Table Valued Function [GetInternal] Cost: 3 %
Table Scan [GetInternal] Cost: 92%
Execution Time: 00:00:00

May I know how to turn the performance using [ order by case @param when …. end ] as my 1st function statement?

Please advise.

Thanks.

Regards,
Micheale

2

How are you calling this function
Remember it will be called for each row

3

The big performance gain would be to convert this to an inline TVF rather than a multi-line TVF, like below. You can't specify RECOMPILE in the function that way, you'd need to put it in the calling query, the query using the function.

CREATE FUNCTION dbo.GetInternal
(@pageNo int, @pageSize int,@orderby nvarchar(max))
RETURNS TABLE
AS
RETURN (
SELECT MarkMastRecNo,    
DataProfRef,
[ImportStatus]=c.Status,
StatusNo=a.StatusNo,
AWFStatus=a.AWFStatus,
ImportType=b.Type,
a.TypeNo,
a.IPAddress,
a.Email,
a.CompanyNo,
a.Organisation,
Country = d.Country,
CountryNo=d.CountryNo,
a.DateInserted,
a.DateCaptured,
a.LoginName,
a.SavedAt,
a.SavedBy,
a.FurtherInfo,
a.SiteID,
Site=s.Site,
a.Physical_Add,
a.DocumentName,
a.FileNo,
a.DownloadPage,
a.LastEdit,
a.caseID
from xxx a
cross apply (
    select offset = @pageSize * (@pageNo - 1)
) as ca1
left join LU_xxx1 b on a.TypeNo = b.TypeNo
left join LU_xxx2 c on a.StatusNo = c.StatusNo
left join LU_xxx3 d on a.CountryNo = d.CountryNo
left join LU_xxx4 s on a.SiteID = s.SiteID
order by
CASE @orderby when 'RecNo' then RecNo
when 'DataProfRef' then DataProfRef
when 'c.Status' then c.Status
when 'a.StatusNo' then a.StatusNo
when 'a.AWFStatus' then a.AWFStatus
when 'b.Type' then b.Type
when 'a.TypeNo' then a.TypeNo
when 'a.IPAddress' then a.IPAddress
when 'a.Email' then a.Email
when 'a.CompanyNo' then a.CompanyNo
when 'a.Organisation' then a.Organisation
when 'd.Country' then d.Country
when 'd.Country_No' then d.Country_No
when 'a.Date_Inserted' then a.Date_Inserted
when 'a.DateTime_Captured' then a.DateTime_Captured
when 'a.LoginName' then a.LoginName
when 'a.SavedAt' then a.SavedAt
when 'a.SavedBy' then a.SavedBy
when 'a.FurtherInfo' then a.FurtherInfo
when 'a.SiteID' then a.SiteID
when 's.Site' then s.Site
when 'a.PhysicalAdd' then a.PhysicalAdd
when 'a.DocumentName' then a.DocumentName
when 'a.FileNo' then a.FileNo
when 'a.DownloadPage' then a.DownloadPage
when 'a.LastEdit' then a.LastEdit
when 'a.CaseID' then a.CaseID
else RecNo
end
offset [offset] rows fetch next @pageSize rows only 
/*option(recompile)*/
)
GO
4

Hi Sir,
Thanks. I changed the offset @pageSize *(@pageNo-1) . It's reduced the time to from 00:00:05 to 00:00:03 . It's doesn't help. offset [offset] <<- [offset] cannot be found as column name.

Regards,
MIcheale

5

Just specify the calc directly in the offset, you don't have to set a variable or alias name for it:

CREATE FUNCTION dbo.GetInternal
(@pageNo int, @pageSize int,@orderby nvarchar(max))
RETURNS TABLE
AS
RETURN (
SELECT MarkMastRecNo,
DataProfRef,
[ImportStatus]=c.Status,
StatusNo=a.StatusNo,
AWFStatus=a.AWFStatus,
ImportType=b.Type,
a.TypeNo,
a.IPAddress,
a.Email,
a.CompanyNo,
a.Organisation,
Country = d.Country,
CountryNo=d.CountryNo,
a.DateInserted,
a.DateCaptured,
a.LoginName,
a.SavedAt,
a.SavedBy,
a.FurtherInfo,
a.SiteID,
Site=s.Site,
a.Physical_Add,
a.DocumentName,
a.FileNo,
a.DownloadPage,
a.LastEdit,
a.caseID
from xxx a
left join LU_xxx1 b on a.TypeNo = b.TypeNo
left join LU_xxx2 c on a.StatusNo = c.StatusNo
left join LU_xxx3 d on a.CountryNo = d.CountryNo
left join LU_xxx4 s on a.SiteID = s.SiteID
order by
CASE @orderby when 'RecNo' then RecNo
when 'DataProfRef' then DataProfRef
when 'c.Status' then c.Status
when 'a.StatusNo' then a.StatusNo
when 'a.AWFStatus' then a.AWFStatus
when 'b.Type' then b.Type
when 'a.TypeNo' then a.TypeNo
when 'a.IPAddress' then a.IPAddress
when 'a.Email' then a.Email
when 'a.CompanyNo' then a.CompanyNo
when 'a.Organisation' then a.Organisation
when 'd.Country' then d.Country
when 'd.Country_No' then d.Country_No
when 'a.Date_Inserted' then a.Date_Inserted
when 'a.DateTime_Captured' then a.DateTime_Captured
when 'a.LoginName' then a.LoginName
when 'a.SavedAt' then a.SavedAt
when 'a.SavedBy' then a.SavedBy
when 'a.FurtherInfo' then a.FurtherInfo
when 'a.SiteID' then a.SiteID
when 's.Site' then s.Site
when 'a.PhysicalAdd' then a.PhysicalAdd
when 'a.DocumentName' then a.DocumentName
when 'a.FileNo' then a.FileNo
when 'a.DownloadPage' then a.DownloadPage
when 'a.LastEdit' then a.LastEdit
when 'a.CaseID' then a.CaseID
else RecNo
end
offset (@pageSize * (@pageNo - 1)) rows fetch next @pageSize rows only
/option(recompile)/
)

6

Does this need to be a function? You could write this as a dynamic stored proc even with the order by being dynamic.

7

Hi Sir,
Thanks. It's work fine.

Regards,
Micheale