问题描述
业务需求如下：
有表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 [email protected]/*一级单位月开户汇总*/
) 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 [email protected] /*一级单位月消费汇总*/
) 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 [email protected]/*一级单位月存取款汇总*/
) 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] [email protected])
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
新闻热点
疑难解答
