Performance: which is better? - SQL Server Forums

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

All Forums

General SQL Server Forums

Database Design and Application Architecture

Performance: which is better?

Author

Topic

acroes
Starting Member

2 Posts

Posted - 2015-02-03 : 08:17:30

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
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-02-03 : 09:58:52

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.

acroes
Starting Member

2 Posts

Posted - 2015-02-03 : 10:53:07

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 :)

lomew01
Starting Member

4 Posts

Posted - 2015-02-21 : 02:24:53

Yeah i think difference will be timing not size.Related to that Performance.
unspammed

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2015-02-22 : 11:48:44

Why not use the FORMAT function?

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources