sql-server – 在SQL Server中执行FIZZBUZZ测试的最有效方法是什么？

这可能不完全是主题,但这是一个缓慢的一天.

是否有更有效的方法从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

解决方法

-- 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 FilesMicrosoft SQL ServerMSSQL12.SQL2014MSSQLDATAFN1.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());

（编辑：安卓应用网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!