Please start any new threads on our new site at http://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 Database Design and Application Architecture
 Performance: which is better?
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

acroes
Starting Member

Netherlands
2 Posts

Posted - 02/03/2015 :  08:17:30  Show Profile  Reply with Quote
I have a sql function, in which i declare a temptable as follows:

CREATE FUNCTION [dbo].[fnFormatDate](
@MyDate DATETIME ,
@Format NVARCHAR(50) )RETURNS NVARCHAR(255)
AS
BEGIN

DECLARE @RetStr NVARCHAR(255)
DECLARE @tMonth TABLE(MonthID INT, MonthStr NVARCHAR(20), LANGUAGE INT)
DECLARE @tWeekDay TABLE(WeekDayID INT, WeekDayStr NVARCHAR(20), LANGUAGE INT)
DECLARE @Year INT DECLARE @Month INT DECLARE @WeekDay INT DECLARE @Day INT
DECLARE @Hour INT DECLARE @Minute INT DECLARE @Second INT DECLARE @MS INT
DECLARE @Quarter INT

INSERT INTO @tWeekDay VALUES(1, 'maandag', 1)
INSERT INTO @tWeekDay VALUES(2, 'dinsdag', 1)
INSERT INTO @tWeekDay VALUES(3, 'woensdag', 1)
INSERT INTO @tWeekDay VALUES(4, 'donderdag', 1)
INSERT INTO @tWeekDay VALUES(5, 'vrijdag', 1)
INSERT INTO @tWeekDay VALUES(6, 'zaterdag', 1)
INSERT INTO @tWeekDay VALUES(7, 'zondag', 1)
INSERT INTO @tMonth VALUES(1, 'januari', 1)
INSERT INTO @tMonth VALUES(2, 'februari', 1)
INSERT INTO @tMonth VALUES(3, 'maart', 1)
INSERT INTO @tMonth VALUES(4, 'april', 1)
INSERT INTO @tMonth VALUES(5, 'mei', 1)
INSERT INTO @tMonth VALUES(6, 'juni', 1)
INSERT INTO @tMonth VALUES(7, 'juli', 1)
INSERT INTO @tMonth VALUES(8, 'augustus', 1)
INSERT INTO @tMonth VALUES(9, 'september', 1)
INSERT INTO @tMonth VALUES(10, 'oktober', 1)
INSERT INTO @tMonth VALUES(11, 'november', 1)
INSERT INTO @tMonth VALUES(12, 'december', 1)

SET @RetStr = UPPER(@Format)
SET @Year = DATEPART(year, @MyDate)
SET @Month = DATEPART(month, @MyDate)
SET @WeekDay = DATEPART(weekday, @MyDate)
SET @Day = DATEPART(day, @MyDate)
SET @Hour = DATEPART(hour, @MyDate)
SET @Minute = DATEPART(minute, @MyDate)
SET @Second = DATEPART(second, @MyDate)
SET @MS = DATEPART(millisecond, @MyDate)
SET @Quarter = DATEPART(quarter, @MyDate)

