|
|
Posted on 2008-11-11 18:55 大大毛 閱讀(520) 評(píng)論(0) 編輯 收藏 所屬分類: SQL
問題?
??????這一次有同事在做一個(gè)小CASE時(shí)遇到問題無法解決. 整個(gè)的需求比較簡(jiǎn)單, 是需要從資料庫(kù)中撈資料出來進(jìn)行列印, 需求如下:
??????1. 從tbMain中找符合條件的客戶.限定條件:只有一筆資料的客戶
??????2. 查詢tbForeclose, 剔除掉某些客戶.剔除條件:姓名存在於tbForeclose中的客戶即要從名單中剔除
??????3. 從tbDetail中撈客戶的明細(xì)資料出來進(jìn)行列印.關(guān)聯(lián)條件:依tbMain中客戶ID直接帶出即可
--
主檔(同一客戶會(huì)有多筆資料)
Create
?
Table
?tbMain?(
????aID?
int
?
identity
(
1
,
1
),
????pID?
varchar
(
10
)?
not
?
null
,????
--
客戶ID
????pName?
nvarchar
(
20
)?
not
?
null
,????
--
客戶姓名
????pDoing?
varchar
(
20
)?
not
?
null
);
--
剔除客戶檔(僅存放客戶姓名)
Create
?
Table
?tbForeclose?(
????pName?
nvarchar
(
20
)?
not
?
null
????
--
剔除客戶姓名
);
--
客戶資料明細(xì)檔
Create
?
Table
?tbDetail?(
????pID?
varchar
(
10
)?
primary
?
key
,????
--
客戶ID(KEY)
????pSex?
char
(
1
)?
not
?
null
????????
--
客戶其他詳細(xì)資料
);
?
示例數(shù)據(jù)
SET
?NOCOUNT?
ON
Declare
?
@iRowCount
?
int
Declare
?
@pID
?
varchar
(
10
),
@pName
?
nvarchar
(
20
),
@pDoing
?
varchar
(
20
),
@pSex
?
char
(
1
)
Declare
?
@i
?
int
,
@j
?
int
,
@k
?
int
--
生成tbMain記錄條數(shù)
Set
?
@iRowCount
=
1000
Select
?
@i
=
1
While
?
@i
?
<
?
@iRowCount
Begin
????
--
生成客戶ID
????
Set
?
@j
?
=
?
1
????
While
?
@j
?
>
?
0
????
Begin
????????
Set
?
@pID
?
=
?
Left
(
NewID
(),
10
)
????????
Select
?
@j
?
=
?
Count
(
*
)?
From
?tbDetail?
Where
?pID
=
@pID
????
End
????
--
生成客戶姓名
????
Set
?
@k
?
=
?
2
+
Round
(
3
*
Rand
(),
0
)
????
Select
?
@j
?
=
?
1
,
@pName
=
''
????
While
?
@j
?
<=
?
@k
????
Begin
????????
Set
?
@pName
?
=
?
@pName
?
+
?
NChar
(
31000
?
+
?
Round
(
3000
*
Rand
(),
0
))
????????
Set
?
@j
?
=
?
@j
?
+
?
1
????
End
????
--
生成客戶性別
????
If
?
@k
?
%
?
2
?
=
?
0
????????
Set
?
@pSex
?
=
?
'
T
'
????
Else
????????
Set
?
@pSex
?
=
?
'
F
'
????
--
添加資料
????
--
主檔添加筆數(shù)(1-5筆)
????
Set
?
@k
?
=
?
1
?
+
?
Round
(
5
*
Rand
(),
0
)
????
Set
?
@j
?
=
?
1
????
While
?
@j
?
<=
?
@k
????
Begin
????????
Set
?
@i
?
=
?
@i
?
+
?
1
????????
--
生成Doing欄位
????????
Set
?
@pDoing
?
=
?
Cast
(
@j
?
As
?
Varchar
(
2
))?
+
?
'''
th?Doing
'
????????
Insert
?
Into
?tbMain?
Values
?(
@pID
,
@pName
,
@pDoing
)
????????
Set
?
@j
?
=
?
@j
?
+
?
1
????
End
????
--
客戶資料明細(xì)檔
????
Insert
?
Into
?tbDetail?
Values
?(
@pID
,
@pSex
)
End
--
隨機(jī)向剔除客戶檔添加1/10的客戶姓名
Set
?
@k
?
=
?
Round
(
@iRowCount
?
/
?
10
,
0
)
SET
?
ROWCOUNT
??
@k
Insert
?
Into
?tbForeclose?
Select
?pName?
From
?tbMain?
Group
?
By
?pID,pName?
Order
?
By
?
NewID
()
SET
?
ROWCOUNT
?
0
SET
?NOCOUNT?
OFF
嘗試及解決
??????就此需求表面來看, 實(shí)現(xiàn)起來很是簡(jiǎn)單, 只要一條SQL就好:
Select
????????
*
????
From
????????tbDetail
????
Inner
?
Join
?(
????????
Select
????????????????pID,pName
????????????
From
????????????????tbMain
????????????
Where
????????????????
Not
?
Exists
?(
Select
?
1
?
From
?tbForeclose?
Where
?tbForeclose.pName
=
tbMain.pName)
????????????
Group
?
By
????????????????pID,pName
????????????
Having
????????????????
Count
(
*
)
=
1
????)?tbMain1?
On
?tbMain1.pID
=
tbDetail.pID
??????但此需求在開發(fā)時(shí)卻有遇到一些限制條件, 不能這樣去實(shí)現(xiàn).
??????限制條件:
?????????1. tbMain,tbForeclose,tbDetail三表分別存在於不同的DB, 因DB所屬系統(tǒng)各不相同, 線上環(huán)境則會(huì)有可能布署在不同DBServer上.
?????????2. tbMain記錄筆數(shù)極大,索引效率低下.
?????????3. 三張TABLE是屬於其他的系統(tǒng), 本次需求不允許進(jìn)行UPDATE的操作.
??????因此, 需要對(duì)應(yīng)解決.
??????對(duì)於限制條件1, 資料處理必須依3個(gè)步驟進(jìn)行(見圖上A,B,C), 如圖1:
????????????????????????
??????對(duì)於限制條件2, 主要卡在步驟A的超時(shí)上(其後都僅處理到少量資料), 實(shí)測(cè)時(shí)在本機(jī)的查詢分析器RUN步驟A的SQL就要10分鍾,符合的資料筆數(shù)為百萬左右.除非是將ADO連線設(shè)為不超時(shí), 否則一定會(huì)查詢失敗.
??????我首先的想法就是想要提高查詢效率.?
?????????. 經(jīng)檢查tbMain發(fā)現(xiàn)pID列上有索引而pName上卻無, 所以將步驟A進(jìn)行分解, 僅查詢符合的pID, 實(shí)測(cè)步驟A1可以在10分鍾以內(nèi)完成, 見圖2.
???????????????????????????
?????????. 嘗試減少步驟A1的處理筆數(shù), 比如先依pID的前兩碼進(jìn)行分組, 再進(jìn)行多次查詢, 實(shí)測(cè)資料量最大的一組pID約在2-3分鍾, 單次資料量減少到十萬.
??????通過測(cè)試的結(jié)果可以看到該問題僅通過改善/分解查詢已經(jīng)不能實(shí)現(xiàn)解決.?
??????那麼是否能夠?qū)⒉襟EA的筆數(shù)減少到一筆呢?
?????????. 因三檔都不允許進(jìn)行UPDATE的操作, 在處理完TOP 1的一筆後因無法對(duì)處理過的pID進(jìn)行標(biāo)識(shí), 無法獲取下一筆的資料.
??????既然多種方法都不可行, 那麼就只有完全改變處理的流程, 通過多添加一張表(專為此開發(fā)而新增的TABLE)並將消耗最大的步驟A交由DB內(nèi)部來完成, 最終解決方法步驟如下:
??????1. 先手工新增臨時(shí)TABLE
??????
--新增臨時(shí)檔
Create?Table?tbTemp?(
????aID?int?identity(1,1),
????pID?varchar(10)
); ??????2. 如果是需要多次RUN的話, 則要先Truncate Table tbTemp, 然後通過Insert Into tbTemp Select....在DB內(nèi)部完成資料的篩選, 由於在客戶端不需要撈一筆資料, 所以耗時(shí)非常的短, 達(dá)到秒級(jí).
??????3. 由於新增檔案是有KEY值(Identity), 所以只要拿到批次添加的最大/最小ID值即可通過循環(huán)來逐筆處理了.
??????最終解決方案如圖3
??????????????????????????????
???????????????????????????
|