这可能不完全是主题,但这是一个缓慢的一天.
是否有更有效的方法从1到49获得一个数字列表,其中一列包含单词FIZZ,当数字可以被均匀地除以3时,BUZZ当数字可以被均匀地除以5时,和FIZZBUZZ当数字可以被3和5均分？
我的尝试是(小心,这将清空你的程序缓存,所以不要在生产箱上运行)：
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!*/;
正如@AaronBertrand所建议的那样,我已经修改了每次运行每组语句100次的尝试,然后显示sql Server通过sys.dm_exec_query_stats记录的时间.
结果：
Runs total_time average time VARIANT1 100 42533 425 VARIANT2 100 138677 1386
解决方法
使用sql Server 2014内存优化表和本机编译过程：
-- Setup
CREATE DATABASE InMem;
GO
ALTER DATABASE InMem
ADD FILEGROUP FG1
CONTAINS MEMORY_OPTIMIZED_DATA;
GO
ALTER DATABASE InMem
ADD FILE
(
NAME = 'FN1',-- Change to suit your system
FILENAME = 'C:\Program Files\Microsoft sql Server\MSsql12.sql2014\MSsql\DATA\FN1.mod'
)
TO FILEGROUP FG1;
GO
USE InMem;
GO
CREATE TYPE dbo.FizzBuzzTableType AS TABLE
(
n integer NOT NULL INDEX i,FizzBuzz varchar(8) NOT NULL
) WITH (MEMORY_OPTIMIZED = ON);
GO
原生程序：
CREATE PROCEDURE dbo.FizzBuzz
WITH
NATIVE_COMPILATION,SCHEMABINDING,EXECUTE AS OWNER
AS
BEGIN ATOMIC
WITH
(
TRANSACTION ISOLATION LEVEL = SNAPSHOT,LANGUAGE = N'english'
)
DECLARE @n AS dbo.FizzBuzzTableType;
DECLARE @i integer = 1;
WHILE @i < 50
BEGIN
IF @i % 15 = 0
BEGIN
INSERT @n (n,FizzBuzz)
VALUES (@i,'FizzBuzz')
END
ELSE
BEGIN
IF @i % 3 = 0
BEGIN
INSERT @n (n,FizzBuzz)
VALUES (@i,'Fizz')
END
ELSE
BEGIN
IF @i % 5 = 0
BEGIN
INSERT @n (n,FizzBuzz)
VALUES (@i,'Buzz')
END
ELSE
BEGIN
INSERT @n (n,CONVERT(varchar(8),@i));
END;
END;
END;
SET @i += 1;
END;
SELECT
N.n,N.FizzBuzz
FROM @n AS N
ORDER BY
N.n;
END;
测试：
SET NOCOUNT ON; PRINT SYSUTCDATETIME(); GO DECLARE @T AS dbo.FizzBuzzTableType; INSERT @T (n,FizzBuzz) EXECUTE dbo.FizzBuzz; GO 100 PRINT SYSUTCDATETIME();
典型结果：
-- 95ms for 100 iterations,< 1ms each 2014-12-31 10:07:13.7993355 Beginning execution loop Batch execution completed 100 times. 2014-12-31 10:07:13.8943409
这会将过程输出写入内存表变量,因为否则我们只是测试在SSMS中显示结果的速度.
一百万行
上述本机过程大约需要12秒才能运行1,000,000个数字.在T-sql中有各种更快的方法来做同样的事情.我之前写的一篇文章如下.当达到预期的并行计划时,它在我的笔记本电脑上以大约500毫秒的速度运行：
IF OBJECT_ID(N'tempdb..#Result',N'U') IS NOT NULL
DROP TABLE #Result;
IF OBJECT_ID(N'tempdb..#Thousand',N'U') IS NOT NULL
DROP TABLE #Thousand;
SET NOCOUNT ON;
DECLARE @start datetime2(7) = SYSUTCDATETIME();
CREATE TABLE #Thousand
(
n integer NOT NULL,CONSTRAINT PK_#Thousand
PRIMARY KEY CLUSTERED (n)
);
-- Add 1,000 rows numbered 0-999 to #Thousand
WITH
L1 (n) AS
(
SELECT V.n
FROM
(
VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9)
) AS V (n)
),Thousand AS
(
SELECT n =
CONVERT
(
integer,ROW_NUMBER() OVER (
ORDER BY (SELECT NULL))
- 1
)
FROM L1
CROSS JOIN L1 AS L2
CROSS JOIN L1 AS L3
)
INSERT #Thousand (n)
SELECT n
FROM Thousand;
-- To hold the Fizz Buzz output
CREATE TABLE #Result
(
n integer NOT NULL,result varchar(8) NOT NULL
);
INSERT #Result
SELECT
Million.n,Million.result
FROM
(
-- Modulo operation to encourage few outer rows parallelism
SELECT n
FROM #Thousand
WHERE n % 1 = 0
) AS T1
-- Outer Apply to keep the Compute Scalar parallel
OUTER APPLY
(
SELECT
F2.n,F2.result
FROM #Thousand AS T2
CROSS APPLY
(
-- Row numbers 1 to 1,000
SELECT (T1.n * 1000) + T2.n + 1
) AS F1 (n)
CROSS APPLY
(
-- The Fizz Buzz bit
SELECT
F1.n,result =
CASE
WHEN F1.n % 15 = 0 THEN 'FizzBuzz'
WHEN F1.n % 3 = 0 THEN 'Buzz'
WHEN F1.n % 5 = 0 THEN 'Fizz'
ELSE CONVERT(varchar(8),F1.n)
END
) AS F2
) AS Million
OPTION (MAXDOP 4,QUERYTRACEON 9481);
PRINT DATEDIFF(MILLISECOND,@start,SYSUTCDATETIME());
