December 9, 2008 at 9:41 am
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [dbo].[ConvertGMTToEST](@DateTime DateTime)
-- converts the GMT time to EST time based on the siebel S_TIMEZONE and S_TIMEZONE_TMP tables.
-- returns the datetime in EST
returns datetime
as
BEGIN
RETURN DATEADD(MINUTE, ISNULL((SELECT TOP 1 TT.UTC_OFFSET
FROM S_TIMEZONE T JOIN S_TIMEZONE_TMP TT ON T.ROW_ID = TT.TIMEZONE_ID
WHERE T.STD_ABBREV = 'Eastern Standard Time' AND @DateTime = TT.START_DT), 0), @DateTime)
END
December 9, 2008 at 9:49 am
How long does it take to run once?
How many times is it running in the procedure which brought it to your attention as being a potential bottleneck?
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
December 9, 2008 at 10:53 am
Do you have an execution plan showing how the function is behaving?
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
December 9, 2008 at 11:27 am
This query:
SELECT ROW_ID, CREATED, dbo.ConvertGMTToEST(CREATED)
FROM S_SRV_REQ S
returns 959,970 rows in 48 seconds
This query:
SELECT ROW_ID, CREATED
FROM S_SRV_REQ S
returns 959,970 rows in 4 seconds
Want to know how to speed up the function. Rewite it better? Different code for the function, etc.
December 9, 2008 at 11:31 am
Yes I do have an execution plan. Please see attached zipped file that includes the execution plan
Thank you everyone. I appreciate this
December 9, 2008 at 11:39 am
Not surprising. The function is basically running a query for each and every row in your database.
A better approach would be to JOIN to the lookup table, either directly or through a sub-select. This is untested because I don't have your structure.
SELECT s.ROW_ID, DATEADD(MINUTE,ISNULL(x.UTC_OFFSET,s.Created))
FROM S_SRV_REQ S
LEFT JOIN (SELECT TOP 1 tt.UTC_OFFSET, TT.START_DT
FROM S_TIMEZONE T
JOIN S_TIMEZONE_TEMP TT
ON T.ROW_ID = TT.TIMEZONE_ID
WHERE T.STD_ABBREV = 'Eastern Standard Time'
ORDER BY tt.UTC_OFFSET DESC) AS x
ON s.Created = x.START_DT
But once you go this route, you'll need to look at indexing because you've got several JOINs and WHERE clauses in there that might cause you difficulty.
Also, since you're not putting a where clause on the basic query, you're pretty much forced to scan each table, meaning, read every single row whether you really need them or not.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
December 9, 2008 at 11:43 am
pino_daddy (12/9/2008)
Want to know how to speed up the function. Rewite it better? Different code for the function, etc.
Remove it?
The way that SQL runs UDFs means that the function will be getting run once for each row in the outer result set. So you're running that function almost a million times and doing almost a million seeks of the S_TIMEZONE and S_TIMEZONE_TMP tables (assuming there are indexes, otherwise a million scans)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 9, 2008 at 11:49 am
pino_daddy (12/9/2008)
This query:SELECT ROW_ID, CREATED, dbo.ConvertGMTToEST(CREATED)
FROM S_SRV_REQ S
returns 959,970 rows in 48 seconds
This query:
SELECT ROW_ID, CREATED
FROM S_SRV_REQ S
returns 959,970 rows in 4 seconds
Want to know how to speed up the function. Rewite it better? Different code for the function, etc.
The function gets a time offset in minutes and applies it to the date returned by GETDATE(). Your query would run much faster if you were to obtain the time offset (in minutes) from the function first using DATEDIFF and GETDATE(), then apply it using DATEADD() in the SELECT.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
December 9, 2008 at 11:49 am
First things first, I think your statistics may be out of date. on [SWUS].[dbo].[SW_Ord_List_Ext].[_dta_index_SW_Ord_List_Ext_23_254064091__K3_1_4_5_18_20_22] [OLE]. The estimated number of rows is 54, the actual is 48000. That's a bit of disparity there.
You're getting a full scan on [SWUS].[dbo].[SW_Booking_Detail].[_dta_index_SW_Booking_Detail_23_1450592356__K2_1_3_4_5_6_7_9_10_11_12_13_14_15_16_17_18_19] [BDTL]. It looks like you're calculating two values and then joining on them based on the probes in use in the hash join up there. These two events alone are causing most of the estimated cost. You can't perform calculations on columns and then use them expecting an index to work with them. You need to do the calculations elsewhere. Same issues on [SWUS].[dbo].[SW_Booking_Detail_Seats].[PK_SW_Booking_Detail_Seats] [BDS].
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
December 9, 2008 at 12:56 pm
This query was a simple example of how we would use the function.
We are aquiring companies around teh world and for UTC we need to have one time zone. Many queries will have this function now embedded in them to generate the reports they already generate, only the time with be UTC based of EST. I cannot change the times in the tables, nor can I stiore them elsewhere since there are many tables with time. The function is used to convert whatever time is being retrieved from whatever table it is being retrieved from. I was just wondering if there was a better way to write this function.
Thank you everyone.
Looking for a rewrite of this function if anyone feels it can be written better
December 9, 2008 at 12:58 pm
The error message returned from executing the SELECT above with the DATEADD function is:
Msg 174, Level 15, State 1, Line 1
The dateadd function requires 3 argument(s).
Msg 156, Level 15, State 1, Line 8
Incorrect syntax near the keyword 'ORDER'.
December 9, 2008 at 1:12 pm
Grant did say it was untested. There's a misplaced bracket in the dateadd function.
SELECT s.ROW_ID, DATEADD(MINUTE,ISNULL(x.UTC_OFFSET,0),s.Created)
FROM S_SRV_REQ S
LEFT JOIN (SELECT TOP 1 tt.UTC_OFFSET, TT.START_DT
FROM S_TIMEZONE T
JOIN S_TIMEZONE_TEMP TT
ON T.ROW_ID = TT.TIMEZONE_ID
WHERE T.STD_ABBREV = 'Eastern Standard Time'
ORDER BY tt.UTC_OFFSET DESC) AS x
ON s.Created = x.START_DT
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 9, 2008 at 1:16 pm
Sorry for the confusion. Thanks for the clarification Gail.
It's still the approach I'd take, but I'd sure take the time to examine those indexes.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
December 9, 2008 at 1:23 pm
pino_daddy (12/9/2008)
The function is used to convert whatever time is being retrieved from whatever table it is being retrieved from. I was just wondering if there was a better way to write this function.
Maybe some index tweaks would help. Make sure that the joins in the function are indexed.
The problem is that user-defined functions that do data access are slow because of the way they're run - once per row. There's no getting around that with the function.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 10, 2008 at 2:56 am
pino_daddy (12/9/2008)
This query was a simple example of how we would use the function.We are aquiring companies around teh world and for UTC we need to have one time zone. Many queries will have this function now embedded in them to generate the reports they already generate, only the time with be UTC based of EST. I cannot change the times in the tables, nor can I stiore them elsewhere since there are many tables with time. The function is used to convert whatever time is being retrieved from whatever table it is being retrieved from. I was just wondering if there was a better way to write this function.
Thank you everyone.
Looking for a rewrite of this function if anyone feels it can be written better
Since the target time zone is hard-coded into the function, the query in there is always going to return the same offset. In fact, as written, I don't think it returns anything other than the date passed in:
ALTER FUNCTION [dbo].[ConvertGMTToEST](@DateTime DateTime)
-- converts the GMT time to EST time based on the siebel S_TIMEZONE and S_TIMEZONE_TMP tables.
-- returns the datetime in EST
returns datetime
as
BEGIN
RETURN DATEADD(MINUTE, ISNULL((SELECT TOP 1 TT.UTC_OFFSET
FROM S_TIMEZONE T JOIN S_TIMEZONE_TMP TT ON T.ROW_ID = TT.TIMEZONE_ID
WHERE T.STD_ABBREV = 'Eastern Standard Time' AND @DateTime = TT.START_DT), 0), @DateTime)
AND @DateTime = TT.START_DT
How likely is it that S_TIMEZONE_TMP is going to contain the passed-in datetime?
Since the function is supposed to return the datetime value adjusted to Eastern Standard Time, why not identify the offset and change the function so that it returns DATEADD(mm, offset, @DateTime)? You don't need to drag it out of the tables each time - although you might need to account for daylight saving time somewhere.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply