避免把判断处理放入WHERE 条件

轉自:http://www.netfocus.cn/peoplearticle1131.html

问题描述

    业务需求如下：

    有表A，在查询的时候，需要根据标志确定是查询大于某个值的记录，还是小于某个值的记录

 

A、一般的处理方法

IF @a = 0

    SELECT [TranNumber] FROM A

    WHERE [TranNumber] < 10000

ELSE IF @a = 1

    SELECT [TranNumber] FROM A

    WHERE [TranNumber] > 10000

B、一句的处理方法

SELECT [TranNumber] FROM A

WHERE

    (@a = 0 AND [TranNumber] < 10000)

    OR

    (@a = 1 AND [TranNumber] > 10000)

 

分析

    从语句的简捷性来看，方法B具有技巧性，它们两者之间，究竟那一个更好呢？你可能会从性能上来评估，以决定到底用那一种。单纯从语句上来看，两者的效率差别应该不会非常大，实际测试的结果会如我们想象吗？继续往下看

 

建立测试环境（注，此测试环境是为几个主题服务的，因此结构看起来有些怪异）

USE tempdb

GO

SET NOCOUNT ON

--======================================

--创建测试环境

--======================================

RAISERROR('创建测试环境', 10, 1) WITH NOWAIT

-- Table A

CREATE TABLE [dbo].A(

    [TranNumber] [int] IDENTITY(1, 1) NOT NULL,

    [INVNO] [char](8) NOT NULL,

    [ITEM] [char](15) NULL DEFAULT (''),

    PRIMARY KEY([TranNumber])

)

CREATE INDEX [indexONinvno] ON [dbo].A([INVNO])

CREATE INDEX [indexOnitem] ON [dbo].A ([ITEM])

CREATE INDEX [indexONiteminnvo] ON [dbo].A([INVNO], [ITEM])

GO

--======================================

--生成测试数据

--======================================

RAISERROR('生成测试数据', 10, 1) WITH NOWAIT

INSERT [dbo].A([INVNO], [ITEM])

SELECT LEFT(NEWID(), 8), RIGHT(NEWID(), 15)

FROM syscolumns A, syscolumns B

GO

 

进行性能测试

DECLARE @a int

SET @a = 0

DECLARE @t TABLE(

    id int IDENTITY,

    a int, b int)

DECLARE @dt datetime, @loop int, @id int

SET @loop = 1

WHILE @loop < 10

BEGIN

    SET @loop = @loop + 1

    RAISERROR('test %d', 10, 1, @loop) WITH NOWAIT

    SET @dt = GETDATE()

        IF @a = 0

            SELECT * FROM A

            WHERE [TranNumber] < 10000

        ELSE IF @a = 1

            SELECT * FROM A

            WHERE [TranNumber] > 10000

    INSERT @t(a) VALUES(DATEDIFF(ms, @dt, GETDATE()))

    SELECT @id = SCOPE_IDENTITY(), @dt = GETDATE()

        SELECT * FROM A

        WHERE

            (@a = 0 AND [TranNumber] < 10000)

            OR

            (@a = 1 AND [TranNumber] > 10000)

    UPDATE @t SET b = DATEDIFF(ms, @dt, GETDATE())

    WHERE id = @id

END

SELECT * FROM @t

UNION ALL

SELECT NULL, SUM(a), SUM(b) FROM @t 

性能测试结果

id          a           b

----------- ----------- -----------

1           173         173

2           140         170

3           140         173

4           126         170

5           140         173

6           140         173

7           123         170

8           190         170

9           123         190

NULL        1295        1562

从结果看，两者有一定性能差异，但还算是在可接受范围内吧

 

还有其他问题吗？

除了性能外，另一个要考虑的问题是BLOCK的问题，下面的测试来反映BLOCK的影响

 

BLOCK 的测试—为表A加锁(查询窗口A)

-- run query windows 1

BEGIN TRAN

    UPDATE A SET [ITEM] = RIGHT(NEWID(), 4)

    WHERE [TranNumber] < 100

--ROLLBACK TRAN

 

BLOCK 的测试—测试查询方法A(查询窗口B)

-- run query windows 2

DECLARE @a int

SET @a = 1

IF @a = 0

    SELECT * FROM A

    WHERE [TranNumber] < 10000

ELSE IF @a = 1

    SELECT * FROM A

    WHERE [TranNumber] > 10000

 

BLOCK 的测试—测试查询方法B(查询窗口C)

-- run query windows 3

DECLARE @a int

SET @a = 1

SELECT * FROM A

WHERE

    (@a = 0 AND [TranNumber] < 10000)

    OR

    (@a = 1 AND [TranNumber] > 10000)

 

结果

你会看到，查询窗口B中的查询会及时地完成，而查询窗口C的查询会一直等待，你可以通过执行存储过程 sp_who2，查看当前的BLOCK状况来确定查询窗口C的查询是否被查询窗口A的查询BLOCK住

 

结论

不要使用查询方法B，它看起来很棒，实际的结果是性能不太好，而且会增加被BLOCK的机会