Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 123»»»

Function vs Direct query Expand / Collapse
Author
Message
Posted Wednesday, October 16, 2013 12:18 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Yesterday @ 12:45 AM
Points: 162, Visits: 451
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
Post #1505074
Posted Wednesday, October 16, 2013 12:22 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Yesterday @ 12:45 AM
Points: 162, Visits: 451
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.
Post #1505076
Posted Wednesday, October 16, 2013 7:51 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 2:05 PM
Points: 11,927, Visits: 10,967
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 Moden's 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)
Post #1505208
Posted Wednesday, October 16, 2013 8:28 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Yesterday @ 12:45 AM
Points: 162, Visits: 451
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...
Post #1505251
Posted Wednesday, October 16, 2013 8:35 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 2:05 PM
Points: 11,927, Visits: 10,967
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 Moden's 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)
Post #1505265
Posted Wednesday, October 16, 2013 8:39 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Yesterday @ 12:45 AM
Points: 162, Visits: 451
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.
Post #1505271
Posted Wednesday, October 16, 2013 8:41 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 2:05 PM
Points: 11,927, Visits: 10,967
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 Moden's 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)
Post #1505274
Posted Wednesday, October 16, 2013 10:33 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 9:47 PM
Points: 22,472, Visits: 30,138
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.



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)
Post #1505330
Posted Wednesday, October 16, 2013 5:19 PM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Yesterday @ 2:03 PM
Points: 500, Visits: 2,290
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



Read this article for best practices on asking questions.
Need to split a string? Try this (Jeff Moden)
Need a pattern-based string spitter? Try this (Dwain Camps)
My blog
Post #1505448
Posted Thursday, October 17, 2013 12:48 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Yesterday @ 12:45 AM
Points: 162, Visits: 451
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
Post #1505496
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse