|
|
Posted on 2008-11-11 18:55 大大毛 閱讀(514) 評論(0) 編輯 收藏 所屬分類: SQL
問題?
??????這一次有同事在做一個小CASE時遇到問題無法解決. 整個的需求比較簡單, 是需要從資料庫中撈資料出來進行列印, 需求如下:
??????1. 從tbMain中找符合條件的客戶.限定條件:只有一筆資料的客戶
??????2. 查詢tbForeclose, 剔除掉某些客戶.剔除條件:姓名存在於tbForeclose中的客戶即要從名單中剔除
??????3. 從tbDetail中撈客戶的明細資料出來進行列印.關聯條件:依tbMain中客戶ID直接帶出即可
--
主檔(同一客戶會有多筆資料)
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
????
--
剔除客戶姓名
);
--
客戶資料明細檔
Create
?
Table
?tbDetail?(
????pID?
varchar
(
10
)?
primary
?
key
,????
--
客戶ID(KEY)
????pSex?
char
(
1
)?
not
?
null
????????
--
客戶其他詳細資料
);
?
示例數據
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記錄條數
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
'
????
--
添加資料
????
--
主檔添加筆數(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
????
--
客戶資料明細檔
????
Insert
?
Into
?tbDetail?
Values
?(
@pID
,
@pSex
)
End
--
隨機向剔除客戶檔添加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
嘗試及解決
??????就此需求表面來看, 實現起來很是簡單, 只要一條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
??????但此需求在開發時卻有遇到一些限制條件, 不能這樣去實現.
??????限制條件:
?????????1. tbMain,tbForeclose,tbDetail三表分別存在於不同的DB, 因DB所屬系統各不相同, 線上環境則會有可能布署在不同DBServer上.
?????????2. tbMain記錄筆數極大,索引效率低下.
?????????3. 三張TABLE是屬於其他的系統, 本次需求不允許進行UPDATE的操作.
??????因此, 需要對應解決.
??????對於限制條件1, 資料處理必須依3個步驟進行(見圖上A,B,C), 如圖1:
????????????????????????
??????對於限制條件2, 主要卡在步驟A的超時上(其後都僅處理到少量資料), 實測時在本機的查詢分析器RUN步驟A的SQL就要10分鍾,符合的資料筆數為百萬左右.除非是將ADO連線設為不超時, 否則一定會查詢失敗.
??????我首先的想法就是想要提高查詢效率.?
?????????. 經檢查tbMain發現pID列上有索引而pName上卻無, 所以將步驟A進行分解, 僅查詢符合的pID, 實測步驟A1可以在10分鍾以內完成, 見圖2.
???????????????????????????
?????????. 嘗試減少步驟A1的處理筆數, 比如先依pID的前兩碼進行分組, 再進行多次查詢, 實測資料量最大的一組pID約在2-3分鍾, 單次資料量減少到十萬.
??????通過測試的結果可以看到該問題僅通過改善/分解查詢已經不能實現解決.?
??????那麼是否能夠將步驟A的筆數減少到一筆呢?
?????????. 因三檔都不允許進行UPDATE的操作, 在處理完TOP 1的一筆後因無法對處理過的pID進行標識, 無法獲取下一筆的資料.
??????既然多種方法都不可行, 那麼就只有完全改變處理的流程, 通過多添加一張表(專為此開發而新增的TABLE)並將消耗最大的步驟A交由DB內部來完成, 最終解決方法步驟如下:
??????1. 先手工新增臨時TABLE
??????
--新增臨時檔
Create?Table?tbTemp?(
????aID?int?identity(1,1),
????pID?varchar(10)
); ??????2. 如果是需要多次RUN的話, 則要先Truncate Table tbTemp, 然後通過Insert Into tbTemp Select....在DB內部完成資料的篩選, 由於在客戶端不需要撈一筆資料, 所以耗時非常的短, 達到秒級.
??????3. 由於新增檔案是有KEY值(Identity), 所以只要拿到批次添加的最大/最小ID值即可通過循環來逐筆處理了.
??????最終解決方案如圖3
??????????????????????????????
???????????????????????????
|