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

原创 2006年06月14日 19:53:00

问题描述

    业务需求如下:

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

《重构--改善既有代码的设计》读书笔记之四:将条件分支语句放入合适类中

要点:最好不要在另一个对象的属性基础上运用条件分支语句,如果不得不使用,也应该在对象自己的数据上使用。 方法:getCharge() 和 getFrequentRenterPoints()移动到Mo...

C语随机产生20个正整数存入数组a中,且每个数均在1000-9999之间(包含1000和9999)。对数组进行排序,要求按每个数的后三位的大小进行升序排列,然后取出满足此条件的前10个数放入数组b中,

函数的实现部分: //给20个数赋值 void fuzhiNumber(int *p, int count) { for (int i = 0; i < count; i++) { ...

mysql where条件中的判断语句

mysql where条件中的判断语句  AND CASE 1 WHEN "Y"=(SELECT is_open from category as oca where oca.category_i...

SQL语句Where中使用别名作为判断条件

当我们使用某个表达式作为输出的一列时 , 我们无法再 Where 条件中直接使用该列作判断条件 . 例如下面的 SQL 语句 : select id, (c1 + c...

insert中加入where条件判断,解决插入重复数据的问题

对于会员注册,我们经常会做唯一性验证,通常情况下我们有这两种方式: 1. 数据库表设计的时候loginName增加唯一约束 2. 注册之前先查一下然后再去进行插入操作针对以上两种情况,第一种情况,...

对传入where条件的sql语句进行参数化处理

一般对sql参数化处理都是在sql语句执行的地方对所要输入的参数进行参数化已防止注入。但有时候会碰到一些特殊情况。一些老的项目可能会在逻辑处理的地方执行sql语句 而sql语句的where条件来自we...

用MD5函数处理oracle数据库中clob字段在where条件或者group中使用

最近一段一直被大字段困扰,想了很多办法
  • siyouzi
  • siyouzi
  • 2014年06月09日 16:52
  • 748

数据量较大时,把数据放入缓存中的处理办法

public List TrafficLog { get { List result = new List(); if (Session["TrafficLog"] == null || (DateT...
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:避免把判断处理放入 WHERE 条件
举报原因:
原因补充:

(最多只允许输入30个字)