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 (CAUTION, this will empty your procedure cache, so DON'T RUN ON a PRODUCTION BOX):
DBCC FREEPROCCACHE
GO
/*VARIANT1*/
;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;
GO 100
/*VARIANT2*/
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;
GO 100
SELECT CASE WHEN dest.text LIKE '%/*VARIANT1*/%' THEN 'VARIANT1' ELSE 'VARIANT2' END
, MAX(deqs.execution_count)
, SUM(deqs.total_worker_time)
, AvgWorkerTime = SUM(deqs.total_worker_time) / MAX(deqs.execution_count)
FROM sys.dm_exec_query_stats deqs
CROSS APPLY sys.dm_exec_sql_text(deqs.sql_handle) dest
WHERE (dest.text LIKE '%/*VARIANT1*/%'
OR dest.text LIKE '%/*VARIANT2*/%')
AND dest.text NOT LIKE '%/*NOT_ME!*/%'
GROUP BY CASE WHEN dest.text LIKE '%/*VARIANT1*/%' THEN 'VARIANT1' ELSE 'VARIANT2' END
ORDER BY CASE WHEN dest.text LIKE '%/*VARIANT1*/%' THEN 'VARIANT1' ELSE 'VARIANT2' END
/*NOT_ME!*/;
As advised by @AaronBertrand, I've modified my attempts to run each set of statements 100 times each, then show the times recorded by SQL Server through sys.dm_exec_query_stats
.
The results:
Runs total_time average time
VARIANT1 100 42533 425
VARIANT2 100 138677 1386
Aucun commentaire:
Enregistrer un commentaire