Dave Ballantyne (5/6/2010)
In any case calling a Function (scalar udf) a million time is bad news.
http://sqlblogcasts.com/blogs/sqlandthelike/archive/2009/10/15/udf-overhead-a-simple-example.aspx
That was a very interesting read, thankyou 🙂
This is my random months table - 1,000,000 rows (not the cleverest way to do it, just wanted to grab some test data)
USE testingdb
IF EXISTS (SELECT *
FROM sys.tables t
JOIN sys.schemas s
ON t.schema_id = s.schema_id
WHERE t.[Name] = 'monthsNO'
AND s.[Name] = 'dbo')
DROP TABLE dbo.monthsno
CREATE TABLE dbo.monthsno
(
[fldmonth] TINYINT NOT NULL
)
ON [PRIMARY]
GO
USE testingdb
IF EXISTS (SELECT *
FROM sys.tables t
JOIN sys.schemas s
ON t.schema_id = s.schema_id
WHERE t.[Name] = 'months'
AND s.[Name] = 'dbo')
DROP TABLE dbo.months
CREATE TABLE dbo.months
(
[fldmonth] VARCHAR(20) NOT NULL
)
ON [PRIMARY]
GO
USE testingdb
DECLARE @maxRandomValue TINYINT,
@minRandomValue TINYINT,
@cnt INT
SET @maxRandomValue = 12
SET @minRandomValue = 1
SET @cnt = 1000000
WHILE @cnt > 0
BEGIN
SET @cnt = @cnt - 1
INSERT INTO dbo.monthsno
([fldmonth])
SELECT CAST(CAST(( ( @maxRandomValue ) - @minRandomValue ) * Rand() + @minRandomValue AS TINYINT) AS VARCHAR)
END
GO
USE testingdb
INSERT INTO dbo.months
([fldmonth])
SELECT Datename(MONTH, Dateadd(MONTH, [fldmonth] - 1, 0)) AS monthname
FROM dbo.monthsno
GO
DROP TABLE dbo.monthsno
Now, to test the time I ran each query surrounded by: -
DECLARE @time datetime
SET @time=getdate()
/*CODE*/
SELECT datediff(ms,@time,getdate()) as "Time Taken"
Firstly: -
SELECT MONTH([fldmonth] + ' 1 2010')
FROM dbo.months
After 5 attempts I got - 4513, 4453, 4453, 4606 and 4426, so roughly 4.5 seconds on 1 million rows.
Secondly: -
SELECT ( CASE [fldmonth]
WHEN 'January' THEN 1
WHEN 'February' THEN 2
WHEN 'March' THEN 3
WHEN 'April' THEN 4
WHEN 'May' THEN 5
WHEN 'June' THEN 6
WHEN 'July' THEN 7
WHEN 'August' THEN 8
WHEN 'September' THEN 9
WHEN 'October' THEN 10
WHEN 'November' THEN 11
WHEN 'December' THEN 12
END )
FROM dbo.months
After 5 attempts I got - 4513, 4406, 4533, 4530 and 4516, so once again roughly 4.5 seconds on 1 million rows.
I think the problem was my use of the word "function" in my post. . . it wasn't the word I was after 🙂