HELLO ALL,

I have a table values function

which used to return TAT days of a files ..

so , function call ..

SELECT * FROM dbo.Fun_File_Stage_Holding_Day_iTVF(File_id,Stage_id,is_quarter,is_only_holding_days)

SELECT * FROM dbo.Fun_File_Stage_Holding_Day_iTVF(66695,745,0,1)

statisctics ..

(1 row(s) affected)
Table '#A56C71BE'. Scan count 1, logical reads 1, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(1 row(s) affected)

Plan 

It is doing table scan .. how can replace it to seek or indexed one?

also when i use this function in procedure which return 3678 files after filter by cross apply join

i got statistics like ..

(3678 row(s) affected)
Table '#A56C71BE'. Scan count 3516, logical reads 3678, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Workfile'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'FILE_STAGE_LOG'. Scan count 1, logical reads 235, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'FILE_MASTER'. Scan count 1, logical reads 80, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(1 row(s) affected)

i guess the #A56C71BE is by that functions result .. and the query takes 47 sec. to execute when use this function 

after commenting function join statement and its output in select i got 3678 rows in 1 sec.

Please help ..

Could you provide the code for the function? Without that how someone would be able to make any suggestion? Also providing the DDL for the table wou

Thanks satheesh , please see the function code 

ALTER FUNCTION [dbo].[Fun_File_Stage_Holding_Day_iTVF] 
(@File_ID INT,
@Stage_ID INT,
@Quater_ID INT,
@Is_Only_Holding_Day BIT=0)
RETURNS @Temptable TABLE (TAT_Days INT)
AS
BEGIN


-- Declare the return variable here
DECLARE @Group_Id INT, @Target_Days INT,@int_Side_Track_Day INT,@int_Not_Working_Day INT
,@Stage_Ids NVARCHAR(MAX),@S_LogDate DATETIME,@E_LogDate DATETIME
, @File_Holding_Days INT
SELECT @File_Holding_Days=0,@Stage_Ids=''

SELECT @Group_Id=GROUP_ID, @Target_Days=HOLDING_DAY 
FROM dbo.FILE_STAGE_HOLDING_DAY_SETTING WITH (NOLOCK) WHERE STAGE_ID=@Stage_ID

IF(@Target_Days>0)
BEGIN
IF(@Quater_ID > 0)
BEGIN
DECLARE @tbl_Quater_Date TABLE (FROM_DATE DATETIME,TO_DATE DATETIME,STAGE_ID INT)

INSERT INTO @tbl_Quater_Date( FROM_DATE ,TO_DATE,STAGE_ID)
SELECT FROM_DATE,TO_DATE,STAGE_ID
FROM dbo.Split(@Quater_ID,',') AS tbl_Quater_Id
INNER JOIN dbo.FILE_STAGE_LOG WITH (NOLOCK) ON tbl_Quater_Id.Data=QUATER_ID AND FILE_ID=@File_ID AND ISNULL(IS_BACK_STAGE,'False')='False'

SELECT @S_LogDate=FROM_DATE FROM @tbl_Quater_Date WHERE STAGE_ID=@Stage_ID
IF(EXISTS(SELECT FROM_DATE FROM @tbl_Quater_Date WHERE TO_DATE IS NULL AND STAGE_ID=@Stage_ID))
SELECT @E_LogDate=GETDATE()
ELSE
SELECT @E_LogDate=TO_DATE FROM @tbl_Quater_Date WHERE STAGE_ID=@Stage_ID
END
ELSE
BEGIN
IF(ISNULL(@Group_Id,0) >0)
SELECT @Stage_Ids = COALESCE(@Stage_Ids+ ', ','') + CONVERT(NVARCHAR(10),STAGE_ID) FROM  dbo.FILE_STAGE_HOLDING_DAY_SETTING WHERE GROUP_ID=@Group_Id
ELSE
SET @Stage_Ids=@Stage_ID
DECLARE @tbl_Date TABLE(FROM_DATE DATETIME,TO_DATE DATETIME)

INSERT INTO @tbl_Date( FROM_DATE ,TO_DATE)
SELECT FROM_DATE,TO_DATE
FROM dbo.Split(@Stage_Ids,',') AS tbl_Stage_Id
INNER JOIN dbo.FILE_STAGE_LOG WITH (NOLOCK) ON tbl_Stage_Id.Data=STAGE_ID AND FILE_ID=@File_ID AND ISNULL(IS_BACK_STAGE,'False')='False'

