超大資料表分頁的效能問題
- 大家好,小弟有一個很頭痛的分頁效能問題想請教一下大家是否有較好的解決方案:
環境說明
Root Table Layer 1 Table Layer 2 Table Leaf Table
RT1, RT2... L1T1, L1T2.... L2T1, L2T2.... LFT
Root Table可以包含很多Layer 1 Table
Layer 1 Table可以包含很多Layer 2 Table
Layer 2 Table可以包含很多Leaf Table
所有的詳細資料都在Leaf Table (例如說所有客戶的詳細資料),其他的像是Root, Layer 1和Layer 2 Table都只是所謂的lookup table,基本上其內容就只有紀錄所包含的primary key而已.
現在我必須查詢例如說RT1到底包含了多少客戶(且要詳細資料),所以勢必要從RT1一路join Layer 1, Layer 2和Leaf Table. 但是因為上層Table可以重疊包含到下層的Table,例如說
RT1包含了L1T1,L1T2
L1T1包含了L2T1, L2T2, L2T3
L1T2包含了L2T2, L2T3,L2T4
造成了很多重複的情況,所以join最後的結果必須用distinct去排除重複的部分,然後用分頁的方式讓前端程式可以選擇每頁要秀幾筆資料,要秀第幾頁,所採用的方式如同這裡所述. (SQL 2005)
但是因為最後的Leaf Table資料有一百萬筆以上(且會持續增加),為了效能上的考量所以只join各資料表的parmary key,又因為ROW_NUMBER()必須要有東西讓他去order by,又無法order by primary key(因為primary key是GUID並沒有任何意義,所以基本上是order by customerName),所以必須在分頁功能之前把所有資料依照最後所得到的primart key list從Leaf Table給全部取回之後才能分頁.
如果每頁顯示100筆資料,每取回一頁大約花費20秒,愈到後面的頁數愈慘,每頁大約要花上40多秒才能取回. 這可是很難讓客戶接受的...
我原本的構想是分頁也是用primart key list去分頁,等到取得那最後要秀出的100筆primart key之後才去取回詳細的資料然後回傳給前端,想必效能可以提升很多,問題就在於ROW_NUMBER()要有東西去排序,用primart key(GUID)去排序所取得的第n頁資料絕對和用customerName去排序所取得的第n頁資料不同,這可讓我傷透了腦筋.
不知道各位達人有沒有什麼更好的建議呢? (例如說用不同的query,或是用不同的方式去分頁...等等)
PS:其實單純的join各資料表並沒有花多少時間,問題是distinct卻花了不少時間.
解答
你的索引是一般索引還是 Clustered 的索引?
如果是 Clustered 索引，那跟沒有其實沒兩樣 ...
如果用了索引還慢，那可能是索引的設法不好，或是 I/O (Disk I/O) 本身就不夠快。
Disk I/O 的話，可能需要做 Disk Defragment 或是換一顆更快的硬碟。
如果你公司有人在做 DBA，這應該是 DBA 在做的事，而不是由你來做。
Paged Query Comparsion between SQL Server 2000 and SQL Server 2005:
所有回覆
用XML 去放資料吧
1. 有時候在說明的時候，一圖勝千言。
2. 不知道你如何寫的，文字上看起來很亂。
3. 如果是過於複雜的 query，可以利用 snapshot table (把查詢的結果存成一個快照表)，程式直接利用這個快照表即可。
抱歉說得太複雜了,簡而言之就是要join root table, layer 1 table, layer 2 table和最後的leaf table以得到最後想要的資料,然後應用分頁技術讓前端查詢，可是因為資料太大導致sql server在執行這個簡單的query時耗掉太多時間(也就是效率太差),所以想說有沒有什麼辦法可以改善這個情況。這個query大略如下
select distinct lT.* into @tempTable --@tempTable是之前已經宣告好的table變數
from rootTable rt
inner join layer1Table l1t on rt.PID = l1t.PID
inner join layer2Table l2t on l1t.BID = l2t.BID
inner join leafTable lT on l2t.CID = lT.CID --這個leafTable非常大
where rt.RID = @RID --@RID是前端所輸入想要查詢的根ID
--接下來開始套用分頁技術
DECLARE @StartRow INT
DECLARE @EndRow INT
SET @StartRow = 120
SET @EndRow = 140select *
from (
select *, row_number() over(order by t.customerName)
from @tempTable t) customerInfo
where RowNumber > @StartRow
and RowNumber < @EndRow
第一階段是瓶頸所在,join大table之後還需要distinct這個table導致效率很差。所以才在想說可否在第一階段只選取lT.CID存入@tempTable即可,分頁應用完之後知道要return哪些CID,然後才join leafTable去取得所需要的客戶詳細資料,如此應該大幅提升效率。
問題在於分頁使用到的row_number()這個function須要order by欄位,我如果只取CID,那勢必就只有CID可以排序,可是CID只是一個沒有任何意義的GUID,而前端需要的是依照customerName排序,所以才想來板上問說有沒有其他種類的SQL的分頁方法(不需要用到row_number()這個function的方法)來避開這個死結,或是我的query還有其他可以大幅改進效率的地方。
你這種方法很奇怪吧。
Code Snippetselect distinct lT.* into @tempTable --@tempTable是之前已經宣告好的table變數
from rootTable rt
inner join layer1Table l1t on rt.PID = l1t.PID
inner join layer2Table l2t on l1t.BID = l2t.BID
inner join leafTable lT on l2t.CID = lT.CID --這個leafTable非常大
where rt.RID = @RID --@RID是前端所輸入想要查詢的根ID
照理說，INNER JOIN 要在來源表和目的表有相同對應時才使用，而不是一值 JOIN 下去。
也就是：
Code SnippetSELECT ...
FROM srcTable INNER JOIN targetTable1 ON srcTable.Column = targetTable1.Column
INNER JOIN targetTable2 ON srcTable.Column = targetTable2.Column
INNER JOIN targetTable3 ON srcTable.Column = targetTable3.Column
....
但你變成：
Code SnippetSELECT ...
FROM srcTable INNER JOIN targetTable1 ON srcTable.Column = targetTable1.Column
INNER JOIN targetTable2 ON targetTable1.Column = targetTable2.Column
INNER JOIN targetTable3 ON targetTable2.Column = targetTable3.Column
....
這樣會有資料列虛增的問題吧，你仔細看一下你的結果集就知道了。
還有，你的查詢的 Lookup 有落在索引上嗎 (看執行計畫就可看出) ?
如果沒有，那慢是一定會的。
你可以利用子查詢法來切割你目前的 SQL，不然原本 leafTable 有一百萬筆，這樣子查詢下來，可能會輸出大於一百萬的資料。
花路路的,我只看到要分頁.
你這樣就用一個Table 然後用 Row_Number 直接切,效能可能比你切三層再Join 的好.
小朱 寫信: 你這種方法很奇怪吧。
照理說，INNER JOIN 要在來源表和目的表有相同對應時才使用，而不是一值 JOIN 下去。
這樣會有資料列虛增的問題吧，你仔細看一下你的結果集就知道了。
還有，你的查詢的 Lookup 有落在索引上嗎 (看執行計畫就可看出) ?
如果沒有，那慢是一定會的。
你可以利用子查詢法來切割你目前的 SQL，不然原本 leafTable 有一百萬筆，這樣子查詢下來，可能會輸出大於一百萬的資料。
是的，其實layer 1和layer 2 table都只是lookup table，其記錄著上層包含了哪些下層，所以必須一路join下去以得到最後所需的資料。同時因為下層資料可重複被不同的上層包含所以勢必產生許多重複的資料，所以結果也會如您所言可能會輸出大於一百萬筆的資料，所以我才會用distinct去移除重複。但是這個distinct是效能瓶頸所在...
根據執行計畫的結果，花在index match上的時間佔了90%
因為目前的query會在分頁之前把所有的詳細資料取回才進行分頁(因為分頁需要一個column name去排序)，所以我才在想說可否在分頁之後(知道要送哪些CustomerID之後)，才取出那些需要被送出的詳細資料。
也有可能我這個query還有可以改善的地方，必須請有經驗的人幫我指點一下~
好說 寫信: 花路路的,我只看到要分頁.
你這樣就用一個Table 然後用 Row_Number 直接切,效能可能比你切三層再Join 的好.
抱歉沒說清楚,前面寫的那些查詢都是在同一個stored procedure裡面.
我並沒有切割那些table,那些table是已經被(前人)設計好要如此,所以必須join這些table才能得到最後所需的資料.
因為資料太大,又需要distinc,排序和分頁導致效能直直落... 真頭痛
你的索引是一般索引還是 Clustered 的索引?
如果是 Clustered 索引，那跟沒有其實沒兩樣 ...
如果用了索引還慢，那可能是索引的設法不好，或是 I/O (Disk I/O) 本身就不夠快。
Disk I/O 的話，可能需要做 Disk Defragment 或是換一顆更快的硬碟。
- 是 Clustered的.
不過table的design, SQL server的Admin 那些部分似乎已經超出我這個資淺的.net developer所可以變動到的範圍，看樣子query這部分似乎是沒什麼方式改善?
想請問在SQL 2005的新功能row_number()出來之前,不知道大家都是用哪些方法在做分頁的功能? 可否給一些提示讓我去做一些search? 如果你公司有人在做 DBA，這應該是 DBA 在做的事，而不是由你來做。
Paged Query Comparsion between SQL Server 2000 and SQL Server 2005:
小朱這篇我打?
台灣人,全能人.
分頁叫DBA 做,別想了.
不要說DBA,光是網路有問題,叫網管查一下就有一推問題了,明明系統有病毒,他們就凹說是程式的問題.
所以你叫DBA 去做這件事,可能嗎?
好說 寫信: 小朱這篇我打?
台灣人,全能人.
分頁叫DBA 做,別想了.
不要說DBA,光是網路有問題,叫網管查一下就有一推問題了,明明系統有病毒,他們就凹說是程式的問題.
所以你叫DBA 去做這件事,可能嗎?
不論台灣的現實如何。
如果以專業分工的角度，分頁其實就是限制輸出和管制資料的流量，這件事不是 DBA 做，誰做？叫不懂 DB 的 Programmer 做，還是叫 System Administrator 來做？
沒錯，台灣的環境下，想不做全能的人很難，但以有 MIS 部門或 Developer Team 的公司來說，除非那個 DBA 是裝死，不然這種和資料庫有關的事本來就要叫 DBA 做，DBA 是負責開一個出入口給應用程式的人，分頁查詢可以做成 stored procedure，所以理所當然是由 DBA 做。雖然在台灣不太可能有，但不代表完全沒有。
想像一下，如果你的團隊中有 DBA，你不會把這工作丟給 DBA 嗎？
還是要全攬在自己身上，表示自己很行？
那只會累死你自己而已。
如果現實環境真的沒有 DBA，再來自己想辦法也來的及。
在台灣你所認定的角色為何?
程設師?
網管?
DBA?
當然大小公司不一.
我所認定的
程設師:聊天,上網,打屁,上色情網站,全公司網路流量花費最大,然後偷拐騙搶的東西做出來.
網管:裝裝電腦,掃掃毒,備備份,哪邊印表機卡紙就往哪裡去,哪邊美美多就往哪邊走.
DBA:小公司很少,要保證系統不當機,資料不Lock,每天燒好香,不要被急Call,什麼是正規化,有空在聯絡.什麼是程式,不關我的事.
你要怎麼認定，那是你的自由，但不要把你的認定套在別人身上。
你前面的回覆，就給人有這樣的感覺。
What's Programmer:
http://zh.wikipedia.org/w/index.php?title=%E7%A8%8B%E5%BA%8F%E5%91%98&variant=zh-twWhat's Network Administrator (or System Administrator) ?
What's Database Administrator?
http://en.wikipedia.org/wiki/Database_administrator
不好意思，你前面那三個角色，我都是一個人包。
Roadmap for Programming and Development:
http://www.microsoft.com/taiwan/msdn/columns/jhu_ming_jhong/rop.htm
至少，我可沒你說的那麼惡劣。
Sorry 可能是我語言表達不是很好.
但我一開始有 程設師? DBA? 就是說他們在你們心中的定義為何?
而我認定也不是說你們接受我的想法,而是說這在一般公司環境下的真實的一面(當然在MS,IBM等大型公司可能不同).
在我待過的公司,程式設計部門跟系統部門通常是不同部門,而DBA 通常不會有.
而這三個部門就跟我說過的賦予不同的認務跟責任,所以說你程式Run 起來有問題,其中可能是頻寬的問題,或者其它的系統問題,你就叫系統人員來處理,基本上誰理你,主管間還是有的喬.
而就DBA,就這個字,就代表他是管理人員,也就是什麼系統備份...管理認務,所以坦白說設計還不是他的強項,所以有這方面的問題也不會想到他.而就以前MS程式認證,MCAD,MCSD裡都有資料庫設計這一部分.而程設前端/後端(程式/資料庫)是一體二面,你前端怎麼設計,後端就互相配合,老實說在程設部門怎麼清楚的你的想法很明確的傳達給別人就有一點困難了,更何況怎麼傳達到設計理念可能不同的DBA身上.
所以以目前來看,都是By Case ,全部自己包.
DBA 本來就是為了要管理資料庫所設的職位。
管理有很多種，但回到管理的本質來看：
-
規劃 (Planning)：DBA 需要會處理資料庫和資料庫運行環境的規劃，所以像是資料庫正規化，環境的設定，Recovery Planning 和 High Availability Planning 這些，不可能叫系統管理員去做吧。
-
組織 (Organize)：DBA 要有能力駕馭 DBMS，熟悉 DBMS 中的各項功能。
-
領導 (Lead)：DBA 在資料庫中是最具權力的，任何應用程式或使用者想要存取資料庫，得要先經過 DBA 這一關，以及存取權限的設定。也就是說，能夠直接以人工方式存取資料庫的，應該只有 DBA，連系統管理員都不行。
-
控制 (Control)：由 DBA 發展介面，打開資料庫的管道，讓應用程式可以經由這個管道來存取資料庫，而 Paged Query 也是這個介面的其中之一。其他還包括 Reference Integrity Constraints，Stored Procedure 或是 UDF, Custom Types 等等都是要由 DBA 來處理。
網路問題不是 DBA 在管的，那是網管人員在做的事。
DBA 和 Development Team 溝通，都是靠 Data Dictionary, Schema 和陳述式來做的，Development Team 如果需要，應該開立像查詢資料庫的規格（資料表，查詢欄位，條件，分頁與否等等），交給 DBA 來做，除非 DBA 太忙，授權由 Developer 自己處理（通常是給一個 View，不可能給 table），否則這些東西都要由 DBA 來做。
要求再嚴格一點，DBA 可以說是公司資料庫的守門員，其他人（就算是董事長或 CEO）通常也是不能直接去碰，但它也是資料庫的醫生，資料庫一有問題，DBA 就要負責去治它（Recovery or Repair），而不是叫系統管理員去治。
Developer 本來就要會資料庫，不然怎麼寫 ADO.NET？
至於設計理念，在專案一開始時 DBA 和 Developement Team 就要把資料格式敲定，至少要在 Logical Design 這個階段就要抵定，而在 Physical Design 或 Production 時，若想要改 schema，就得要看 DBA 的臉色了。
-
小朱 寫信: 如果你公司有人在做 DBA，這應該是 DBA 在做的事，而不是由你來做。
Paged Query Comparsion between SQL Server 2000 and SQL Server 2005:
感謝! 原來線索就在同一個網站,真不好意思~
無意間引起大家的爭論也非我本意,請見諒.
