JAVAとSQLと処理時間

◆実行環境・つかったもの
・Eclipse4.8 Photon
・Java8
・Postgres
　（ライブラリ：postgresql-42.2.4.jar）
・処理時間を計測するプログラム
　（ライブラリ：commons-lang3-3.1.jar、StopWatchクラス）

◆処理時間を計りたい（ストップウォッチ）時

org.apache.commons.lang3のStopWatchクラスが便利！

参照: Javaを簡単に計測する。(StopWatch) : Javaありがたや

使い方も簡単。インスタンス生成して、.start()と.stop()をして.getTime()するだけ。

 
◆問題と結果
①テーブル内のある項目の合計を計算するとき、
　SQLでやったほうが早いのか、JAVAでやったほうが早いのか、が知りたい

　→JAVAでやったほうが早かった！

②SQLで検索するとき、複数の主キーは、
　結合して検索した方が早いのか、それぞれで検索した方が早いのか、が知りたい

　→それぞれで検索した方が早かった！

◆詳細
＜調査方法について＞
下記方法にて調査を行う。

・処理の直前から直後までに、どのくらいの時間がかかったか計測する。
→org.apache.commons.lang3.time.StopWatchを利用。
　参照: Javaを簡単に計測する。(StopWatch) : Javaありがたや
　使い方も簡単。インスタンス生成して、.start()と.stop()をして.getTime()するだけ。

・問題で挙げたパターンで計測を行う。

・それらの実行結果が同じであることもassert()で確認すること。

＜①について＞
もともと、SQLでGROUP BYおよびSUMを行なって、日毎の金額合計をグルーピングしていました。
select date, count(1) as count, sum(amount) as amount from table group by date

ただ、amountについてはテーブルの型が文字列となっており、sumするには一度to_number()をしていたんです。
select date, count(1) as count, sum(to_number(amount, '99999')) as amount from table group by date

取得した結果は下記のようになります。

もともとのSQLで、時間のかかってそうなのはこんなところです。
・TO_NUMBER
・GROUP BY
・SUM
・COUNT
じゃあこれをJAVAに肩代わりしてもらってみよう、ということです。

こうしてSQLで集計しているところを、
SQLでは全データ取得にして、JAVA上で合計していく処理に書き替えました。
select date, amount from table
こんなシンプルなSQLにして（もちろん本当はもっと検索条件とかつけてますけど）、
JAVA上でLongなりBigDecimalなり変換してaddしていっただけです。

その結果、下記のようになりました。

！？　ばらけてる！
まぁ当然のごとく、1回2回じゃ実行環境の性能や体調にもよって変わってくるのは当たり前なので…（アホ）
何回かに増やして行なってみる。

うん、これなら納得する結果。
というわけで、JAVAの方が早かったです。

＜この結果を受けて考えられること＞
・「結果」欄を見ていると、回数が多いほど、1回当たりの処理時間の差が開いている。→けっこうそのときどきで処理時間が異なっている？
・「処理時間」欄を見ると、集計する件数は同じでも、テーブル内全件数が多いほど時間がかかっている。それも2倍以上に跳ね上がっている。→indexや、SQL文について再検が必要？
・ケース⑤については回数を多くして1回ごとの時間も計測してみたが、
　「処理時間」欄を見ると、1回最速と最遅の差が大きい。また、SQLよりもJAVAの方が差が大きい。
　　→JAVAで集計する場合は、実行環境によってより大きく変わる可能性がある。不安定。（※ローカル環境(Win10)だからという可能性もあるけど）
　　→SQLの方が、処理時間は安定する。
　　→ただし、A: SQL最速とJAVA最遅の差、B: SQL最遅とJAVA最速の差、比べるとA<Bであり、ここからもJAVAの方が早いことがわかる。

まぁ、性能や状況、テーブルの構成、データの状態、SQLの内容など、
いろいろな要因によって変わってくるのはもちろんのことなので、
この結果は大事なデータですが、これに囚われないようにしたいです。こういった比較調査は大事ですね。

また、JAVAでやったときはSQL実行とResultSet処理とで時間がそれぞれかかりますね。
それぞれどのくらいかかっているのか、も計測してみました。

JAVAでやったとき、思いのほかRSにかかる時間も少なかったです。

＜この結果を受けて考えられること＞
・「備考」欄に書いた処理時間参照元を見るに、1回目～3回目は一番時間がかかっている。→どちらも立ち上がりに時間がかかっている？
・「備考」欄に書いた処理時間参照元を見るに、SQLは4回目～5回目、JAVAは8～9回目、が一番早い。→SQLの方が慣れは早い？
・「処理時間」欄を見ると、SQL最速＜JAVA最遅となり、必ずしもJAVAが早いわけではない。
　　→何回も処理を行っていると全体的にはJAVAの方が早いが、SQLも極めれば早くなる可能性がある…？
　　→ただし、テーブル内全件が多ければ多いほど、両者の最速値の幅が大きい。
・「処理時間」欄を見ると、SQL実行時の時間がJAVAでやったほうが大幅に早い。RS（ResultSet）取得時の時間を合わせても早い。

まとめるとこんな感じです。

SQLが得意なこと
・テーブル内全件数が少ない場合は早い
・ResultSetにかかる時間がない
・処理時間が安定している（同じ処理ならほぼ同じ時間で処理できる）

JAVAが得意なこと
・テーブル内全件数が多くても早い
・ResultSetにかかる時間はデータ量による
・大量に処理する場合はSQLより早く集計できる

＜②について＞
これは、主キーのパラメータを外部から貰うとき、貰った時点で結合されているため
SQLにもそのまま使っていたのですが…
select hoNyara from table where key1||key2 = :param

こうするより、paramをkey1とkey2用にsubstringなりして検索した方が負荷は少ないのではということです。
負荷がかかりそうなのはもちろん「key1||key2」の部分です。

その結果、下記のようになりました。

結合での取得を確認(100回): 0.59300秒
分解での取得を確認(100回): 0.08600秒
<結論>
[ 分解 ]の方が早い(1回あたり0.00507秒)

分解した方が断然早かった。。
横着はするもんじゃないって、SQLが教えてくれてる気がする。。
まぁ、結合したもので一つのキーになってるんだったらもっと早いんでしょうけど、
今回はわかれているからこうだったということで。

◆結論

結論、JAVAに処理させた方が早かったです。
SQLを複雑にすると処理は遅くなりがちで、作業分担した方がよかったんですね。

ただ、「ことはやってみないとわからない」ですね…
いろんな要因で、遅くなったり早くなったりします。。
１，２回やっただけでは結果がばらけていたように、その時その時で結果が変わったりもしますし。
基本的には結論の通りですが、やってみるのが一番です。