優化報表取數

1.1 取數原理

設計器拼出最終的 SQL，將 SQL 語句傳給資料庫，資料庫執行，將資料傳回給設計器。

由於計算程式首先要透過 SQL 語句從資料庫中取資料，我們可以透過控制資料量的大小和對資料的提前預處理來提高報表的效能。

1.2 取數優化

報表的核心是資料，資料集是否合理決定報表的品質。     

1）每張報表都應該有一個主資料集，為了降低維護時的工作量，儘量將所有欄位置於主資料集，除非在某些情況下，不使用多源資料集會導致主資料集異常複雜。

2）在製作報表之前，儘量考慮到所有需要展示的資料欄位，在資料庫軟體中，合理編寫 SQL 語句，大數據情況儘量對 SQL 做優化，以及新增索引。

本文將詳細介紹如何優化報表取數。

FineReport 報表的資料集採用的是表模型，也就是說透過 SQL 這種 DSL 語言，從資料庫透過簡單查詢或各種組合聯動查詢得到一個關係表，而這部分 SQL 查詢根據各種資料庫廠商長時間的優化（比如建立索引），已經非常成熟。

資料集一般要透過 FineReport 報表模型的複雜處理才能生成最終的表樣。因此，從資料庫 SQL 查詢取出資料量越少，FineReport 報表模型需要做的複雜處理和計算就越少，所花的時間和記憶體就少，進而可以提高效能。

2.1 SQL 語句取具體的欄位

我們一般會用select * from 這樣的形式將一個資料庫表中所有的欄位都取出來，而其中一些欄位是報表中不需要用到的，例如報表中只需要用到三個欄位，但是資料庫中實際的表有十個欄位，一些初學者習慣性的用select * from table1，這樣相當於把十個欄位的資料都取到報表伺服器端，增加了報表伺服器端的記憶體佔用以及減慢了運算速度，所以 SQL 語句中儘量不要用“*”號，而是寫上具體的欄位，能夠減少報表伺服器端的記憶體佔用，加快報表的運算速度。

2.2 SQL 中直接分組代替報表中分組

一些匯總類型的報表，例如製作一張訂單總額的表，可能會從訂單明細表中取出大量的資料記錄，然後進行資料匯總，即進行分組聚集運算，報表計算程式中我們可以在 SQL 中提前進行一次分組聚集，能夠大大減少取到的報表伺服器的記錄數，加快取數和報表運算的速度。

SQL 語句：SELECT 成本價,類別ID FROM 產品

從資料庫中選擇如上兩個欄位，然後根據類別 ID 進行成本價的匯總，此時資料庫傳回給報表處理的資料就有 76 條。如下：

優化的 SQL 語句：SELECT sum(成本價),類別ID FROM 產品 group by 類別ID

經過 SQL 優化後，報表需要處理的資料就只剩 8 條了。如下：

優化分析：

• 第一種做法，不僅僅取到報表伺服器上記錄數多了，取數速度慢，而且報表模型需要對錶資料欄進行分組運算，增加了報表運作時間。

• 第二種做法，資料庫雖然要進行分組運算，但是資料庫中有索引，運算速度快，且取到報表伺服器端的記錄數大大減少，取數速度大大加快，因此在報表模型進行分組運算的時候只要對很少的記錄數進行，報表的運算速度大大加快了。

實驗結果以及分析表明，第二種做法的效能遠優於第一種。所以，分組應該儘量在 SQL 裏進行。

2.3 SQL 中直接排序代替報表中排序

報表計算程式中很多時候需要對資料進行排序，雖然排序運算可以在報表端進行，不過我們還是建議在 SQL 中提前將資料排序，這是因為資料庫中的索引功能，通常是 C/C++ 語言(往往在效率上比 Java 好)寫的，會使得排序運算的速度更快。

2.4 SQL 中直接過濾代替報表中過濾

報表計算程式中很多時候並不需要對錶中的所有記錄進行操作，而只是需要對部分滿足條件的記錄進行操作，雖然可以在報表設計器中對資料過濾，不過我們建議在 SQL 中對資料提前過濾，這樣資料庫傳回的資料就減少了，既加快了取數速度，也加快了報表的運算速度。

