一.概要
データは帳票の中心であり、データセットの合理性で帳票の品質が決まります。
1.帳票ごとにメインデータセットを一つ持たせるべきです。保守時の作業量の軽減を念頭に、全てのフィールドをメインデータセットに設置しましょう。一方、マルチデータセットを使用しないとメインデータセットが非常に複雑になるという場合は除きます。
2.帳票作成の前に、表示したいすべてのデータフィールドを考慮したうえで、合理的なSQL文を考えましょう。データ量が多い場合、SQLをできるだけ最適化し、インデックスを添えてみましょう。
二.データ取得の原理
デザイナがSQLを生成し、SQL文をデータベースに送信します。データベースソフトがそれを実行し、データをデザイナまでフィードバックします。
計算の過程で、SQL文を介してデータベースからデータを取得することが最初の一歩で、データ量の調整とデータの前処理によって帳票の性能を向上させることができます。
三.SQL文の最適化
FineReport帳票のデータはテーブルモデルを使用します。つまり、SQL言語を通して、簡易クエリまたは結合クエリでデータベースからテーブルを取得します。データベース各社の長い間の最適化（インデックスの構築など）で、このようなSQLクエリは非常に発達しています。一方、取得されたデータは、FineReport帳票モデルによる大量の処理が必要となります。したがって、データベースでのSQLクエリで抽出したデータが少ないほど、FineReport帳票モデルによる必要な処理や計算量が低減され、所要時間とメモリの負担も少なくなり、性能が上がります。
1.SQL文から特定なフィールドを抽出する
select * fromでデータベースのテーブルからすべてのフィールドを抽出すると、不要なフィールドが負担となります。例えば、帳票に3つのフィールドだけ必要となっているものの、データベースのテーブルには10のフィールドがあります。初心者なら習慣的にselect * fromを使用しますが、それでは10のフィールドのデータをすべて帳票サーバーに抽出することになり、結果として帳票サーバーのメモリ使用が上昇し、計算速度が落ちてしまいます。このように、SQL文の際、できるだけ「*」ではなく、具体的なフィールドを書き込むことで、帳票サーバーのメモリ占有を削減し、帳票の計算速度を引き上げることです。
2.帳票内のグループ分けの代わりにSQLでグループ分けを行う
一部の集計型帳票では、例えば注文総額の表を作成する場合、注文明細表から大量のデータ記録を抽出して集計を実行することになります。つまりグルーピング集計の計算です。帳票計算の過程で、SQLで先にグルーピング集計を実施できます。それで帳票サーバーに送られるレコード数が大幅に削減され、データ取得と帳票計算の速度を引き上げられます。
SQL文：SELECT Cost_price, CategoryID FROM Product
データベースからフィールドを2つ選んで、種類IDでコストを集計する際、データベースからフィードバックするデータが76に上ります。具体的には下記の図をご覧ください。
最適化されたSQL文：SELECT sum(Cost_price),CategoryID FROM Product group by CategoryID
SQLの最適化の結果、帳票サーバーが処理するデータは8行だけになりました。詳しくは下記のとおりです。
分析：
方法一：帳票に送られるレコード数の増加でデータ取得が遅れるだけでなく、帳票モデルでデータ列のグルーピング計算を行うことになったため、帳票の実行はさらに時間がかかってしまいます。
方法二：データベースではグルーピング計算をしますが、インデックスがあるから速くできます。しかも帳票サーバーに抽出されるレコード数が大幅に削減されたため、データ取得がかなり速くなります。このように、帳票モデルにおけるグルーピング計算ではごく少ないレコードを対象に実行することになり、帳票の計算が大幅に加速できます。
実験と分析の結果、方法一より方法二のほうの性能がはるかにが優れています。したがって、グルーピングはできるだけSQLで行ったほうがいいです。
3.帳票内のソートの代わりにSQLでソートを行う
帳票の計算の中で、データソートが必要な場合があります。帳票でもソートが可能ですが、SQLの段階でソートを行うことを提案します。データベースのインデックス機能にC/C++言語(効率上Javaより優れる)が使われるため、ソート計算がより速くできるからです。
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.DELETEの代わりにTRUNCATEを使う
表の中のレコードを削除する時、通常の場合、復元可能なデータはロールバックセグメント(rollback segments) に保存されます。そして、COMMITトランザクションを設定しなければ、Oracleが削除命令が実行される前の状態に復元します。一方で、TRUNCATEを使用する際、ロールバックセグメントに復元可能なデータを保存することは一切なくなり、命令実行後のデータの復元も不可能になるめ、呼び出されたリソースがかなり少量になり、実行の時間もだいぶ短縮されます。
4.レコード数を計算する
通論と逆に、count(*)はcount(1)より速いです。ただしインデックスで検索できるテーブルは、COUNT(EMPNO)など、インデックス列に対する計算は一番速いです。
5.INの代わりにEXISTSを使う
別のテーブルと結合しなければならないことがあります。この場合、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.DISTINCTの代わりにEXISTSを使う
テーブルをまたがる(例えば部門表と職員表)クエリを設計する場合、SELECT文におけるDISTINCTの使用の代わりに、EXISTを使用してみましょう。例えば、
低効率
ELECT 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)
EXISTSの使用でクエリが加速されます。
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の使用を避ける
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,dateofbirth,GETDATE()) > 21
は、次のように最適化しましょう。
SELECT member_number, first_name, last_name FROM members WHERE dateofbirth < DATEADD(yy,-21,GETDATE()) つまり、データベース関数や表現式の計算などいかなる列操作も表全体のスキャンを引き起こすので、クエリの際はできるだけ=の右側で操作文を書き込むことです。
13.where文での関数操作を避ける
関数の操作でエンジンがインデックスを使用せずに、そのまま表全体のスキャンしなければならないからです。例えば、
select id from t where substring(name,1,3)='abc'
：name abcから始まるid
select id from t where datediff(day,createdate,'2005-11-30')=0
：2005-11-30に発行されたid
以上のSQL文は、次のように最適化しましょう。
select id from t where name like 'abc%'
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フィールドが通貨型の場合、60000は整数型の数値であるため、オプティマイザによる最適化の実行が難しくなります。実行の時ではなく、プログラミングの段階で整数型をmoney型に変換しましょう。
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文の使用で表がロックされ、ほかのユーザーからのアクセスができなくなります。