一次sql server最佳化經曆

最近在搞一個ERP系統裡面的報表，這個報表中的大列是統計性的，比如出貨率，採購百分比等等，這就意味著不能簡單的串連某幾個表，取幾個基礎資料表的資料就能獲得報表的資料，而是對幾個表進行串連，然後再計算，或者更不幸的，經過一輪計算後，得出的結果再去另外的表的資料進行計算。而我的報表中，需要一輪計算產生的欄位大概佔40%，需要串連，計算然後再計算的，大概有50%，而能直接獲得的，只有兩三個列。

起初，我是直接寫查詢語句，企圖通過一個查詢就搞掂。但由於邏輯太複雜，語句中充斥著大量的case when語句不說，裡面還有N層的子查詢，非常難看懂。而且有些列就根本無法得到。

 為了使查詢的思路清晰化，我使用了暫存資料表作為中間表的方法。我分析了報表，將可以一同計算出來的一些列計算出來，並儲存到暫存資料表中。然後將這些暫存資料表再作一次合并，並計算出各個列資料。這樣，查詢邏輯是比較明確了。這時候，大概有6個暫存資料表。

最初查詢的時候，大概10-20秒鐘。其實對於報表這類統計性非常強的資料來說，10來20秒算是可以容忍，畢竟報表不是經常用，匯出的時候就忍忍唄。但是不久後又一個問題出現了：伺服器記憶體消耗嚴重。估計是查報表的時候，暫存資料表太多了，逐漸出現了逾時問題。資料量少的話，使用暫存資料表存放並沒有什麼不妥，但是如果資料量一多，記憶體佔用就飆升了，如果導致了sql server太多的頁交換的話，還是得不償失的。

很一個多兩個星期的時間裡，我都還是主要通過尋求增加和最佳化索引，最佳化sql語句的方式來進行最佳化，但無奈，某些業務需求導致某些計算無法使用索引（主要是二次計算太多，我曾經甚至想過給暫存資料表添加索引）。

最後，即時的查詢並不能解決問題的情況下，我選擇另一種方式：使用預先計算的方式來在閒置時間做好報表的統計工作，然後放到一個表中，如果領導需要看報表，系統就去查詢那個表，而不是即時的去查詢基礎資料表計算資料。因為正如我所說，報表中大多是統計性的資料，而且對於即時性要求不是很高，完全可以在伺服器空閑先“偷偷”完成報表產生，

在供人查詢使用。我具體的是：建立一個作業，每天淩晨1點和中午13點的時候運行報表統計工作，存放在一個名叫“預統計表”的表中，並且這個表的結構和報表結構完全一下，盡量達到“select * from table”就可以得到報表的地步。然後通過分頁，最終的查詢速度不是一般的高。

原本，我是不太喜歡這樣的方式：既不那麼美觀，又增加了大量的冗餘資料。而且如果一開始就抱著這種思路來提高效能，往往會降低資料庫設計的範式，這無異於是一種“旁門左道”。但是，在這期間，我google了不少部落格，不少人都贊同一點：嚴格遵循範式的資料庫設計，並不一定就是好的設計。往往降低範式，適當增加冗餘度，提高查詢效能，也是一種除sql server本身提供的最佳化策略之外一種比較好的最佳化手段。