檢視表是指計算機資料庫中的檢視表，是一個虛擬表，其內容由查詢定義。同真實的表一樣，檢視表包含一系列帶有名稱的列和行資料。但是，檢視表並不在資料庫中以儲存的資料值集形式存在。行和列資料來自由定義檢視表的查詢所引用的表，並且在引用檢視表時動態生成。

儲存程式透過流控制與 SQL 語句，可以對資料進行強大的運算與處理，對於業務比較複雜的應用，常常需要將原始資料透過儲存程式處理後再供報表使用。另外儲存程式運作前，資料庫會對其進行文法和文法的分析，並進行優化，這種已經編譯好的儲存程式極大地改善 SQL 語句的效能。在報表端也只需要書寫較短的呼叫語句來獲得結果，進而降低網路的通訊量。

所以表與表的連結、複雜的 SQL 儘量在資料庫中使用檢視表或者儲存程式直接進行，這樣將複雜的 SQL 語句直接儲存於資料庫伺服器端（資料庫本身會對 SQL 語句進行文法分析並進行優化），在報表設計器端就不需要寫大段的 SQL 語句而是直接呼叫檢視表或儲存程式了，一方面減少網路傳輸量，減輕資料庫的壓力，另一方面加快了報表的運算速度。

1）SELECT 子句中避免使用 “*”  

當你想在 SELECT 子句中列出所有的 columns 時，使用動態 SQL 列引用‘*’是一個方便的方法。不幸的是，這是一個非常低效的方法。 實際上，Oracle 在解析的程式中, 會將“*” 依次轉換成所有的列名, 這個工作是透過查詢資料字典完成的, 這意味着將耗費更多的時間。  

2）刪除重複記錄

最高效的刪除重複記錄方法 ( 因為使用了 ROWID)

DELETE FROM EMP E WHERE E.ROWID > (SELECT MIN(X.ROWID) FROM EMP X WHERE X.EMP_NO = E.EMP_NO)

3）用 TRUNCATE 替代 DELETE

 當刪除表中的記錄時，在通常情況下，回滾段（rollback segments）用來存放可以被恢復的資訊。如果你沒有 COMMIT 交易，Oracle 會將資料恢復到刪除之前的狀態（準確地說是恢復到執行刪除命令之前的狀況），而當運用 TRUNCATE 時, 回滾段不再存放任何可被恢復的資訊。當命令運作後，資料不能被恢復。因此很少的資源被呼叫，執行時間也會很短。

4）計算記錄條數

 和一般的觀點相反count(*) 比count(1)稍快，當然如果可以透過索引檢索，對索引列的計數仍舊是最快的。例如 COUNT(EMPNO) 

5）用 EXISTS 替代 IN

在許多基於基礎表的查詢中，為了滿足一個條件，往往需要對另一個表進行聯接。在這種情況下，使用 EXISTS 或 NOT EXISTS 可以提高查詢的效率。例如：

低效：

SELECT * FROM EMP WHERE EMPNO > 0 AND DEPTNO IN (SELECT DEPTNO FROM DEPT WHERE LOC ='MELB')

高效：

SELECT * FROM EMP WHERE EMPNO > 0 AND EXISTS (SELECT ‘X’  FROM DEPT WHERE DEPT.DEPTNO = EMP.DEPTNO AND LOC = 'MELB')

6）用 EXISTS 更換 DISTINCT  

當提交一個包含一對多表資訊( 比如部門表和僱員表 )的查詢時，避免在 SELECT 子句中使用 DISTINCT， 一般可以考慮用 EXIST 更換。例如

低效:     

SELECT DISTINCT DEPT_NO,DEPT_NAME  FROM DEPT D,EMP E WHERE D.DEPT_NO = E.DEPT_NO 

高效:  

SELECT DEPT_NO,DEPT_NAME  FROM DEPT D WHERE EXISTS ( SELECT ‘X’ FROM EMP E WHERE E.DEPT_NO = D.DEPT_NO)

7）用 >= 替代 >

例如 DEPTNO 上有一個索引： 

高效：

SELECT *  FROM EMP  WHERE DEPTNO >=4  

低效：

SELECT *  FROM EMP   WHERE DEPTNO >3

