mardi 30 décembre 2014

What is the most efficient method of performing the FIZZBUZZ test in SQL Server?


This may not be completely on-topic, however it's a slow day here.


Is there a more efficient method of obtaining a list of numbers from 1 to 49 with a column containing the words FIZZ when the number can be evenly divided by 3, BUZZ when the number can be evenly divided by 5, and FIZZBUZZ when the number can be evenly divided by both 3 and 5?


My attempts are:



DECLARE @Start DATETIME2(7);
DECLARE @End DATETIME2(7);
SET @Start = SYSDATETIME();
;WITH t AS (
SELECT RowNum = ROW_NUMBER() OVER (ORDER BY o.object_id)
FROM sys.objects o
)
SELECT t.RowNum
, CASE WHEN ((t.RowNum % 3) + (t.RowNum % 5)) = 0 THEN 'FIZZBUZZ'
ELSE
CASE WHEN t.RowNum % 3 = 0 THEN 'FIZZ'
ELSE
CASE WHEN t.RowNum % 5 = 0 THEN 'BUZZ'
ELSE ''
END
END
END
FROM t
WHERE t.RowNum < 50;
SET @End = SYSDATETIME();
SELECT DATEDIFF(MICROSECOND, @Start, @End);

GO

DECLARE @Start DATETIME2(7);
DECLARE @End DATETIME2(7);
SET @Start = SYSDATETIME();
DECLARE @t TABLE
(
Num INT NOT NULL PRIMARY KEY CLUSTERED
);
INSERT INTO @t (Num)
SELECT ROW_NUMBER() OVER (ORDER BY o.object_id)
FROM sys.objects o;

SELECT t.Num
, CASE WHEN ((t.Num % 3) + (t.Num % 5)) = 0 THEN 'FIZZBUZZ'
ELSE
CASE WHEN t.Num % 3 = 0 THEN 'FIZZ'
ELSE
CASE WHEN t.Num % 5 = 0 THEN 'BUZZ'
ELSE ''
END
END
END
FROM @t t
WHERE t.Num < 50;
SET @End = SYSDATETIME();
SELECT DATEDIFF(MICROSECOND, @Start, @End);


I'm using SQL Server 2012, so I have the luxury of using DATETIME2(7) as a fairly high precision timer to evaluate how long each process takes. They both take about the same amount of time most of the time, which is to say around 1 millisecond on my workstation. The execution plan for the second version has a total "estimated subtree cost" far higher than the first version.





Aucun commentaire:

Enregistrer un commentaire