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

5081人阅读 评论(6) 收藏 举报

问题描述

    业务需求如下:

    有表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的机会

0
0

查看评论
* 以上用户言论只代表其个人观点,不代表CSDN网站的观点或立场
    个人资料
    • 访问:1298765次
    • 积分:14679
    • 等级:
    • 排名:第785名
    • 原创:178篇
    • 转载:9篇
    • 译文:0篇
    • 评论:880条
    最新评论