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 Thursday, October 17, 2013 1:15 AM


SSC-Insane

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

Group: General Forum Members
Last Login: Today @ 8:50 PM
Points: 23,003, Visits: 31,495
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.




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 #1505501
Posted Thursday, October 17, 2013 1:30 AM


SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Wednesday, May 28, 2014 3:17 AM
Points: 163, Visits: 483
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 #1505505
Posted Thursday, October 17, 2013 1:35 AM


SSC-Insane

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

Group: General Forum Members
Last Login: Today @ 8:50 PM
Points: 23,003, Visits: 31,495
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

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 #1505509
Posted Thursday, October 17, 2013 1:41 AM


SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Wednesday, May 28, 2014 3:17 AM
Points: 163, Visits: 483
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
Post #1505511
Posted Thursday, October 17, 2013 1:48 AM


SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Wednesday, May 28, 2014 3:17 AM
Points: 163, Visits: 483
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
20131017	WT_W003	A	600	20131016	WT_W003	C	2700

HERE IS RESULT exec UP_PDP_PRODUCTION for this time, it is still changed according to data..
CPF01	216	0	  0.0	0220A6W 052123  	F	2013-10-17 09:46:34.663
CPM01 216 213 98.6 0220A6W 052123 F 2013-10-17 09:46:34.663
ENG01 216 200 92.6 0220A6W 052123 F 2013-10-17 09:46:34.663
FAX01 216 199 92.1 0220A6W 052123 F 2013-10-17 09:46:34.663
FAX02 216 200 92.6 0220A6W 052123 F 2013-10-17 09:46:34.663
FCM01 216 196 90.7 0220A6W 052123 T 2013-10-17 09:46:34.663
FEF01 216 0 0.0 0220A6W 052123 F 2013-10-17 09:46:34.663
FEM01 216 207 95.8 0220A6W 052123 F 2013-10-17 09:46:34.663
RCM01 216 207 95.8 0220A6W 052123 F 2013-10-17 09:46:34.663
SUS01 216 0 0.0 0220A6W 052123 F 2013-10-17 09:46:34.663

Post #1505514
Posted Thursday, October 17, 2013 1:50 AM


SSC-Insane

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

Group: General Forum Members
Last Login: Today @ 8:50 PM
Points: 23,003, Visits: 31,495
tony28 (10/17/2013)
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



The more procedures that use this function, the more important it is to make sure it is as efficient as possible.
I would also stay away from using the NOLOCK hint as it could introduce errors into the numerous procedures that use the function.



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 #1505515
Posted Thursday, October 17, 2013 1:54 AM


SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Wednesday, May 28, 2014 3:17 AM
Points: 163, Visits: 483
Lynn Pettis (10/17/2013)
tony28 (10/17/2013)
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



The more procedures that use this function, the more important it is to make sure it is as efficient as possible.
I would also stay away from using the NOLOCK hint as it could introduce errors into the numerous procedures that use the function.


inside procedures isnt NOLOCK i just tried this, for sure that problem isnt in lock, but it is same.
Post #1505517
Posted Thursday, October 17, 2013 4:05 AM


SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Wednesday, May 28, 2014 3:17 AM
Points: 163, Visits: 483
I did it.... but you can see... the no name column will be with cte , but you can see that performance is not good. maybe something is wrong

