問題描述
業(yè)務(wù)需求如下：
有表a和表b，這兩個表結(jié)構(gòu)一致，為不同的業(yè)務(wù)服務(wù)，現(xiàn)在要寫一個存儲過程，存儲過程接受一個參數(shù)，當參數(shù)為0時，查詢表a，參數(shù)為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具有技巧性，它們兩者之間，究竟那一個更好呢？你可能會從性能上來評估，以決定到底用那一種。單純從語句上來看，似乎兩者的效率差不多，下面通過數(shù)據(jù)測試來反映結(jié)果似乎和想像的一樣
建立測試環(huán)境（注，此測試環(huán)境是為幾個主題服務(wù)的，因此結(jié)構(gòu)看起來有些怪異）
use tempdb
go
set nocount on
--======================================
--創(chuàng)建測試環(huán)境
--======================================
raiserror('創(chuàng)建測試環(huán)境', 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
--======================================
--生成測試數(shù)據(jù)
--======================================
raiserror('生成測試數(shù)據(jù)', 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
性能測試結(jié)果
id a b
--- ------- -------
1 3410 2063
2 1703 1656
3 1763 1656
4 1800 1793
5 1643 1856
null 10319 9024
從結(jié)果看，兩者的性能差異很小，所以兩者從性能上比較，可以視為沒有差異
問題所在
雖然在性能上，兩者沒有什么差異，但另一個問題也許你從來沒有考慮過，那就是對表的訪問的問題，在方法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 -- 不回滾事務(wù)，讓鎖一直保持
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'
結(jié)果
你會看到，查詢窗口b中的查詢會及時地完成，而查詢窗口c的查詢會一直等待，你可以通過執(zhí)行存儲過程 sp_who2，查看當前的block狀況來確定查詢窗口c的查詢是否被查詢窗口a的查詢block住
結(jié)論
不要使用查詢方法b，它看起來很棒，實際的結(jié)果即是會增加被block的機會
trackback: http://tb.blog.csdn.net/trackback.aspx?postid=787074
[點擊此處收藏本文] 發(fā)表于 2006年06月10日 20:55:00
滄海笑一聲 發(fā)表于2006-06-11 00:37:00 ip: 221.221.210.*
精辟！
感謝分享！
hmj 發(fā)表于2006-06-11 13:18:00 ip: 222.95.184.*
又學(xué)到了新東西！
cyz1980 發(fā)表于2006-06-12 08:15:00 ip: 222.76.2.*
鄒大哥：
你好，以上描述的問題我也有碰到,對我的啟示也很大，謝謝。但在實際中,有些還是要“一氣呵成”的。比如以下問題(代碼比較長，正因為這樣，才比較有深刻的體會，哈哈。。),理解不到位的地方望鄒大哥指點一下：
第一種方法(作視圖用，便于數(shù)據(jù)庫遷移，便于access等快速調(diào)用，適用性廣)：
declare @month datetime
set @month='2005-4-1'
select @month as 月份,dpname1 as 部門,isnull(開戶人次,0) as 開戶人次,isnull(開戶后第一次存款額,0) as 開戶后第一次存款額,isnull(消費額,0) as 消費額,
isnull(消費次數(shù),0) as 消費次數(shù),isnull(存取款額,0) as 存取款額,isnull(存取款次數(shù),0) as 存取款次數(shù),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 消費次數(shù)
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 存取款次數(shù)
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/*月份參數(shù)*/
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
執(zhí)行后的示例數(shù)據(jù):
月份 部門 開戶人次 開戶后第一次存款額 消費額 消費次數(shù) 存取款額 存取款次數(shù) 卡余額總額
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 現(xiàn)金卡 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 存取款次數(shù) 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 消費次數(shù) 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(消費次數(shù),0) 消費次數(shù),isnull(存取款額,0) 存取款額,isnull(存取款次數(shù),0) 存取款次數(shù),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
新聞熱點
疑難解答
圖片精選