SELECT @RetStr = REPLACE(@RetStr, 'YYYY' COLLATE Latin1_General_CS_AS, CONVERT(NVARCHAR(4), @Year))
SELECT @RetStr = REPLACE(@RetStr, 'YY' COLLATE Latin1_General_CS_AS, RIGHT(CONVERT(NVARCHAR(4), @Year), 2))
SELECT @RetStr = REPLACE(@RetStr, ' MS' COLLATE Latin1_General_CS_AS, ' ' + CONVERT(NVARCHAR(5), @MS))
SELECT @RetStr = REPLACE(@RetStr, ':MS' COLLATE Latin1_General_CS_AS, ':' + CONVERT(NVARCHAR(5), @MS))
SELECT @RetStr = REPLACE(@RetStr, '.MS' COLLATE Latin1_General_CS_AS, '.' + CONVERT(NVARCHAR(5), @MS))
SELECT @RetStr = REPLACE(@RetStr, 'MS' COLLATE Latin1_General_CS_AS, CONVERT(NVARCHAR(5), @MS))
SELECT @RetStr = REPLACE(@RetStr, ' M ' COLLATE Latin1_General_CS_AS, ' ' + CONVERT(NVARCHAR(2), @Month) + ' ')
SELECT @RetStr = REPLACE(@RetStr, ' M,' COLLATE Latin1_General_CS_AS, ' ' + CONVERT(NVARCHAR(2), @Month) + ',')
SELECT @RetStr = REPLACE(@RetStr, '-M-' COLLATE Latin1_General_CS_AS, '-' + CONVERT(NVARCHAR(2), @Month) + '-')
SELECT @RetStr = REPLACE(@RetStr, ' D ' COLLATE Latin1_General_CS_AS, ' ' + CONVERT(NVARCHAR(2), @Day) + ' ')
SELECT @RetStr = REPLACE(@RetStr, ' D,' COLLATE Latin1_General_CS_AS, ' ' + CONVERT(NVARCHAR(2), @Day) + ',')
SELECT @RetStr = REPLACE(@RetStr, '-D-' COLLATE Latin1_General_CS_AS, '-' + CONVERT(NVARCHAR(2), @Day) + '-')
SELECT @RetStr = REPLACE(@RetStr, ':N:' COLLATE Latin1_General_CS_AS, ':' + CONVERT(NVARCHAR(2), @Minute) + ':')
SELECT @RetStr = REPLACE(@RetStr, ':S ' COLLATE Latin1_General_CS_AS, ':' + CONVERT(NVARCHAR(2), @Second) + ' ')
SELECT @RetStr = REPLACE(@RetStr, 'NN' COLLATE Latin1_General_CS_AS, RIGHT('0' + CONVERT(NVARCHAR(2), @Minute), 2))
SELECT @RetStr = REPLACE(@RetStr, 'SS' COLLATE Latin1_General_CS_AS, RIGHT('0' + CONVERT(NVARCHAR(2), @Second), 2))
SELECT @RetStr = REPLACE(@RetStr, ':S' COLLATE Latin1_General_CS_AS, ':' + CONVERT(NVARCHAR(2), @Second))
SELECT @RetStr = REPLACE(@RetStr, 'MMMM' COLLATE Latin1_General_CS_AS, m.MonthStr) FROM @tMonth AS m WHERE m.MonthID = @Month
SELECT @RetStr = REPLACE(@RetStr, 'MMM' COLLATE Latin1_General_CS_AS, LEFT(m.MonthStr, 3)) FROM @tMonth AS m WHERE m.MonthID = @Month
SELECT @RetStr = REPLACE(@RetStr, 'DDDD' COLLATE Latin1_General_CS_AS, w.WeekDayStr) FROM @tWeekDay AS w WHERE w.WeekDayID = @WeekDay
SELECT @RetStr = REPLACE(@RetStr, 'DDD' COLLATE Latin1_General_CS_AS, LEFT(w.WeekDayStr, 2)) FROM @tWeekDay AS w WHERE w.WeekDayID = @WeekDay
SELECT @RetStr = REPLACE(@RetStr, 'MM' COLLATE Latin1_General_CS_AS, RIGHT('0' + CONVERT(NVARCHAR(2), @Month), 2))
SELECT @RetStr = REPLACE(@RetStr, 'M-' COLLATE Latin1_General_CS_AS, CONVERT(NVARCHAR(2), @Month) + '-')
SELECT @RetStr = REPLACE(@RetStr, 'DD' COLLATE Latin1_General_CS_AS, RIGHT('0' + CONVERT(NVARCHAR(2), @Day), 2))
SELECT @RetStr = REPLACE(@RetStr, 'D' COLLATE Latin1_General_CS_AS, CONVERT(NVARCHAR(2), @Day))
SELECT @RetStr = REPLACE(@RetStr, 'Q' COLLATE Latin1_General_CS_AS, CONVERT(NVARCHAR, @Quarter))
select @Retstr = replace(@retstr, '(', '<nobr>')
select @retstr = replace(@retstr, ')', '</nobr>')
select @retstr = replace(@retstr, '&', ' ')

