NetBeans 8.2ではお手軽にSQLプロファイリングが取れるようになった。この新機能を紹介する。
SQLプロファイリングとは
SQLを発行した場合、そのSQLにかかった時間とパラメータ付きSQLの発行時に実際にどのようなパラメータが渡されたのかが表示される。呼び出しているメソッドへジャンプできるので、重いSQLを発見したらすぐ直せる。
使い方はプロファイルの種類で「SQL問い合わせ」を設定しておいてプロファイル実行するだけ。NetBeansを普段使っていないユーザーにも分かるよう具体的な操作を説明していく。
サンプルの動き
最初に10件データを挿入してその後、countを数える。insert文10回、select文1回の発行。
テーブル名はSAMPLE。
BIGINT型のid、VARCHARA型のname、TIMESTAMP型のdatetimeの3つのカラムを持つ。idが主キーだ。
package nb82;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Timestamp;
public class DBTest {
public static void main(String[] args) throws SQLException {
//コネクションの取得
Connection con = getConnection();
//10件サンプルを追加
{
PreparedStatement ps = con.prepareStatement("insert into SAMPLE (ID, NAME, DATETIME)values(? , ? , ?)");
long now = System.currentTimeMillis();
for(int i=0;i<10;i++){
Timestamp timestamp = new Timestamp(now + i * 1000);//1秒ずつずらす
insertRow(ps, i, timestamp);
}
ps.close();
}
//件数を数える
long count = insertCount(con);
System.out.println(count + "件");
con.close();
}
//コネクションの取得
private static Connection getConnection() throws SQLException {
String url = "jdbc:derby://localhost:1527/test";
String user = "test";
String pass = "test";
Connection con = DriverManager.getConnection(url, user, pass);
return con;
}
//件数を数える
private static long insertCount(Connection con) throws SQLException {
Statement stmt = con.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE);
ResultSet rs = stmt.executeQuery("select count(*) as count from SAMPLE");
long count = -1;
if(rs.next()){
count = rs.getLong("count");
}
rs.close();
stmt.close();
return count;
}
//データの挿入
private static void insertRow(PreparedStatement ps, int i, Timestamp timestamp) throws SQLException {
ps.setLong(1, i);
ps.setString(2, "name"+i);
ps.setTimestamp(3, timestamp);
ps.executeUpdate();
}
}
見てわかる通り例外処理はちゃんとやっていないので、あくまでもサンプルコード。また、誰でもすぐわかるようにtry with resourceもやっていない。わからない人もNetBeansが変換しろと電球だしてくるのでその辺使いながらチャレンジしてほしい。
まずはこれを一度普通に実行して10件が表示されること、データベースにデータが入っているかどうか確認しよう。
NetBeansならサービスタブからデータベースの中身を確認することができる。
接続設定をしてデータの表示を選択。
以下のSQLが自動で実行されて結果表示される。
当たり前だが、プログラムを再実行するとすでに同じIDがデータベース上に登録されているためエラーが出る。
ここでデータを消しておこう。
行を選択しておいて削除ボタンを押すだけでよい。
この程度ならdelete文を発行してもよいのだが、Excelのように表のデータを個別に気軽に更新できるので、いろいろとデータをいじったり積極的に利用するとよいだろう。テストには非常に役に立つ。
プロファイル実行までの手順
まず起点となるボタンはここ。
左にステップ実行などを行うデバッグボタン、さらに左に通常の実行ボタン、と３つならんでいるところだ。
プロファイリングの画面が表示されるので左上にあるプロファイルボタンの横にある▼ボタンを押そう。そこでSQL問い合わせを選択する。
そのあとプロファイルとかかれたボタン部分を押すと実行だ。
そうすると以下の画面が表示される。各SQLの実行時間が表示されているのがわかる。パラメータ付き問い合わせも、パラメータがちゃんと設定されているのがわかる。
下の方にフィルターがある。コマンドの種類や表やSQLで絞り込める。
1回目のinsertが遅いのはいろいろと初期化などがあるだろうから無視しよう。
ちなみにautoCommitをfalseに設定したところ以下のようになった。
SQL発行ではcommit自体の時間が入っていないので単純には比較はできないが、commitを含めた時間計測（プロファイルではなく通常実行）でもautoCommitはfalseにした方が予想通り早かった。
また、addBatchを利用して実行をするとさらにかなり高速になった。バッチ実行の場合はこういう表示になるようだ。1つのでかいSQLを投げているかのよう。
こちらもプロファイル実行ではなく通常実行でも計測してみたがcommitまでの時間を入れても一番早くなっていた。
このようにチューニングによって高速化の確認はできるものの、基本遅いクエリをコードを一切変更せずに全部探し出してくれるもの、という使い方が良いだろう。一塊としての処理の速度などはメソッドのプロファイルや通常実行の計測のほうがよい。
そのほか
ちなみにinsertRow()などJDBCメソッドの場合はプロファイルに出てこない。
なので今回のサンプルはinsert文に変更して作成した。
プロファイリングは統計を取り続けるため、重い処理だ。そのため、アプリの実際の時間はプロファイリング時はあてにはならない。
一方、SQLは別のサーバー／プロセスが解釈するものなので、割とあてになると思われる。そのため、RDBを利用しているアプリの場合この機能はかなり使い勝手が良いだろう。一度でよいのでプロファイルで実行してみるとよいだろう。
batchを使うなどすこしチューニングをしてみたが、この先は製品依存のチューニングとなる。例えば大量データの場合Derbyならバルクインポートを使うのが良いだろう。postgreSQLならバルクインサートやCOPYなど。製品依存部分は開発者によって知識の差が出やすいので、利用する際は目立つようにコメントを書いておこう。