October 17, 2013 at 1:50 am
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.
October 17, 2013 at 1:54 am
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.
October 17, 2013 at 4:06 am
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.
October 17, 2013 at 4:16 am
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.
October 17, 2013 at 4:48 am
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
20131017WT_W003A60020131016WT_W003C2700HERE 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
You have in attachment above ... few response back...
posted at @ 8:54:27 AM, I thought that you work with this now.
October 17, 2013 at 4:56 am
Well, the word doc has the DDL but I am not taking the time to convert you Excel sheet to necessary format to load the tables.
You really need to read the first article I reference in my signature block. It walks you through the steps on what and how to post the information needed to get the best answers in return.
October 17, 2013 at 5:34 am
Lynn Pettis (10/17/2013)
Well, the word doc has the DDL but I am not taking the time to convert you Excel sheet to necessary format to load the tables.You really need to read the first article I reference in my signature block. It walks you through the steps on what and how to post the information needed to get the best answers in return.
I tried and with lot of column shows NULL on each rows 😀 lot of data... , but in excel there was problem in convert time, I fixed and now just you can try, next time i will do like you posted. I tried insert from excel and was ok.
October 17, 2013 at 6:11 am
tony28 (10/17/2013)
Lynn Pettis (10/17/2013)
Well, the word doc has the DDL but I am not taking the time to convert you Excel sheet to necessary format to load the tables.You really need to read the first article I reference in my signature block. It walks you through the steps on what and how to post the information needed to get the best answers in return.
I tried and with lot of column shows NULL on each rows 😀 lot of data... , but in excel there was problem in convert time, I fixed and now just you can try, next time i will do like you posted. I tried insert from excel and was ok.
Unfortunately I don't have time to develop and test an import process from Excel as I currently work 10+ hours a day 7 days a week as I am currently deployed in Afghanistan. The more you do up front to make helping you easier (think copy/paste/execute in SSMS) the more time we have to actually devote to working on your problem rather than trying to set everything up before we can help.
October 17, 2013 at 6:57 am
I understand, but I tried this. but result is null, i think that no more space for string...lot of columns..ok i will do few columns with 'A'
SELECT TOP 1000
'SELECT '
+ QUOTENAME([TR_ID],'''')+','
+ QUOTENAME([COMM_FLAG],'''')+','
+ QUOTENAME([DEVICE_ID],'''')+','
+ QUOTENAME([DATA_TYPE],'''')+','
+ QUOTENAME([SPOOL_POINT],'''')+','
+ QUOTENAME([PROD_DATE],'''')+','
+ QUOTENAME([STATION_ID],'''')+','
+ QUOTENAME([COMMIT_NO],'''')+','
+ QUOTENAME([BODY_NO],'''')+','
+ QUOTENAME([WO_SERIAL],'''')+','
+ QUOTENAME([WO_NATION],'''')+','
+ QUOTENAME([WO_DEALER],'''')+','
+ QUOTENAME([EXT_COLOR],'''')+','
+ QUOTENAME([INT_COLOR],'''')+','
+ QUOTENAME([FSC_YEAR],'''')+','
+ QUOTENAME([FSC_NATION],'''')+','
+ QUOTENAME([FSC_DEALER],'''')+','
+ QUOTENAME([FSC_MCODE],'''')+','
+ QUOTENAME([FSC_BODY],'''')+','
+ QUOTENAME([FSC_ENGCAP],'''')+','
+ QUOTENAME([FSC_ENGTYPE],'''')+','
+ QUOTENAME([FSC_FUELTYPE],'''')+','
+ QUOTENAME([FSC_TMTYPE],'''')+','
+ QUOTENAME([FSC_SPCAR],'''')+','
+ QUOTENAME([FSC_OPCNO],'''')+','
+ QUOTENAME([FSC_VER],'''')+','
+ QUOTENAME([FSC_EXTCOR],'''')+','
+ QUOTENAME([FSC_INTCOR],'''')+','
+ QUOTENAME([FSC_TDATA],'''')+','
+ QUOTENAME([REGION_ID],'''')+','
+ QUOTENAME([MODEL_NAME],'''')+','
+ QUOTENAME([BODY_TYPE],'''')+','
+ QUOTENAME([DRIVE_TYPE],'''')+','
+ QUOTENAME([TRIM_LEVEL],'''')+','
+ QUOTENAME([PACK_MONTH],'''')+','
+ QUOTENAME([PROD_NATION],'''')+','
+ QUOTENAME([TECH_SPEC],'''')+','
+ QUOTENAME([OPT_219],'''')+','
+ QUOTENAME([PART_UNIQUE],'''')+','
+ QUOTENAME([PART_COLOR],'''')+','
+ QUOTENAME([MCS_PART],'''')+','
+ QUOTENAME([DEST_CODE],'''')+','
+ QUOTENAME([PORT_CODE],'''')+','
+ QUOTENAME([REPORT_DTIME],'''')+','
+ QUOTENAME([CREATE_TIME],'''')+','
+ QUOTENAME([COMM_MODE],'''')+','
+ QUOTENAME([WO_FLAG],'''')+','
+ QUOTENAME([MASTER_FLAG],'''')+','
+ QUOTENAME([ERP_FLAG],'''')
+ ' UNION ALL'
FROM [MCS_MESDB].[dbo].[TB_RCV_ALCDATA]
October 17, 2013 at 10:38 pm
hello, in attachment is all.
The function is on the first page of topic and the one of procedure is page back .
Thx fo reply and advices
Viewing 10 posts - 16 through 24 (of 24 total)
You must be logged in to reply to this topic. Login to reply