October 16, 2013 at 12:19 am
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
October 16, 2013 at 12:22 am
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.
October 16, 2013 at 7:51 am
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/
October 16, 2013 at 8:28 am
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...
October 16, 2013 at 8:35 am
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/
October 16, 2013 at 8:39 am
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.
October 16, 2013 at 8:41 am
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/
October 16, 2013 at 10:33 am
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.
October 16, 2013 at 5:19 pm
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.
-- Itzik Ben-Gan 2001
October 17, 2013 at 12:48 am
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
October 17, 2013 at 1:15 am
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.
October 17, 2013 at 1:30 am
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.
October 17, 2013 at 1:35 am
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.
October 17, 2013 at 1:41 am
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
October 17, 2013 at 1:48 am
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