问题描述
业务需求如下：
有表 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 )
结果
发表评论
评论