locked
OPTIMIZE INLINE TABLE VALUE FUNCTION RRS feed

  • Question

  • 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 


    Dilip Patil..

    Tuesday, July 7, 2015 7:59 AM

Answers

All replies

  • 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 ..


    Dilip Patil..

    Tuesday, July 7, 2015 8:04 AM
  • 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 would be good

    Satheesh
    My Blog | How to ask questions in technical forum


    Tuesday, July 7, 2015 8:09 AM
    Answerer
  • 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


    Dilip Patil..

    Tuesday, July 7, 2015 8:49 AM
  • 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,


    Milan Das

    Tuesday, July 7, 2015 9:04 AM
  • how to trace function using profiler ?


    Dilip Patil..

    Tuesday, July 7, 2015 9:18 AM
  • 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.


    Rajat Jaiswal

    Tuesday, July 7, 2015 12:16 PM
  • Thanks Rajat ,

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


    Dilip Patil..

    Tuesday, July 7, 2015 12:53 PM
  • 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,


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Tuesday, July 7, 2015 1:06 PM
    Answerer
  • Dear Uri Demant ,

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


    Dilip Patil..

    Wednesday, July 8, 2015 5:49 AM
  • 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 .


    Dilip Patil..

    Wednesday, July 8, 2015 5:55 AM