兩者的差別在於, 前者 DBMS 將直接跳到第一個 DEPT 等於 4 的記錄。而後者將首先定位到 DEPTNO=3 的記錄並且向前掃描到第一個 DEPT 大於 3 的記錄。

8）應儘量避免在 where 子句中對欄位判斷

例如：

select id from t where num is null

可以在 num 上設定預設值 0，確定表中 num 列沒有 null 值，然後這樣查詢：

select id from t where num=0

9）應避免在 where 中使用 != 或 <> 運算子

將引擎放棄使用索引而進行全表掃描。優化器將無法透過索引來確定將要命中的行數，因此需要搜尋該表的所有行。

10）應避免在 where 子句中使用 or 連結

否則將導致引擎放棄使用索引而進行全表掃描，例如：

select id from t where num=10 or num=20

可以這樣查詢：

select id from t where num=10 union all select id from t where num=20

11）in 和 not in 也要慎用

因為 IN 會使系統無法使用索引，而只能直接搜尋表中的資料。例如：

select id from t where num in(1,2,3)

對於連續的數值，能用 between 就不要用 in 了：

select id from t where num between 1 and 3

12）應避免在 where 中進行運算式操作

這將導致引擎放棄使用索引而進行全表掃描。例如：

• SELECT * FROM T1 WHERE F1/2=100應改為：SELECT * FROM T1 WHERE F1=100*2

• SELECT * FROM RECORD WHERE SUBSTRING(CARD_NO,1,4)='5378'應改為：SELECT * FROM RECORD WHERE CARD_NO LIKE '5378%'

• SELECT member_number, first_name, last_name FROM members WHERE DATEDIFF(yy,datofbirth,GETDATE()) > 21應改為：SELECT member_number, first_name, last_name FROM members WHERE dateofbirth < DATEADD(yy,-21,GETDATE())

注：任何對列的操作都將導致表掃描，它包括資料庫函式、計算運算式等等，查詢時要儘可能將操作移至等號右邊。

13）應避免在 where 子句中進行函式操作

這將導致引擎放棄使用索引而進行全表掃描。例如：

• name 以 abc 開頭的 id：select id from t where substring(name,1,3)='abc'應改為：select id from t where name like 'abc%'

• '2005-11-30'生成的 id：select id from t where datediff(day,createdate,'2005-11-30')=0應改為：select id from t where createdate>=’2005-11-30′ and createdate<'2005-12-1'

14）不要在 where 中的“=”左邊運算

進行函式、算術運算或其他運算式運算，系統將可能無法正確使用索引。

15）儘量避免向用戶端傳回大數據量

若資料量過大，應該考慮相應需求是否合理。

16）避免使用不相容的資料類型

例如 float 和 int、char 和 varchar、binary 和 varbinary 是不相容的。資料類型的不相容可能使優化器無法執行一些本來可以進行的優化操作。例如:

SELECT name FROM employee WHERE salary > 60000

在這條語句中，如 salary 欄位是 money 型的，則優化器很難對其進行優化,因為 60000 是個整數型態數。我們應當在編程時將整數型態轉化成為錢幣型，而不要等到運作時轉化。

17）充分利用連結條件

在某種情況下，兩個表之間可能不只一個的連結條件，這時在 WHERE 子句中將連結條件完整的寫上，有可能大大提高查詢速度。例如：

SELECT SUM(A.AMOUNT) FROM ACCOUNT A,CARD B WHERE A.CARD_NO = B.CARD_NO

SELECT SUM(A.AMOUNT) FROM ACCOUNT A,CARD B WHERE A.CARD_NO = B.CARD_NO AND A.ACCOUNT_NO=B.ACCOUNT_NO

第二句將比第一句執行快得多。

18）能用 GROUP BY 就不用 DISTINCT

SELECT DISTINCT OrderID FROM Details WHERE UnitPrice > 10

可改為：

SELECT OrderID FROM Details WHERE UnitPrice > 10 GROUP BY OrderID

19）能用 UNION ALL 就不要用 UNION

UNION ALL 不執行 SELECT DISTINCT 函式，這樣就會減少很多不必要的資源。

20）儘量不要用 SELECT INTO 語句

SELECT INTO 語句會導致表鎖定，阻止其他使用者存取該表。