問題描述
業(yè)務(wù)需求如下:
有表 A 和表 B ,這兩個(gè)表結(jié)構(gòu)一致,為不同的業(yè)務(wù)服務(wù),現(xiàn)在要寫一個(gè)存儲(chǔ)過程,存儲(chǔ)過程接受一個(gè)參數(shù),當(dāng)參數(shù)為 0 時(shí),查詢表 A ,參數(shù)為 1 時(shí),查詢表 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 具有技巧性,它們兩者之間,究竟那一個(gè)更好呢?你可能會(huì)從性能上來評估,以決定到底用那一種。單純從語句上來看,似乎兩者的效率差不多,下面通過數(shù)據(jù)測試來反映結(jié)果似乎和想像的一樣
建立測試環(huán)境 (注,此測試環(huán)境是為幾個(gè)主題服務(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
進(jìn)行性能測試
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é)果看,兩者的性能差異很小,所以兩者從性能上比較,可以視為沒有差異
問題所在
雖然在性能上,兩者沒有什么差異,但另一個(gè)問題也許你從來沒有考慮過,那就是對表的訪問的問題,在方法 A 中,肯定只會(huì)訪問到一個(gè)表;而在方法 B 中,情況還是如此嗎?答案是否定的,方法 B 始終會(huì)掃描兩個(gè)表。而這樣的潛臺(tái)詞是, 即使在我的查詢中,只會(huì)用到 A 表,但如果 B 表被下了鎖的話,整個(gè)查詢就會(huì)被阻塞,而方法 A 不會(huì) 。
為了證明這個(gè)問題,我們再做下面的測試
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 [
- 2008-04-12 19:09
- 瀏覽 166
- 評論(0)
- 相關(guān)推薦
發(fā)表評論
更多文章、技術(shù)交流、商務(wù)合作、聯(lián)系博主
微信掃碼或搜索:z360901061

微信掃一掃加我為好友
QQ號(hào)聯(lián)系: 360901061
您的支持是博主寫作最大的動(dòng)力,如果您喜歡我的文章,感覺我的文章對您有幫助,請用微信掃描下面二維碼支持博主2元、5元、10元、20元等您想捐的金額吧,狠狠點(diǎn)擊下面給點(diǎn)支持吧,站長非常感激您!手機(jī)微信長按不能支付解決辦法:請將微信支付二維碼保存到相冊,切換到微信,然后點(diǎn)擊微信右上角掃一掃功能,選擇支付二維碼完成支付。
【本文對您有幫助就好】元

評論