SELECT   CONVERT(VARCHAR(8), DATEADD(DAY, -START_TIME_DAY, (CONVERT(VARCHAR(11), GETDATE(), 120) +   CONVERT(VARCHAR(8), GETDATE(), 108))), 112) 
,A.WORK_TIME_ID
,A.SHIFT_ID
--,CASE WHEN IS_WORK = 'N' THEN DATEDIFF(SECOND, CAST(START_TIME AS DATETIME) + START_TIME_DAY, CONVERT(DATETIME, CONVERT(VARCHAR(8), CONVERT(VARCHAR(8), GETDATE(), 108), 114)) + TIME_DAY ) ELSE 0 END
,(SELECT (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')
,B.WORK_DATE
,B.WORK_TIME_ID
,B.SHIFT_ID
,CASE WHEN IS_WORK = 'N' THEN DATEDIFF(SECOND, CAST(START_TIME AS DATETIME) + START_TIME_DAY, CONVERT(DATETIME, CONVERT(VARCHAR(8), CONVERT(VARCHAR(8), GETDATE(), 108), 114)) + TIME_DAY ) ELSE 0 END

FROM TB_CODE_WORK_TIMEDTL A
CROSS APPLY
(SELECT TOP 1 WORK_DATE, SHIFT_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 AND CONVERT(VARCHAR(8), DATEADD(DAY, -START_TIME_DAY, (CONVERT(VARCHAR(11), GETDATE(), 120) + CONVERT(VARCHAR(8), GETDATE(), 108))), 112)
GROUP BY WC.WORK_DATE, WTD.WORK_TIME_ID, WTD.SHIFT_ID
) A
WHERE A.WORK_DT < CONVERT(VARCHAR(8), DATEADD(DAY, -START_TIME_DAY, (CONVERT(VARCHAR(11), GETDATE(), 120) + CONVERT(VARCHAR(8), GETDATE(), 108))), 112) + RIGHT('000' + CONVERT(VARCHAR, TIME_SEQ),3)
ORDER BY A.WORK_DT DESC ) B
WHERE A.WORK_TIME_ID = (SELECT WORK_TIME_ID FROM TB_WORK_CALENDAR WHERE WORK_DATE =CONVERT(VARCHAR(8), DATEADD(DAY, -START_TIME_DAY, (CONVERT(VARCHAR(11), GETDATE(), 120) + CONVERT(VARCHAR(8), GETDATE(), 108))), 112))
AND CONVERT(VARCHAR(8), GETDATE(), 108) >= CAST(START_TIME AS DATETIME) + START_TIME_DAY
AND CONVERT(VARCHAR(8), GETDATE(), 108) < CAST(END_TIME AS DATETIME) + END_TIME_DAY


(1 row(s) affected)
Table '#03B3C554'. 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 = 21 ms.

(1 row(s) affected)
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 'TB_WORK_CALENDAR'. Scan count 5, logical reads 19, 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 3, logical reads 6, 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 = 78 ms, elapsed time = 137 ms.



Post #1505569
Posted Thursday, October 17, 2013 4:16 AM


SSC-Insane

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

Group: General Forum Members
Last Login: Today @ 8:50 PM
Points: 23,003, Visits: 31,495
Please let me know when you decide to post the DDL (CREATE TABLE) statements, sample data (INSERT INTO) for the tables, and expected results based on given inputs to the function and the sample data. Until then, there really isn't much I can do to really help you rewrite the function. I am a visual type of person. I need to see the starting point and the ending point so I make the necessary connections between the two and come up with a viable solution.




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 #1505574
Posted Thursday, October 17, 2013 4:48 AM


SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Wednesday, May 28, 2014 3:17 AM
Points: 163, Visits: 483
tony28 (10/17/2013)
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
20131017	WT_W003	A	600	20131016	WT_W003	C	2700

HERE IS RESULT exec UP_PDP_PRODUCTION for this time, it is still changed according to data..
CPF01	216	0	  0.0	0220A6W 052123  	F	2013-10-17 09:46:34.663
CPM01 216 213 98.6 0220A6W 052123 F 2013-10-17 09:46:34.663
ENG01 216 200 92.6 0220A6W 052123 F 2013-10-17 09:46:34.663
FAX01 216 199 92.1 0220A6W 052123 F 2013-10-17 09:46:34.663
FAX02 216 200 92.6 0220A6W 052123 F 2013-10-17 09:46:34.663
FCM01 216 196 90.7 0220A6W 052123 T 2013-10-17 09:46:34.663
FEF01 216 0 0.0 0220A6W 052123 F 2013-10-17 09:46:34.663
FEM01 216 207 95.8 0220A6W 052123 F 2013-10-17 09:46:34.663
RCM01 216 207 95.8 0220A6W 052123 F 2013-10-17 09:46:34.663
SUS01 216 0 0.0 0220A6W 052123 F 2013-10-17 09:46:34.663




You have in attachment above ... few response back...

posted at @ 8:54:27 AM, I thought that you work with this now.
Post #1505589
« Prev Topic | Next Topic »

Add to briefcase ««123»»

Permissions Expand / Collapse