SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Function vs Direct query


Function vs Direct query

Author
Message
tony28
tony28
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1648 Visits: 889
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

ALTER   FUNCTION [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_TIME DATETIME
DECLARE @GET_DATE DATETIME

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_DATE VARCHAR(10)
DECLARE @WORK_DATE_PRE VARCHAR(10)
DECLARE @WORK_TIME_ID VARCHAR(10)
DECLARE @SHIFT_ID VARCHAR(5)
DECLARE @PLAN_STOP_SEC INT
DECLARE @PRE_WORK_DATE VARCHAR(10)
DECLARE @PRE_WORK_TIME_ID VARCHAR(10)
DECLARE @PRE_SHIFT_ID VARCHAR(5)
DECLARE @PRE_PLAN_STOP_SEC INT
DECLARE @TIME_SEQ INT
DECLARE @TIME_DAY INT

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



How to post data/code on a forum to get the best help: Option 1 / Option 2
tony28
tony28
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1648 Visits: 889
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.




How to post data/code on a forum to get the best help: Option 1 / Option 2
Sean Lange
Sean Lange
SSC Guru
SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)

Group: General Forum Members
Points: 63478 Visits: 17966
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/

_______________________________________________________________

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.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
tony28
tony28
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1648 Visits: 889
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/


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



How to post data/code on a forum to get the best help: Option 1 / Option 2
Sean Lange
Sean Lange
SSC Guru
SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)

Group: General Forum Members
Points: 63478 Visits: 17966
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/


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.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
tony28
tony28
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1648 Visits: 889
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.



How to post data/code on a forum to get the best help: Option 1 / Option 2
Sean Lange
Sean Lange
SSC Guru
SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)

Group: General Forum Members
Points: 63478 Visits: 17966
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.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Lynn Pettis
Lynn Pettis
SSC Guru
SSC Guru (96K reputation)SSC Guru (96K reputation)SSC Guru (96K reputation)SSC Guru (96K reputation)SSC Guru (96K reputation)SSC Guru (96K reputation)SSC Guru (96K reputation)SSC Guru (96K reputation)

Group: General Forum Members
Points: 96255 Visits: 38981
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.

Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Alan.B
Alan.B
SSChampion
SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)

Group: General Forum Members
Points: 13585 Visits: 8005
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)
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.

-- Alan Burstein



Best practices for getting help on SQLServerCentral
Need to split a string? Try DelimitedSplit8K or DelimitedSplit8K_LEAD (SQL 2012+)
Need a pattern-based splitter? Try PatternSplitCM
Need to remove or replace those unwanted characters? Try PatExclude8K and PatReplace8K.

"I can't 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. " -- Itzek Ben-Gan 2001
tony28
tony28
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1648 Visits: 889
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



How to post data/code on a forum to get the best help: Option 1 / Option 2
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search