Function vs Direct query

  • Hello,

    I have problem with comparing function vs direct query

    there is different about 15-30ms and this function I have to join with another table in select in more procedures.

    I updated statistics and rebuild index from includes tables, I tried use in select OPTION recompile, added primary key, which is in ON clausule but it is still same.

    UPDATE STATISTICS TB_CODE_WORK_TIMEDTL

    UPDATE STATISTICS TB_CODE_WORK_TIME

    UPDATE STATISTICS TB_WORK_CALENDAR

    UPDATE STATISTICS TB_RCV_ALCDATA

    First optimazed was from CPU time = 85 ms, elapsed time = 85 ms. to CPU time = 16 ms, elapsed time = 26 ms.

    with added WORK_DATE to WHERE clausule, but still I dont understand why I have this one, If I selected one row,

    If I start direct I have in all queries time 0ms

    ALTERFUNCTION [dbo].[FN_TABLE_GET_WORK_DATE](

    )

    RETURNS @tbl table ( WORK_DATE VARCHAR(10), WORK_TIME_ID VARCHAR(10), SHIFT_ID VARCHAR(5), PLAN_STOP_SEC INT

    , PRE_WORK_DATE VARCHAR(10), PRE_WORK_TIME_ID VARCHAR(10), PRE_SHIFT_ID VARCHAR(5), PRE_PLAN_STOP_SEC INT, primary key ( WORK_DATE,WORK_TIME_ID ))

    as

    BEGIN

    --TIME TOTAL SEARCH

    DECLARE @GET_TIMEDATETIME

    DECLARE @GET_DATEDATETIME

    SET @GET_TIME = CONVERT(VARCHAR(8), GETDATE(), 108)

    SET @GET_DATE = CONVERT(VARCHAR(11), GETDATE(), 120) + @GET_TIME

    --SET @GET_TIME = '05:50'

    --SET @GET_DATE = '2011-05-30 ' + @GET_TIME

    DECLARE @WORK_DATEVARCHAR(10)

    DECLARE @WORK_DATE_PREVARCHAR(10)

    DECLARE @WORK_TIME_IDVARCHAR(10)

    DECLARE @SHIFT_IDVARCHAR(5)

    DECLARE @PLAN_STOP_SECINT

    DECLARE @PRE_WORK_DATEVARCHAR(10)

    DECLARE @PRE_WORK_TIME_IDVARCHAR(10)

    DECLARE @PRE_SHIFT_IDVARCHAR(5)

    DECLARE @PRE_PLAN_STOP_SECINT

    DECLARE @TIME_SEQINT

    DECLARE @TIME_DAYINT

    SELECT TOP 1 @GET_TIME = CASE WHEN @GET_TIME < END_TIME THEN DATEADD(DAY, 1, @GET_TIME) ELSE @GET_TIME END

    FROM TB_CODE_WORK_TIMEDTL WHERE TIME_DAY > 0 ORDER BY TIME_SEQ DESC

    --WORK TIME SEARCH

    SELECT @WORK_DATE = CONVERT(VARCHAR(8), DATEADD(DAY, -START_TIME_DAY, @GET_DATE), 112) ,@WORK_TIME_ID = WORK_TIME_ID, @SHIFT_ID = SHIFT_ID, @TIME_SEQ = TIME_SEQ

    , @PLAN_STOP_SEC = CASE WHEN IS_WORK = 'N' THEN DATEDIFF(SECOND, CAST(START_TIME AS DATETIME) + START_TIME_DAY, CONVERT(DATETIME, CONVERT(VARCHAR(8), @GET_TIME, 114)) + TIME_DAY ) ELSE 0 END

    FROM TB_CODE_WORK_TIMEDTL WHERE WORK_TIME_ID = (SELECT WORK_TIME_ID FROM TB_WORK_CALENDAR WHERE WORK_DATE =CONVERT(VARCHAR(8), DATEADD(DAY, -START_TIME_DAY, @GET_DATE), 112))

    AND @GET_TIME >= CAST(START_TIME AS DATETIME) + START_TIME_DAY

    AND @GET_TIME < CAST(END_TIME AS DATETIME) + END_TIME_DAY

    SET @WORK_DATE_PRE=(SELECT TOP 1 PROD_DATE FROM TB_RCV_ALCDATA WHERE PROD_DATE<@WORK_DATE ORDER BY TR_ID desc)

    --SELECT @WORK_DATE, @SHIFT_ID, @TIME_SEQ, @WORK_DATE + RIGHT('000' + CONVERT(VARCHAR, @TIME_SEQ),3)

    -- PRE WORK TIME SEARCH

    SELECT TOP 1 @PRE_WORK_DATE = WORK_DATE, @PRE_SHIFT_ID = SHIFT_ID, @PRE_WORK_TIME_ID = A.WORK_TIME_ID FROM (

    SELECT WC.WORK_DATE + RIGHT('000' + CONVERT(VARCHAR, MAX(WTD.TIME_SEQ)),3) "WORK_DT", WC.WORK_DATE, WTD.WORK_TIME_ID, WTD.SHIFT_ID

    FROM TB_WORK_CALENDAR WC LEFT JOIN TB_CODE_WORK_TIMEDTL WTD

    ON WC.WORK_TIME_ID = WTD.WORK_TIME_ID

    WHERE WORK_DATE BETWEEN @WORK_DATE_PRE AND @WORK_DATE

    GROUP BY WC.WORK_DATE, WTD.WORK_TIME_ID, WTD.SHIFT_ID

    ) A

    WHERE A.WORK_DT < @WORK_DATE + RIGHT('000' + CONVERT(VARCHAR, @TIME_SEQ),3)

    ORDER BY A.WORK_DT DESC

    SELECT @PLAN_STOP_SEC = @PLAN_STOP_SEC + (ISNULL(SUM(TIME_MINUTE),0) * 60)

    FROM TB_CODE_WORK_TIMEDTL WHERE WORK_TIME_ID = @WORK_TIME_ID AND SHIFT_ID = @SHIFT_ID AND TIME_SEQ < @TIME_SEQ AND IS_WORK = 'N'

    SELECT @PRE_PLAN_STOP_SEC = ISNULL(SUM(TIME_MINUTE),0) * 60

    FROM TB_CODE_WORK_TIMEDTL WHERE WORK_TIME_ID = @PRE_WORK_TIME_ID AND SHIFT_ID = @PRE_SHIFT_ID AND IS_WORK = 'N'

    INSERT INTO @tbl

    SELECT @WORK_DATE, @WORK_TIME_ID, @SHIFT_ID, @PLAN_STOP_SEC, @PRE_WORK_DATE, @PRE_WORK_TIME_ID, @PRE_SHIFT_ID, @PRE_PLAN_STOP_SEC

    RETURN;

    END

    Here is result of statistics io and time with direct query

    SQL Server parse and compile time:

    CPU time = 0 ms, elapsed time = 0 ms.

    SQL Server Execution Times:

    CPU time = 0 ms, elapsed time = 0 ms.

    SQL Server Execution Times:

    CPU time = 0 ms, elapsed time = 0 ms.

    Table 'TB_CODE_WORK_TIMEDTL'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

    SQL Server Execution Times:

    CPU time = 0 ms, elapsed time = 0 ms.

    Table 'TB_WORK_CALENDAR'. Scan count 4, logical reads 8, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

    Table 'TB_CODE_WORK_TIMEDTL'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

    SQL Server Execution Times:

    CPU time = 0 ms, elapsed time = 0 ms.

    Table 'TB_RCV_ALCDATA'. Scan count 1, logical reads 89, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

    SQL Server Execution Times:

    CPU time = 0 ms, elapsed time = 1 ms.

    Table 'TB_CODE_WORK_TIMEDTL'. Scan count 2, logical reads 4, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

    Table 'TB_WORK_CALENDAR'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

    SQL Server Execution Times:

    CPU time = 0 ms, elapsed time = 0 ms.

    Table 'TB_CODE_WORK_TIMEDTL'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

    SQL Server Execution Times:

    CPU time = 0 ms, elapsed time = 0 ms.

    Table 'TB_CODE_WORK_TIMEDTL'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

    SQL Server Execution Times:

    CPU time = 0 ms, elapsed time = 0 ms.

    (1 row(s) affected)

    SQL Server Execution Times:

    CPU time = 0 ms, elapsed time = 0 ms.

    Here is result with function

    select * from FN_TABLE_GET_WORK_DATE() OPTION (RECOMPILE)

    SQL Server parse and compile time:

    CPU time = 0 ms, elapsed time = 0 ms.

    (1 row(s) affected)

    Table '#025493D5'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

    SQL Server Execution Times:

    CPU time = 16 ms, elapsed time = 23 ms.

    Anybody know where can be problem ? Thx lot for response

  • I founded that if I start the select twice, the second select is almost always the good time, but sometimes is same or worst,

    It is possible that can be problem in created temporary table?? and deleted ?? But in direct query is also and there isnt problem.

    SELECT WORK_DATE FROM FN_TABLE_GET_WORK_DATE()

    SELECT WORK_DATE FROM FN_TABLE_GET_WORK_DATE()

    SQL Server parse and compile time:

    CPU time = 0 ms, elapsed time = 0 ms.

    (1 row(s) affected)

    Table '#01014840'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

    SQL Server Execution Times:

    CPU time = 31 ms, elapsed time = 28 ms.

    (1 row(s) affected)

    Table '#03DDB4EB'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

    SQL Server Execution Times:

    CPU time = 0 ms, elapsed time = 3 ms.

  • What you have there is called a multi statement table valued function (MTVF). The performance of these can be a killer. Check out this post from Wayne Sheffield on the topic.

    http://www.sqlservercentral.com/blogs/discussionofsqlserver/2012/02/15/comparing-inline-and-multistatement-table-valued-functions/[/url]

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • Sean Lange (10/16/2013)


    What you have there is called a multi statement table valued function (MTVF). The performance of these can be a killer. Check out this post from Wayne Sheffield on the topic.

    http://www.sqlservercentral.com/blogs/discussionofsqlserver/2012/02/15/comparing-inline-and-multistatement-table-valued-functions/[/url]

    Ok thank you , I will check, and is it possible that the second same started query will be like direct query? 0ms...

  • tony28 (10/16/2013)


    Sean Lange (10/16/2013)


    What you have there is called a multi statement table valued function (MTVF). The performance of these can be a killer. Check out this post from Wayne Sheffield on the topic.

    http://www.sqlservercentral.com/blogs/discussionofsqlserver/2012/02/15/comparing-inline-and-multistatement-table-valued-functions/[/url]

    Ok thank you , I will check, and is it possible that the second same started query will be like direct query? 0ms...

    Do you mean if you convert to an iTVF? Not quite sure what you are asking here.

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • tony28 (10/16/2013)


    I founded that if I start the select twice, the second select is almost always the good time, but sometimes is same or worst,

    It is possible that can be problem in created temporary table?? and deleted ?? But in direct query is also and there isnt problem.

    SELECT WORK_DATE FROM FN_TABLE_GET_WORK_DATE()

    SELECT WORK_DATE FROM FN_TABLE_GET_WORK_DATE()

    SQL Server parse and compile time:

    CPU time = 0 ms, elapsed time = 0 ms.

    (1 row(s) affected)

    Table '#01014840'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

    SQL Server Execution Times:

    CPU time = 31 ms, elapsed time = 28 ms.

    (1 row(s) affected)

    Table '#03DDB4EB'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

    SQL Server Execution Times:

    CPU time = 0 ms, elapsed time = 3 ms.

    this one, if I started both in same time, you can see that the first has CPU time = 31 ms, elapsed time = 28 ms. and second CPU time = 0 ms, elapsed time = 3 ms., sometimes it was more on second query, but not like first.. I dont understand this issue.

  • tony28 (10/16/2013)


    tony28 (10/16/2013)


    I founded that if I start the select twice, the second select is almost always the good time, but sometimes is same or worst,

    It is possible that can be problem in created temporary table?? and deleted ?? But in direct query is also and there isnt problem.

    SELECT WORK_DATE FROM FN_TABLE_GET_WORK_DATE()

    SELECT WORK_DATE FROM FN_TABLE_GET_WORK_DATE()

    SQL Server parse and compile time:

    CPU time = 0 ms, elapsed time = 0 ms.

    (1 row(s) affected)

    Table '#01014840'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

    SQL Server Execution Times:

    CPU time = 31 ms, elapsed time = 28 ms.

    (1 row(s) affected)

    Table '#03DDB4EB'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

    SQL Server Execution Times:

    CPU time = 0 ms, elapsed time = 3 ms.

    this one, if I started both in same time, you can see that the first has CPU time = 31 ms, elapsed time = 28 ms. and second CPU time = 0 ms, elapsed time = 3 ms., sometimes it was more on second query, but not like first.. I dont understand this issue.

    This is because the function you posted is a MTVF. Performance of those is all over the place.

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • I was looking at your function and I am sure it can be improved, unfortunately with all the conversions between datetime and character formats and the lack of perspective (we can't see the structure of the table(s) queried nor the data stored).

    Using proper data types will definitely help the performance of your routine, and it could probably be rewritten as an iTVF (inline table valued function).

    If you would like to see this, please post the DDL for the table(s) involved and some sample data. Please read the first article I reference in my signature block regarding asking for help. It will walk you through the steps on what you need to post and how to post it to get the best possible answers in return.

  • Though impossible to demonstrate without any DDL for the underlying tables it certainly does look like you can convert your function into a Inline Table Valued function (as has been mentioned a few times already). Your function is only hitting three tables (TB_WORK_CALENDAR, TB_CODE_WORK_TIMEDTL, TB_RCV_ALCDATA) and your variables are pulling their values from those tables. You should be able to lose those variables and get this into a single sql statement (perhaps using a CTE) which can be used for an iTVF.

    You may also want to take a look at this article: How to Make Scalar UDFs Run Faster (SQL Spackle)[/url]

    Note: this is about Scalar Valued Functions but I included it because there is an example of ad-hoc SQL out-performing a function along with a good explanation of why.

    "I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

    -- Itzik Ben-Gan 2001

  • Before than i will give the design and data,

    I forgot for I think important issue, If this function is using for example inside 20procedures, it can causes this problem? For example lock or something ?

    Because I created new one and without relation in another procedures and this is 2ms... BUT like I wrote above, if I will started this function two times in same time, the second will be 2ms, but first 25ms.

    I will try on the break time delete execution plan.

    txh for reply

  • tony28 (10/17/2013)


    Before than i will give the design and data,

    I forgot for I think important issue, If this function is using for example inside 20procedures, it can causes this problem? For example lock or something ?

    Because I created new one and without relation in another procedures and this is 2ms... BUT like I wrote above, if I will started this function two times in same time, the second will be 2ms, but first 25ms.

    I will try on the break time delete execution plan.

    txh for reply

    What you are experiencing with the first run being slower than the second is caused be caching. The first run, the data has to pulled from disk to memory. the second time the data is already there.

    What we are telling you is that the function you have written can be improved. You just have to provide us with the DDL (CREATE TABLE) statements for the tables used by the function, sample data (as INSERT INTO statements) for those tables, and the expected results based on given inputs to the function and the given sample data.

  • I thinking about modify to ITVF, but i think it will be very hard and slowlier like one query...

    1. I have to declare variable

    2. alone queries are very faster, but together like function is problem,, but it has sometimes different behaviour like i wrote above.

    3. I tried replace without variable and it looks very bad with repeate selects. for example select for work_date,work_date_pre and etc.

  • tony28 (10/17/2013)


    I thinking about modify to ITVF, but i think it will be very hard and slowlier like one query...

    1. I have to declare variable

    2. alone queries are very faster, but together like function is problem,, but it has sometimes different behaviour like i wrote above.

    3. I tried replace without variable and it looks very bad with repeate selects. for example select for work_date,work_date_pre and etc.

    Post the DDL for the tables, sample data for the tables, and expected results based on given input and sample data. Your function can be rewritten as an itvf and it will perform better than the function you currently have.

  • Lynn Pettis (10/17/2013)


    tony28 (10/17/2013)


    Before than i will give the design and data,

    I forgot for I think important issue, If this function is using for example inside 20procedures, it can causes this problem? For example lock or something ?

    Because I created new one and without relation in another procedures and this is 2ms... BUT like I wrote above, if I will started this function two times in same time, the second will be 2ms, but first 25ms.

    I will try on the break time delete execution plan.

    txh for reply

    What you are experiencing with the first run being slower than the second is caused be caching. The first run, the data has to pulled from disk to memory. the second time the data is already there.

    What we are telling you is that the function you have written can be improved. You just have to provide us with the DDL (CREATE TABLE) statements for the tables used by the function, sample data (as INSERT INTO statements) for those tables, and the expected results based on given inputs to the function and the given sample data.

    Yes i know about it, that if i will delete ex.plan, I have to check second run, but I think if I do like this

    select * from FN_TABLE_GET_WORK_DATE() NOLOCK

    select * from FN_TABLE_GET_WORK_DATE() NOLOCK

    Ok I will do DDL. But I think that problem can be, that this function use about 20procedures

  • here is one of procedure, which is slowlier thanks to function...

    in the attachment you have in word all ddl from this, and in excel files for all tables 1000rows.

    ALTER PROCEDURE [dbo].[UP_PDP_PRODUCTION]

    AS

    BEGIN

    DECLARE @LAST_ORDER AS VARCHAR(100)

    SELECT TOP 1 @LAST_ORDER = COMMIT_NO +''+''+''+''+''+BODY_NO FROM TB_RCV_ALCDATA (NOLOCK)WHERE DATA_TYPE = 'SN' ORDER BY TR_ID DESC

    SELECT DS.LINE_CODE

    --DS.ORDER_QTY //--2012-03-08

    , ( DS.OPERATION_SEC - DS.PLAN_STOP_SEC ) / 60 AS NEW_TARGET

    , DS.PROD_QTY

    --, STR( CASE WHEN DS.ORDER_QTY = 0 THEN 0 ELSE CONVERT(DECIMAL, DS.PROD_QTY) / DS.ORDER_QTY * 100 END, 5,1) "PROD_RATE"

    , STR( ISNULL((CONVERT(DECIMAL, DS.PROD_QTY) / (NULLIF((DS.OPERATION_SEC - DS.PLAN_STOP_SEC), 0) / 60) * 100), 0), 5, 1) "PROD_RATE"

    , @LAST_ORDER "LAST_ORDER"

    , ISNULL(LT.LINE_ALARM,'F') "LINE_ALARM"

    ,GETDATE() "GETDATE"

    FROM TB_DAILY_SUMMARY DS (NOLOCK)

    JOIN FN_TABLE_GET_WORK_DATE() FW

    ON DS.WORK_DATE = FW.WORK_DATE AND DS.SHIFT_ID = FW.SHIFT_ID

    LEFT JOIN TB_LINE_TRACKING LT (NOLOCK)

    ON DS.LINE_CODE = LT.LINE_CODE

    ORDER BY LINE_CODE

    SET NOCOUNT OFF

    END

    HERE IS RESULT OF select * from FN_TABLE_GET_WORK_DATE() for today

    20131017WT_W003A60020131016WT_W003C2700

    HERE IS RESULT exec UP_PDP_PRODUCTION for this time, it is still changed according to data..

    CPF012160 0.00220A6W 052123 F2013-10-17 09:46:34.663

    CPM01216213 98.60220A6W 052123 F2013-10-17 09:46:34.663

    ENG01216200 92.60220A6W 052123 F2013-10-17 09:46:34.663

    FAX01216199 92.10220A6W 052123 F2013-10-17 09:46:34.663

    FAX02216200 92.60220A6W 052123 F2013-10-17 09:46:34.663

    FCM01216196 90.70220A6W 052123 T2013-10-17 09:46:34.663

    FEF012160 0.00220A6W 052123 F2013-10-17 09:46:34.663

    FEM01216207 95.80220A6W 052123 F2013-10-17 09:46:34.663

    RCM01216207 95.80220A6W 052123 F2013-10-17 09:46:34.663

    SUS012160 0.00220A6W 052123 F2013-10-17 09:46:34.663

Viewing 15 posts - 1 through 15 (of 24 total)

You must be logged in to reply to this topic. Login to reply