一.概要
帳票の中心がデータであり、データセットの合理性で帳票の品質が決まります。
1.帳票ごとにメインデータセットを一つ持たせるべきです。保守時の作業量の軽減を念頭に、全てのフィールドをメインデータセットにおいてみましょう。一方、マルチデータセットを使用しないとメインデータセットが非常に複雑になるという場合は除きます。
2.帳票作成の前に、表示したいすべてのデータフィールドを考慮したうえで、データベースソフトにおいて合理的なSQL文を考えましょう。データ量が多い場合、SQLをできるだけ最適化し、インデックスを添えてみましょう。
3.高性能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文の使用で表がロックされ、ほかのユーザーからのアクセスができなくなります。