IF CHARINDEX('AMPM', @RetStr) > 0 BEGIN
IF @Hour < 12 BEGIN
SELECT @RetStr = REPLACE(@RetStr, 'HH' COLLATE Latin1_General_CS_AS, RIGHT('0' + CONVERT(NVARCHAR(2), @Hour), 2))
SELECT @RetStr = REPLACE(@RetStr, 'H' COLLATE Latin1_General_CS_AS, CONVERT(NVARCHAR(2), @Hour))
SELECT @RetStr = REPLACE(@RetStr, 'AMPM' COLLATE Latin1_General_CS_AS, 'AM')
END
ELSE BEGIN
SELECT @RetStr = REPLACE(@RetStr, 'HH' COLLATE Latin1_General_CS_AS, RIGHT('0' + CONVERT(NVARCHAR(2), @Hour - 12), 2))
SELECT @RetStr = REPLACE(@RetStr, 'H' COLLATE Latin1_General_CS_AS, CONVERT(NVARCHAR(2), @Hour - 12))
SELECT @RetStr = REPLACE(@RetStr, 'AMPM' COLLATE Latin1_General_CS_AS, 'PM')
END
END
ELSE BEGIN
SELECT @RetStr = REPLACE(@RetStr, 'HH' COLLATE Latin1_General_CS_AS, RIGHT('0' + CONVERT(NVARCHAR(2), @Hour), 2))
SELECT @RetStr = REPLACE(@RetStr, 'H' COLLATE Latin1_General_CS_AS, CONVERT(NVARCHAR(2), @Hour))
END
RETURN @RetStr
END
GO

This does exactly what i want it to do. But recently i discouverd this:

create FUNCTION udf_DayOfWeek(@dtDate DATETIME)
RETURNS VARCHAR(9)
AS
BEGIN
DECLARE @rtDayofWeek VARCHAR(9)
DECLARE @weekDay int
set @weekDay = ((DatePart(dw,GETDATE())+@@DATEFIRST-7)%7)

set @rtDayofWeek = CASE @weekDay
WHEN 1 THEN 'zondag'
WHEN 2 THEN 'maandag'
WHEN 3 THEN 'dinsdag'
WHEN 4 THEN 'woensdag'
WHEN 5 THEN 'donderdag'
WHEN 6 THEN 'vrijdag'
WHEN 7 THEN 'zaterdag'
END
RETURN (@rtDayofWeek)
END
GO

With this i can extract the temp table from the first function, change the underlying code so it calls the udf_DayOfWeek function. Doing that, the function does still what i want.

So far, so good. Now for the performance. I have tested both with the extimated Execution plan, which shows no real diffence's. But I this feeling, that can't be right. I think that including the second function in the first function should be faster or at least less demanding about IO or CPU.

Can any of you guys inform me on this?
Or even if i am really on the wrong track here, let me know.

Regards

gbritton
Flowing Fount of Yak Knowledge

2780 Posts

Posted - 02/03/2015 :  09:58:52  Show Profile  Reply with Quote
You may not be able to see from the execution plan what SQL is doing with the functions, since they are not in-line TVFs. You could test it by building sample data in increasing orders of magnitude to see how it performs with 10, 100, 1000, 1000000 rows etc. I would expect that the first function may result in longer times since you have set-logic in there whereas the second function merely manipulates scalar values.
Go to Top of Page

acroes
Starting Member

Netherlands
2 Posts

Posted - 02/03/2015 :  10:53:07  Show Profile  Reply with Quote
I have tried that, with small recordsets. I see no real differences. But as the resultset getting bigger, so are the time-differences.
I stopped with the resultset of 29k rows, and the second option is indeed faster, by far. So i'm going to implement the udf_dayOfweek in the fnformatdate function.
Thanks for spending time and effort :)
Go to Top of Page

lomew01
Starting Member

Canada
4 Posts

Posted - 02/21/2015 :  02:24:53  Show Profile  Reply with Quote
Yeah i think difference will be timing not size.Related to that Performance.
unspammed

Edited by - lomew01 on 02/24/2015 02:54:10
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30421 Posts

Posted - 02/22/2015 :  11:48:44  Show Profile  Visit SwePeso's Homepage  Reply with Quote
Why not use the FORMAT function?



Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA
Go to Top of Page
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000