SQL を使ったプログラムの今昔 -「Refactoring SQL Applications」を読みながら-

Refactoring SQL Applications」という本を最近読んでいます。

「データベースのパフォーマンスが。。」といった時にはすぐ「チューニング!!」となりますね。
通常、「チューニング」といって思い浮かぶのはインデックスの作成だったり、SQL 文のちょっとした書き直しだったりするわけですが、果たしてそれが正しい答えなのかどうか …
そんな疑問への解答が、この本で著者が言いたいことのようです。

まず最初の章に、全体へのイントロダクションをかねて、ある時間のかかる SQL 処理について対策をしていくケースが載っています。

今回、このケースを DB2 for IBM i でも実行してみて、どうなるかを試してみました。
この本の紹介も兼ねて、ここで紹介してみたいと思います。


まず最初に、この本で例になっている、その「時間のかかる SQL 処理」を見ていきましょう。

それは Java で JDBC を使用してデータベースアクセスを行うプログラムです。
そのプログラムの中で、いくつかの SQL を実行するようになっています。

著者によれば「実際にアプリケーションで使用されていた"そのもの"ではないが、現場で実際によく目にする典型的な処理」ということです。
(たしかに、実際に手続き型の発想でそのままコーディングするとこうなりますので、「よくあるなぁこういうの」と内容を見ながら僕も思いました)

流れの概要を追ってみます。

チューニング対象になる「時間のかかる SQL 処理」

処理の概要

最初に、エリアを指定して、そのエリアに存在する顧客をリストする SQL 文を発行します。

           PreparedStatement st1 = con.prepareStatement("select accountid"
                                                       + " from area_accounts"
                                                      + " where areaid = ?");

次にそのリストされた各顧客の ID 毎に、指定の日付以降の取引をリストします。

           PreparedStatement st2 = con.prepareStatement("select txid,amount,curr"
                                                      +  " from transactions"
                                                      + " where accountid=?"
                                                            + " and txdate >= (cast(? as timestamp) - 30 days)"
                                                      + " order by txdate");

