server
问题描述
业务需求如下：
有表A和表B，这两个表结构一致，为不同的业务服务，现在要写一个存储过程，存储过程接受一个参数，当参数为0时，查询表A，参数为1时，查询表B。
A、一般的处理方法
IF Flag = 0
SELECT * FROM dbo.A
ELSE IF Flag = 1
SELECT * FROM dbo.B
B、一句的处理方法
SELECT * FROM dbo.A
WHERE Flag = 0
UNION ALL
SELECT * FROM dbo.B
WHERE Flag = 1
分析
从语句的简捷性来看，方法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
-- Table B
CREATE TABLE [dbo].B(
[ItemNumber] [char](15) NOT NULL DEFAULT (''),
[CompanyCode] [char] (4) NOT NULL,
[OwnerCompanyCode] [char](4) NULL,
PRIMARY KEY([ItemNumber], [CompanyCode])
)
CREATE INDEX [ItemNumber] ON [dbo].B([ItemNumber])
CREATE INDEX [CompanyCode] ON [dbo].B([CompanyCode])
CREATE INDEX [OwnerCompanyCode] ON [dbo].B([OwnerCompanyCode])
GO
--======================================
--生成测试数据
--======================================
RAISERROR('生成测试数据', 10, 1) WITH NOWAIT
INSERT [dbo].A([INVNO], [ITEM])
SELECT LEFT(NEWID(), 8), RIGHT(NEWID(), 15)
FROM syscolumns A, syscolumns B
INSERT [dbo].B([ItemNumber], [CompanyCode], [OwnerCompanyCode])
SELECT RIGHT(NEWID(), 15), LEFT(NEWID(), 4), LEFT(NEWID(), 4)
FROM syscolumns A, syscolumns B
GO
进行性能测试
DECLARE a int
SET a = 1
DECLARE t TABLE(
id int IDENTITY,
a int, b int)
DECLARE dt datetime, loop int, id int
SET loop = 0
WHILE loop < 5
BEGIN
SET loop = loop + 1
RAISERROR('test %d', 10, 1, loop) WITH NOWAIT
SET dt = GETDATE()
SELECT [ITEM] FROM A
WHERE a = 0
AND [ITEM] < 'A'
UNION ALL
SELECT [ItemNumber] FROM B
WHERE a = 1
AND [ItemNumber] < 'A'
INSERT t(a) VALUES(DATEDIFF(ms, dt, GETDATE()))
SELECT id = SCOPE_IDENTITY(), dt = GETDATE()
IF a = 0
SELECT [ITEM] FROM A
WHERE [ITEM] < 'A'
ELSE IF a = 1
SELECT [ItemNumber] FROM B
WHERE [ItemNumber] < 'A'
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 3410 2063
2 1703 1656
3 1763 1656
4 1800 1793
5 1643 1856
NULL 10319 9024
从结果看，两者的性能差异很小，所以两者从性能上比较，可以视为没有差异
问题所在
虽然在性能上，两者没有什么差异，但另一个问题也许你从来没有考虑过，那就是对表的访问的问题，在方法A中，肯定只会访问到一个表；而在方法B中，情况还是如此吗？答案是否定的，方法B始终会扫描两个表。而这样的潜台词是，即使在我的查询中，只会用到A表，但如果B表被下了锁的话，整个查询就会被阻塞，而方法A不会。
为了证明这个问题，我们再做下面的测试
BLOCK 的测试—为表A加锁 (查询窗口A)
BEGIN TRAN
UPDATE A SET [ITEM] = RIGHT(NEWID(), 4)
WHERE [ITEM] BETWEEN '9' AND 'A'
--ROLLBACK TRAN -- 不回滚事务，让锁一直保持
BLOCK 的测试—测试查询方法A(查询窗口B)
-- run query windows 2
DECLARE a int
SET a = 1
IF a = 0
SELECT [TranNumber] FROM A
WHERE [ITEM] < 'A'
ELSE IF a = 1
SELECT [ItemNumber] FROM B
WHERE [ItemNumber] < 'A'
BLOCK 的测试—测试查询方法B(查询窗口C)
-- run query windows 3
DECLARE a int
SET a = 1
SELECT [ITEM] FROM A
WHERE a = 0
AND [ITEM] < 'A'
UNION ALL
SELECT [ItemNumber] FROM B
WHERE a = 1
AND [ItemNumber] < 'A'
结果
你会看到，查询窗口B中的查询会及时地完成，而查询窗口C的查询会一直等待，你可以通过执行存储过程 sp_who2，查看当前的BLOCK状况来确定查询窗口C的查询是否被查询窗口A的查询BLOCK住
结论
不要使用查询方法B，它看起来很棒，实际的结果即是会增加被BLOCK的机会
Trackback: http://tb.blog.csdn.net/TrackBack.aspx?PostId=787074
[点击此处收藏本文] 发表于 2006年06月10日 20:55:00
沧海笑一声 发表于2006-06-11 00:37:00 IP: 221.221.210.*
精辟！
感谢分享！
hmj 发表于2006-06-11 13:18:00 IP: 222.95.184.*
又学到了新东西！
cyz1980 发表于2006-06-12 08:15:00 IP: 222.76.2.*
邹大哥：
你好，以上描述的问题我也有碰到,对我的启示也很大，谢谢。但在实际中,有些还是要“一气呵成”的。比如以下问题(代码比较长，正因为这样，才比较有深刻的体会，哈哈。。),理解不到位的地方望邹大哥指点一下：
第一种方法(作视图用，便于数据库迁移，便于Access等快速调用，适用性广)：
declare month datetime
set month='2005-4-1'
select month as 月份,dpname1 as 部门,isnull(开户人次,0) as 开户人次,isnull(开户后第一次存款额,0) as 开户后第一次存款额,isnull(消费额,0) as 消费额,
isnull(消费次数,0) as 消费次数,isnull(存取款额,0) as 存取款额,isnull(存取款次数,0) as 存取款次数,isnull(卡余额总额,0) as 卡余额总额
from (select distinct dpcode1,dpname1 from T_Department) Department left outer join (SELECT DpCode1, kh_month, COUNT(*) AS 开户人次, SUM(in_out_fare)
AS 开户后第一次存款额
FROM (SELECT dep.DpCode1, RTRIM(CAST(YEAR(T_Customers.OpenDt) AS char))
+ '-' + RTRIM(CAST(MONTH(T_Customers.OpenDt) AS char))
+ '-' + RTRIM(DAY(0)) AS kh_month, min_in_out_fare.in_out_fare
FROM T_Customers INNER JOIN
(SELECT DpCode1 + DpCode2 + DpCode3 AS dpcode, DpCode1
FROM T_Department) dep ON
T_Customers.Account = dep.dpcode left outer JOIN
(SELECT min_opcount.CustomerID,
T_CashRec.InFare - T_CashRec.OutFare in_out_fare
FROM (SELECT CustomerID, MIN(OpCount) AS min_opcount
FROM T_CashRec
GROUP BY CustomerID) min_opcount INNER JOIN
T_CashRec ON
min_opcount.CustomerID = T_CashRec.CustomerID AND
min_opcount.min_opcount = T_CashRec.OpCount) min_in_out_fare ON
min_in_out_fare.CustomerID = T_Customers.CustomerID)
一级单位月开户明细
GROUP BY DpCode1, kh_month having kh_month=month/*一级单位月开户汇总*/
) kh on kh.dpcode1=Department.dpcode1 left outer join (SELECT DpCode1, xf_month, SUM(OpFare) AS 消费额,count(*) as 消费次数
FROM (SELECT dep.DpCode1, RTRIM(CAST(YEAR(consumerec.OpDt) AS char))
+ '-' + RTRIM(CAST(MONTH(consumerec.OpDt) AS char)) + '-' + RTRIM(DAY(0))
AS xf_month, consumerec.OpFare
FROM T_ConsumeRec consumerec INNER JOIN
T_Customers ON
consumerec.CustomerID = T_Customers.CustomerID INNER JOIN
(SELECT DpCode1 + DpCode2 + DpCode3 AS dpcode, DpCode1
FROM T_Department) dep ON T_Customers.Account = dep.dpcode)
一级单位月消费明细
GROUP BY DpCode1, xf_month having xf_month=month /*一级单位月消费汇总*/
) xf on xf.dpcode1=Department.dpcode1 left outer join (SELECT DpCode1, cqk_month, SUM(inFare - outFare) AS 存取款额,count(*) as 存取款次数
FROM (SELECT dep.DpCode1, RTRIM(CAST(YEAR(consumerec.cashdt) AS char))
+ '-' + RTRIM(CAST(MONTH(consumerec.cashdt) AS char))
+ '-' + RTRIM(DAY(0)) AS cqk_month, consumerec.inFare,
consumerec.outFare
FROM T_CashRec consumerec INNER JOIN
T_Customers ON
consumerec.CustomerID = T_Customers.CustomerID INNER JOIN
(SELECT DpCode1 + DpCode2 + DpCode3 AS dpcode, DpCode1
FROM T_Department) dep ON T_Customers.Account = dep.dpcode)
一级单位月存取款明细
GROUP BY DpCode1, cqk_month having cqk_month=month/*一级单位月存取款汇总*/
) cq on cq.dpcode1=Department.dpcode1 left outer join (SELECT dep.DpCode1, sum(id_MaxO.OddFare) as 卡余额总额
FROM (SELECT id_m_maxC.customerid, id_c_o.OddFare
FROM (SELECT customerid, MAX(OpCount) AS max_opcount
FROM (SELECT CustomerID, OpCount, RTRIM(CAST(YEAR(Dt) AS char))
+ '-' + RTRIM(CAST(MONTH(Dt) AS char)) + '-' + RTRIM(DAY(0))
AS month
FROM (SELECT CustomerID, OpCount, OpDt AS dt
FROM T_ConsumeRec
UNION ALL
SELECT CustomerID, OpCount, cashDt AS dt
FROM T_cashRec
UNION ALL
SELECT CustomerID, OpCount, putoutDt AS dt
FROM T_subsidyputout) id_c_d) id_c_m where month <= month/*月份参数*/
GROUP BY customerid
) id_m_maxC INNER JOIN
(SELECT CustomerID, OpCount, OddFare
FROM (SELECT CustomerID, OpCount, OddFare
FROM T_ConsumeRec
UNION ALL
SELECT CustomerID, OpCount, OddFare
FROM T_cashRec
UNION ALL
SELECT CustomerID, OpCount, OddFare
FROM T_subsidyputout) Lid_c_o) id_c_o ON
id_c_o.CustomerID = id_m_maxC.customerid AND
id_c_o.OpCount = id_m_maxC.max_opcount) id_MaxO INNER JOIN
T_Customers ON id_MaxO.customerid = T_Customers.CustomerID INNER JOIN
(SELECT DpCode1 + DpCode2 + DpCode3 AS dpcode, DpCode1
FROM T_Department) dep ON T_Customers.Account = dep.dpcode/*一级单位在某月份的卡余额明细*/
group by dep.DpCode1 /*一级单位在某月份的卡余额汇总*/) kye on kye.dpcode1=Department.dpcode1
执行后的示例数据:
月份 部门 开户人次 开户后第一次存款额 消费额 消费次数 存取款额 存取款次数 卡余额总额
2005-4-1 职工卡 4 ￥2,400.00 ￥7,728.29 1054 ￥531,369.40 1112 ￥523,937.84
2005-4-1 职工卡2 0 ￥0.00 ￥0.00 0 ￥0.00 0 ￥0.00
2005-4-1 外单位人员 100 ￥620.00 ￥0.00 0 ￥620.00 4 ￥620.00
2005-4-1 挂帐卡 0 ￥0.00 ￥0.00 0 ￥0.00 0 ￥0.00
2005-4-1 现金卡 2 ￥0.00 ￥0.00 0 ￥0.00 0 ￥0.00
2005-4-1 折扣卡 56 ￥16,500.00 ￥984.40 152 ￥16,500.00 55 ￥15,515.60
2005-4-1 集团代办卡 0 ￥0.00 ￥0.00 0 ￥0.00 0 ￥0.00
第二种方法[封装成存储过程，大量使用临时表(效率？)，便于阅读理解与更新，但适用范围有限制]：
declare month datetime
set month='2004-9-1'
SELECT CustomerID, OpCount, fare, oddfare, dt, RTRIM(CAST(YEAR(dt) AS char))
+ '-' + RTRIM(CAST(MONTH(dt) AS char)) + '-' + RTRIM(DAY(dt)) AS rq,
RTRIM(CAST(YEAR(dt) AS char)) + '-' + RTRIM(CAST(MONTH(dt) AS char))
+ '-' + RTRIM(DAY(0)) AS [month], 类别 into #mingxi
FROM (SELECT CustomerID, OpCount, opfare fare, oddfare, OpDt dt, '消费' AS 类别
FROM T_ConsumeRec
UNION ALL
SELECT CustomerID, OpCount, infare - outfare fare, oddfare, cashDt dt,
'出纳' AS 类别
FROM T_Cashrec) l
SELECT T_Customers.CustomerID, t_dpcode.DpCode1 into #custid_dpcode1
FROM (SELECT DpCode1, DpCode1 + DpCode2 + DpCode3 AS dpcode
FROM T_Department) t_dpcode INNER JOIN
T_Customers ON t_dpcode.dpcode = T_Customers.Account
SELECT custid_dpcode1.DpCode1, COUNT(*) AS 开户人次, SUM(l.in_out_fare)
AS 开户后第一次存款额 into #kh
FROM (SELECT T_Customers.CustomerID, RTRIM(CAST(YEAR(T_Customers.OpenDt)
AS char)) + '-' + RTRIM(CAST(MONTH(T_Customers.OpenDt) AS char))
+ '-' + RTRIM(DAY(0)) AS [month], ISNULL([first].in_out_fare, 0)
AS in_out_fare
FROM T_Customers LEFT OUTER JOIN
(SELECT min_opcount.CustomerID,
T_CashRec.InFare - T_CashRec.OutFare AS in_out_fare
FROM (SELECT CustomerID, MIN(OpCount) AS min_opcount
FROM T_CashRec
GROUP BY CustomerID) min_opcount INNER JOIN
T_CashRec ON
min_opcount.CustomerID = T_CashRec.CustomerID AND
min_opcount.min_opcount = T_CashRec.OpCount) [first] ON
T_Customers.CustomerID = [first].CustomerID) l INNER JOIN
#custid_dpcode1 custid_dpcode1 ON l.CustomerID = custid_dpcode1.CustomerID
WHERE (l.[month] = month)
GROUP BY custid_dpcode1.DpCode1
SELECT custid_dpcode1.DpCode1, SUM(mingxi.fare) AS 存取款额, COUNT(*)
AS 存取款次数 into #cq
FROM #mingxi mingxi INNER JOIN
#custid_dpcode1 custid_dpcode1 ON mingxi.CustomerID = custid_dpcode1.CustomerID
WHERE (mingxi.类别 = '出纳') AND (mingxi.[month] = month)
GROUP BY custid_dpcode1.DpCode1
SELECT custid_dpcode1.DpCode1, SUM(mingxi.fare) AS 消费额, COUNT(*)
AS 消费次数 into #xf
FROM #mingxi mingxi INNER JOIN
#custid_dpcode1 custid_dpcode1 ON mingxi.CustomerID = custid_dpcode1.CustomerID
WHERE (mingxi.类别 = '消费') AND (mingxi.[month] =month)
GROUP BY custid_dpcode1.DpCode1
SELECT custid_dpcode1.DpCode1, SUM(custid_oddfare.oddfare) AS 卡余额总额 into #kye
FROM (SELECT custid_max_opcount.CustomerID, mingxi_.oddfare
FROM (SELECT CustomerID, MAX(OpCount) AS max_opcount
FROM #mingxi mingxi
WHERE ([month] <= month)
GROUP BY CustomerID) custid_max_opcount INNER JOIN
#mingxi mingxi_ ON custid_max_opcount.CustomerID = mingxi_.CustomerID AND
custid_max_opcount.max_opcount = mingxi_.OpCount)
custid_oddfare INNER JOIN
#custid_dpcode1 custid_dpcode1 ON custid_oddfare.CustomerID = custid_dpcode1.CustomerID
GROUP BY custid_dpcode1.DpCode1
select month 月份,dpt.dpname1 部门,isnull(开户人次,0) 开户人次,isnull(开户后第一次存款额,0) 开户后第一次存款额,isnull(消费额,0) 消费额,isnull(消费次数,0) 消费次数,isnull(存取款额,0) 存取款额,isnull(存取款次数,0) 存取款次数,isnull(卡余额总额,0) 卡余额总额
from (SELECT DISTINCT DpCode1, DpName1
FROM T_Department) dpt left join #kh kh on kh.dpcode1=dpt.dpcode1 left join #cq cq on
cq.dpcode1=dpt.dpcode1 left join #xf xf on xf.dpcode1=dpt.dpcode1 left join #kye kye
on kye.dpcode1=dpt.dpcode1
drop table #mingxi
drop table #custid_dpcode1
drop table #kh
drop table #cq
drop table #xf
drop table #kye