SELECT @S_LogDate=MAX(FROM_DATE) FROM @tbl_Date
IF(EXISTS(SELECT FROM_DATE FROM @tbl_Date WHERE TO_DATE IS NULL))
SELECT @E_LogDate=GETDATE()
ELSE
SELECT @E_LogDate=MAX(TO_DATE) FROM @tbl_Date
    END

SELECT @int_Side_Track_Day=ISNULL(SUM(DATEDIFF(DAY,CONVERT(DATE,CREATED_DATE) ,CONVERT(DATE,REVISED_DATE))),0)
FROM dbo.Split(@Stage_Ids,',') AS tbl_Stage_Id
INNER JOIN dbo.LOAN_SIDE_TRACK WITH (NOLOCK) ON tbl_Stage_Id.Data=dbo.LOAN_SIDE_TRACK.STAGE_ID AND dbo.LOAN_SIDE_TRACK.FILE_ID=@File_ID

SELECT @int_Not_Working_Day=ISNULL(COUNT(DATE_VALUE),0)  FROM WORKING_DAY_MASTER WITH (NOLOCK)  WHERE IS_WORKING=0 AND DATE_VALUE BETWEEN @S_LogDate AND @E_LogDate

SET @File_Holding_Days = DATEDIFF(DAY,@S_LogDate,@E_LogDate)-(@int_Side_Track_Day- (@int_Side_Track_Day/7)+ @int_Not_Working_Day)
-- Return the result of the function+
END

DECLARE @Str_Result NVARCHAR(50)
IF(@Is_Only_Holding_Day=0)
SET @Str_Result=  CONVERT(VARCHAR(50),@Target_Days) + ',' +  CONVERT(VARCHAR(50),@File_Holding_Days )
ELSE
SET @Str_Result=CONVERT(VARCHAR(50),@Target_Days -@File_Holding_Days)

INSERT INTO @Temptable( TAT_Days )
SELECT ISNULL(@Str_Result,0);

RETURN;

END

Trace your function using Sql server profiler and then run database tuning advisor  with the trace file that you have received using Sql profiler .The tuning advisor will suggest the indexes that needed to be created.

Regards,

how to trace function using profiler ?

Dear Dilip,

After reviewing your function I would suggest use cross Apply where you are using function dbo.Split(@Stage_Ids,',') AS tbl_Stage_Id

I hope this might helpful to you.

Thanks Rajat ,

I will make changes and if it affects i will get back to you.

Hi

I would re-write that UDF as a stored procedure.. You will definitely gain in terms of performance..

One of some downsizes of Multi-Statement Table-Valued UDFs  is estimation prediction (which is very bad) affects a query plan execution,

Dear Uri Demant ,

If i replace function with procedure , how would i join and call the procedure in my main store procedure ??

Dear Rajat ,

cant use cross apply to split(@stage_id,',') Because then it returns me all log related to that stage_id of a file.

whole  story is my function only , please advice me how can i replace it with another object in db.

currently , functioning like ..

select -------

------------

------------

,tbl_TAT_Days.TAT_Days  AS HOLDING_DAYS
,CASE WHEN 
tbl_TAT_Days.TAT_Days>=0 THEN '1' ELSE '2' END AS TAT_Status
,(CASE WHEN tbl_TAT_Days.TAT_Days<0 THEN
CASE WHEN (tbl_TAT_Days.TAT_Days*(-1))<=15 THEN '< 15 Days'  
WHEN (tbl_TAT_Days.TAT_Days*(-1)) BETWEEN 16 AND 30 THEN '16-30 Days'
WHEN (tbl_TAT_Days.TAT_Days*(-1)) BETWEEN 31 AND 60 THEN '31-60 Days'    
WHEN (tbl_TAT_Days.TAT_Days*(-1)) BETWEEN 61 AND 120 THEN '61-120 Days'
WHEN (tbl_TAT_Days.TAT_Days*(-1)) BETWEEN 121 AND 240 THEN '121-240 Days' 
--WHEN (tbl_TAT_Days.TAT_Days*(-1))>240 THEN '>240 Days'    
ELSE '>240 Days' 

END 

END) AS Overdue_Category

from  some tables joins ...

---

--

CROSS APPLY [dbo].[Fun_File_Stage_Holding_Day_iTVF] (dbo.FILE_MASTER.FILE_ID,dbo.FILE_STAGE_LOG.STAGE_ID,0,1) AS tbl_TAT_Days 

I wish to retrieve all files TAT days in one shot and then can join by fileid to get the TAT of file .

Please help .