そのリストされた各取引について、通貨と金額毎に(あらかじめ決められている)上限のチェックを行います。

    private static boolean AboveThreshold(float amount, String iso) throws Exception {
        PreparedStatement thresholdstmt = con.prepareStatement("select threshold"
                                                              + " from thresholds"
                                                              + " where iso=?");
        ResultSet         rs;

        thresholdstmt.setString(1, iso);
        rs = thresholdstmt.executeQuery();
        if (rs.next()) {
           if (amount >= rs.getFloat(1)){
              rs.close();
              thresholdstmt.close();
              return true;
           } else {
              rs.close();
              thresholdstmt.close();
              return false;
           }          

上限を超えたものについては、通貨と日付を指定してレートを取得し、そのレートで換算した金額を計算します。

    private static float Convert(float amount, String iso, String valuationdate) throws Exception {
        PreparedStatement conversionstmt = con.prepareStatement("select cast(? as float) * rate"
                                                               + " from currency_rates"
                                                              + " where iso = ?"
                                                                    + " and rate_date = ?");

上限を超えている取引を、換算された金額と共にチェックログに挿入します。

           PreparedStatement st3 = con.prepareStatement("insert into check_log(txid,"
                                                        + " conv_amount)"
                                                        + " values(?,?)");

結果の検算

結果の検算をしようと考えて、レートの計算をしない、テスト用のSQL を↓のように作ってみました。
プログラムの実行とテスト用の実行結果を比較してみたところ、正しそうです。
(逆に言うと↑のプログラムのロジックの大半は↓のSQL ひとつに集約されてしまう、ということですね。。)

ちなみに、変数の部分はこのプログラムの中で使用されている値を直接指定しています。

SELECT * FROM RFTSQL/TRANSACTIONS as T 
        WHERE AMOUNT >= (SELECT THRESHOLD FROM RFTSQL/THRESHOLDS 
                                         WHERE ISO = T.CURR)
              and date(TXDATE) >= '2007-09-28'
              and T. ACCOUNTID IN (SELECT ACCOUNTID FROM RFTSQL/AREA_ACCOUNTS WHERE AREAID = 7) 
       ORDER BY TXID

さらにちょっと考えてみたら、↓のようにレートの計算も結合すれば織り込めることに思い至りました。

結果をそのまま出力できるSQL、ということになります。

つまり、このプログラム内のいくつかの SQL はたったひとつの SQL にまとめてしまうことが可能、というわけなんですね。
(それがこの本の主張の根幹のひとつになっています)

SELECT TXID, AMOUNT * R.RATE FROM RFTSQL/TRANSACTIONS as T,        
                                  RFTSQL/CURRENCY_RATES as R 
                            WHERE AMOUNT >= (SELECT THRESHOLD FROM RFTSQL/THRESHOLDS WHERE ISO = T.CURR)
                                  and date(TXDATE) >= '2007-09-28'
                                  and T. ACCOUNTID IN (SELECT ACCOUNTID FROM RFTSQL/AREA_ACCOUNTS WHERE AREAID = 7)
                                  and T.CURR = R.ISO
                                  and date(R.RATE_DATE) = '2007-7-20' 
                            ORDER BY TXID                      

「ひとつにまとめられた SQL を発行する」と「こまかく SQL を実行してプログラムの中で処理する」の違い

この例はシンプルにまとめると、「ひとつにまとめられた SQL を発行する」方法と「こまかく SQL を実行してプログラムの中で処理する」方法との違い、になるわけです。

それぞれを、使うシステム資源の観点から考えると、前者は最適化(コンパイル)時間がかかるかわりに全体を最適化でき、後者は(相対的)最適化時間はかからないがファイルのオープンの回数が多くなることに伴うシステム資源の非効率がある、ということになります。

後者の場合、SQL が発行されるたびに、ファイルオープンが行われます。データベース・エンジンによってはすでにクローズされてしまったファイルをまたオープンしなくてはならないので、I/O はもとより CPU 資源やメモリーの取り直しなどが発生する可能性があります。

前者の場合は、コンパイル時間が(後者と比べて相対的に)かかりますが、I/O も CPU 資源も最小で行われるようにすることができます。

後者と前者の違いには、時代背景の違いが実はあるんですね。
その時代背景の違いは大きく二つあります。

ひとつは、やはりコンピューティング・パワーの進化、です。

CPU のクロック数などはこの数十年で、考えられないほどの進歩をしています。
昔のコンピュータでいつも問題になっていたコンパイル時間は、本当に劇的に短くなっています。

これくらいの SQL だったら数秒で最適化(コンパイル)は終了してしまうでしょう。(”昔のコンピュータ”であればそれこそ数分〜数十分かかったかもしれません…)
”オン・デマンド”でコンパイルしても、システムにはたいした負荷にはなりません。
それで、「最適」なプログラムが手に入り、実行できるのであれば、そちらの方がシステム全体の実行効率としてはいいことになります。

ふたつめは、ソフトウェア技術の進化、ですね。

”昔”は「パフォーマンスが問題になるところはアセンブラ(もしくは機械語)で書け」といわれていました。
今はそんなことはありません。少なくともアプリケーションプログラムの領域では、いわゆる「高水準言語」で書こうが、アセンブラや機械語のような「低水準言語」で書こうが、それほどの差は出ないでしょう。

これは先述した「コンピューティング・パワーの進化」と歩を合わせつつ、ソフトウェアの最適化技術も進歩してきていることによります。

”昔”の、複雑な処理については、コンパイルに時間もかかるし、できたプログラムもあまり速くない、という状態が、今は、そうした処理でも、コンパイルに時間もかからないし、プログラムも必要十分な効率を実現している、という状態に変わっています。

こうした時代背景の変化に伴って、「カンタンな SQL を複数実行してプログラムで処理」から「なるべくひとつの SQL にまとめてデータ取得を実行し、プログラムはその制御に専念」という形に変わってきているわけです。

ソース

テストに使用したソースの全文は↓です。

この本のサポートサイトに載っていたものを修正して使用しました。(もともと MySQL/Oracle/SQL Server 用にしかサンプルがなく( = DB2 用のものはありませんでした …)、DB2 にあわせて SQL を書き換える必要がありました。
コメントで元のソースは残してありますが、どう書き換えたかについてはいずれ稿を改めて書こうかと思っています)

import java.io.*;
import java.sql.*;
import java.util.Properties;
import java.text.*;
import java.lang.Math.*;

public class FirstExample{

   private static Connection        con;

   private static Connection DBConnect(String cnx,
                                       String username,
                                       String password) throws Exception {
        Connection mycon = null;

        try {
             // Establish Connection to the database
             if (username != null) {
                 mycon = DriverManager.getConnection(cnx, username, password);
             } else {
                 mycon = DriverManager.getConnection(cnx);
             }
        } catch (Exception e) {
             System.err.print("Warning : ");
             System.err.println(e.getMessage());
        }
        return mycon;
       }

  
    private static Properties LoadProp() throws Exception {
        Properties prop = new Properties();
        try {
             FileInputStream fis = new
                            FileInputStream("database.properties");
             prop.load(fis);
        } catch (Exception e) {
             System.err.print("Warning : ");
             System.err.println(e.getMessage());
        }
        return prop;
    }

    private static boolean AboveThreshold(float amount, String iso) throws Exception {
        PreparedStatement thresholdstmt = con.prepareStatement("select threshold"
                                                              + " from thresholds"
                                                              + " where iso=?");
        ResultSet         rs;

        thresholdstmt.setString(1, iso);
        rs = thresholdstmt.executeQuery();
        if (rs.next()) {
           if (amount >= rs.getFloat(1)){
              rs.close();
              thresholdstmt.close();
              return true;
           } else {
              rs.close();
              thresholdstmt.close();
              return false;
           }          
        } else {   // not found - assume no problem
          rs.close();
          thresholdstmt.close();
          return false;
        }
    }

    private static float Convert(float amount, String iso, String valuationdate) throws Exception {
     // パラメータ・マーカーをデータタイプがわかるようにキャスト
        PreparedStatement conversionstmt = con.prepareStatement("select cast(? as float) * rate"
     // PreparedStatement conversionstmt = con.prepareStatement("select ? * rate"
                                                               + " from currency_rates"
                                                               + " where iso = ?"
                                                               + " and rate_date = ?");
        ResultSet         rs;
        float             val = (float)0.0;

        conversionstmt.setFloat(1, amount);
        conversionstmt.setString(2, iso);
        conversionstmt.setString(3, valuationdate);
        rs = conversionstmt.executeQuery();
        if (rs.next()) {
           val = rs.getFloat(1);
        }
        rs.close();
        conversionstmt.close();
        return val;
    }

    public static void main(String args[]) throws Exception {
        String           cnx;
        String           username = null;
        String           password = null;
        String           driver;
        String           queryString = "";
        Properties       myprop;
        FileReader       filereader;
        int              i;
        int              j;
        int              k;
        int              dummy;
        String           somedate = "00/00/0000";
        String           valuationdate = "00/00/0000";
        int              areaid;
        long             start;
        long             stop;

        myprop = LoadProp();
        if (((driver = System.getProperty("DRIVER")) == null)
            && ((driver = myprop.getProperty("DRIVER")) == null)) {
            System.err.println("Undefined JDBC driver (DRIVER parameter)");
            System.exit(1);
        }
        Class.forName(driver).newInstance();
        if (((cnx = System.getProperty("CONNECT")) == null)
            && ((cnx = myprop.getProperty("CONNECT")) == null)) {
            System.err.println("Undefined connection string " +
                               "(CONNECT parameter)");
            System.exit(1);
        }
        // Username / Password may be passed in CONNECT
        if ((username = System.getProperty("USERNAME")) == null) {
            username = myprop.getProperty("USERNAME");
        }
        if (username != null) {
            if (((password = System.getProperty("PASSWORD")) == null)
                && ((password = myprop.getProperty("PASSWORD")) == null)) {
                System.err.println("Both username and password "
                                   + "must be set");
                System.exit(1);
            }
        }

      con = DBConnect(cnx, username, password);
      // We arbitrarily set three values:
      //     * One "areaid"
      //     * One date for fetching transactions after a given time
      //     * One valuation date for currency conversion 
      //
      areaid = 7;
      // Get a particular date
      try {
           Statement st = con.createStatement();
           String sqltext = "select txdate from transactions where txid=12345"; 
           ResultSet rs = st.executeQuery(sqltext);
           if (rs.next()) {
              somedate = rs.getString(1);
           }
           System.out.println("areaid=" + Integer.toString(areaid) + ", somedate=" + somedate);
           sqltext = "select max(rate_date) from currency_rates";
           rs = st.executeQuery(sqltext);
           if (rs.next()) {
              valuationdate = rs.getString(1);
           }
           System.out.println("valuationdate=" + valuationdate);
           rs.close();
           st.close();
      } catch(SQLException ex){
            System.err.println("==> SQLException: ");
            while (ex != null) {
                System.out.println("Message:   " + ex.getMessage ());
                System.out.println("SQLState:  " + ex.getSQLState ());
                System.out.println("ErrorCode: " + ex.getErrorCode ());
                ex = ex.getNextException();
                System.out.println("");
            }
      }
      // Clean-up the check_log table 
      Statement stmt = con.createStatement();
      // stmt.executeUpdate("truncate table check_log");
      // DB2 には "truncate table" 文がないため(DB2 for z V9.1以降以外)DELETE 文を使用
      stmt.executeUpdate("delete from check_log");
      stmt.close();
      // Now the real process
      start = System.currentTimeMillis();
      try {
           long    txid;
           long    accountid;
           float   amount;
           String  curr;
           float   conv_amount;

           PreparedStatement st1 = con.prepareStatement("select accountid"
                                                        + " from area_accounts"
                                                        + " where areaid = ?");
           ResultSet         rs1;
      //   PreparedStatement st2 = con.prepareStatement("select txid,amount,curr"
      //                                                + " from transactions"
      //                                                + " where accountid=?"
      //                                                + " and txdate >= date_sub(?, interval 30 day)"
      //                                                + " order by txdate");
      //   DB2 には DATE_SUB 関数が不要
      //   DB2 for i の場合、オプティマイザへの情報としてパラメーター・マーカーにはデータタイプがわかることが必要
      //   (あいまいな場合は明示的なキャストが必要)
           PreparedStatement st2 = con.prepareStatement("select txid,amount,curr"
                                                        + " from transactions"
                                                        + " where accountid=?"
                                                        + " and txdate >= (cast(? as timestamp) - 30 days)"
                                                        + " order by txdate");
           ResultSet         rs2;
           PreparedStatement st3 = con.prepareStatement("insert into check_log(txid,"
                                                        + " conv_amount)"
                                                        + " values(?,?)");
           
           st1.setInt(1, areaid);
           rs1 = st1.executeQuery();
           while (rs1.next()) {
              accountid = rs1.getLong(1);
              st2.setLong(1, accountid);
              st2.setString(2, somedate);
              rs2 = st2.executeQuery();
              while (rs2.next()) {
                 txid = rs2.getLong(1);
                 amount = rs2.getFloat(2);
                 curr = rs2.getString(3);
                 if (AboveThreshold(amount, curr)) {
                    // Convert
                    conv_amount = Convert(amount, curr, valuationdate);
                    st3.setLong(1, txid);
                    st3.setFloat(2, conv_amount);
                    dummy = st3.executeUpdate();
                 }
              }
              rs2.close();
           }
           rs1.close();
           st3.close();
           st2.close();
           st1.close();
      } catch(SQLException ex){
            System.err.println("==> SQLException: ");
            while (ex != null) {
                System.out.println("Message:   " + ex.getMessage ());
                System.out.println("SQLState:  " + ex.getSQLState ());
                System.out.println("ErrorCode: " + ex.getErrorCode ());
                ex = ex.getNextException();
                System.out.println("");
            }
      }
      stop = System.currentTimeMillis();
      // Last check
      try {
           Statement st = con.createStatement();
           String sqltext = "select count(*), min(txid), max(txid) from check_log"; 
           ResultSet rs = st.executeQuery(sqltext);
           if (rs.next()) {
              System.out.println("Logged transactions = " + Integer.toString(rs.getInt(1)) +
                                 ", from " + Long.toString(rs.getLong(2)) +
                                 " to " + Long.toString(rs.getLong(3)));
           }
           rs.close();
           st.close();
      } catch(SQLException ex){
            System.err.println("==> SQLException: ");
            while (ex != null) {
                System.out.println("Message:   " + ex.getMessage ());
                System.out.println("SQLState:  " + ex.getSQLState ());
                System.out.println("ErrorCode: " + ex.getErrorCode ());
                ex = ex.getNextException();
                System.out.println("");
            }
      }
      System.out.println("Elapsed (ms)\t" + (stop - start));
      con.close();
    }
}

[Top Pageに戻る]

Ads by TOK2