insert処理はまとめて実施する(バルクインサート)
大量のデータをfor分で一つずつ追加するよりも,一つにまとめて実施した方が効率がいい.バルクインサートと呼ばれます。
当たり前ですが,1つのトランザクションごとにトランザクション管理(ロック,ログ生成等)が発生するため,一つ一つ実施するよりも1つのトランザクションにまとめたほうが効率は上がります.
ただし,これはトランザクションで管理されるべき粒度(ロックの粒度)がありますので,状況によりけりです.
テストデータベースを作製したい時など,トランザクション管理が必要ないときは一つにまとめたほうが効率的です.
カラム名に型と同一の名称は使用しない
例えばtimestampの方を持つところにカラム名をtimestampとするのは避けるべきです。理由としては、
- クエリが読みにくくなる
- IDEの入力補助の恩恵を受けにくくなる
です。timestampであればcreated_at, updated_at等、より実態に適した名称を使用すべきです。
DB側処理 vs アプリ側処理
パフォーマンス面から
SQLを使用すれば殆どの処理をDB側に委譲できますが、果たしてどこまでの処理をDB側で実施し、アプリ側で実施すればいいのか。
一つの処理に対するシステム全体の応答性で考えた場合には、可能な全ての処理をDB側で実施した方が効率化できます。DBとアプリケーション間のメモリ情報授受が一番大きなボトルネックだからです。
とはいっても、現実は複数処理が同時にDBに投げられるので、ロックを考慮したDBのスループットを意識する必要があります。DB側で負荷の大きい処理を実施してしまうと不必要に長いロック時間を取ってしまい、ある処理時に応答性が低下します。
よって、結局どの程度のクエリがDBに投げられるかの見込量で適切な処理区分は異なり、明確な単一方針はありません。
ただ広く適用可能な結論を出すとすれば、レコードを選択する処理はDB側で実施し、選択されたレコードに対する追加処理(sort)はアプリ側で実施するのが良さそうです。sort自体は比較的負荷の大きい処理ですので、それをアプリ側に委譲することでスループットの向上が見込めます。
規約の面から
どこまでの処理をDBに委譲するかはプロジェクトごとに指定されていることもあると思います。指定された決定背景としては、
- コードメンテナンス性
- 責務の分離
が考慮されています。
大人数のプロジェクトの場合、比較的単純な処理のみDB側で実施する、という方針にしない場合、無秩序に類型のプロシージャが作成され、手に負えなくなることが予見されます。これには勿論、SQLの記述方法が比較的自由度があること、SQLはアプリケーション使用の言語よりも習熟率が低い、という理由も含まれます。
プロジェクトを開始する前に設計規約として規定しておくことが重要です。