[SQL Server] image型やtext型はテキストポインタを利用して取得したほうが速い

Javaでimage、text、ntext型の列に格納したサイズの大きなデータを取得する場合、普通は通常のデータ型の列と同様にSELECT文で検索し、getBinaryStream()などで取得しますが、実はこの方法よりもテキストポインタを利用する方法のほうがパフォーマンスがよかったりします。

例えば、IMAGE_TABLEというテーブルのIMAGE_DATAという列に格納されたバイナリーファイルイメージを取得して、ファイルに出力するという場合、普通は以下のようなプログラムとなります。

OutputImageToFile1.java

import java.io.*;
import java.sql.*;
import java.util.*;

public class OutputImageToFile1 {
  public static void main( String[] args ) throws Exception {

    FileOutputStream fos = null;
    Connection con = null;
    PreparedStatement pstmt = null;
    ResultSet rs = null;

    try {
      fos = new FileOutputStream( args[ 0 ] );
      Driver driver = ( Driver )Class.forName( "com.microsoft.jdbc.sqlserver.SQLServerDriver" ).newInstance();
      Properties dbprop = new Properties();
      dbprop.put( "DatabaseName", "dbname" );
      dbprop.put( "User", "sa" );
      dbprop.put( "Password", "sa" );
      dbprop.put( "SelectMethod", "cursor" );
      con = driver.connect( "jdbc:microsoft:sqlserver://hostname:1433", dbprop );

      // SQLを実行し、image列を取得する。
      pstmt = con.prepareStatement( "SELECT IMAGE_DATA FROM IMAGE_TABLE WHERE ID = ?" );
      pstmt.setString( 1, args[ 1 ] );
      rs = pstmt.executeQuery();
      rs.next();
      InputStream is = rs.getBinaryStream(1);

      // 取得したimage列からデータを64Kずつ読み込み、ファイルに出力する。
      byte[] buffer = new byte[ 65536 ];
      int len = -1;
      while( ( len = is.read( buffer ) ) != -1 ) {
        fos.write( buffer, 0, len );
      }
    } finally {
      try { fos.close(); } catch( Exception ignore ) {}
      try { rs.close(); } catch( Exception ignore ) {}
      try { pstmt.close(); } catch( Exception ignore ) {}
      try { con.close(); } catch( Exception ignore ) {}
    }
  }
}

このプログラムはSELECT文でIMAGE_TABLEテーブルのIMAGE_DATA列を検索し、それに対する入力ストリームをgetBinaryStream()で取得し、それを一定バイト数単位で読み込んではファイル出力ストリームに書き出していくという比較的単純な処理です。

これをテキストポインタを利用した方法に書き直すと以下のようになります。

OutputImageToFile2.java

import java.io.*;
import java.sql.*;
import java.util.*;

public class OutputImageToFile2 {
  public static void main( String[] args ) throws Exception {

    FileOutputStream fos = null;
    Connection con = null;
    PreparedStatement pstmt = null;
    ResultSet rs = null;

    try {

      fos = new FileOutputStream( args[ 0 ] );
      Driver driver = ( Driver )Class.forName( "com.microsoft.jdbc.sqlserver.SQLServerDriver" ).newInstance();
      Properties dbprop = new Properties();
      dbprop.put( "DatabaseName", "dbname" );
      dbprop.put( "User", "sa" );
      dbprop.put( "Password", "sa" );
      dbprop.put( "SelectMethod", "cursor" );
      con = driver.connect( "jdbc:microsoft:sqlserver://hostname:1433", dbprop );

      // SQLを実行し、image列のテキストポインタを取得する。
      pstmt = con.prepareStatement( "SELECT TEXTPTR( IMAGE_DATA ) FROM IMAGE_TABLE WHERE ID = ?" );
      pstmt.setString( 1, args[ 1 ] );
      rs = pstmt.executeQuery();
      rs.next();
      byte[] bptr = rs.getBytes( 1 );
      rs.close();
      pstmt.close();

      // SQLを実行し、image列のサイズ（長さ）を取得する。
      pstmt = con.prepareStatement( "SELECT DATALENGTH( IMAGE_DATA ) FROM IMAGE_TABLE WHERE TEXTPTR( IMAGE_DATA ) = ?" );
      pstmt.setBytes( 1, bptr );
      rs = pstmt.executeQuery();
      int blen = 0;
      if ( rs.next() ) {
        blen = rs.getInt( 1 );
      }
      rs.close();
      pstmt.close();

      // テキストポインタを起点にデータを64Kずつ読み込み、ファイルに出力する。
      int bsize = 65536;
      byte[] buffer = new byte[ bsize ];
      int spos = 0;
      int len = -1;
      pstmt = con.prepareStatement( "READTEXT IMAGE_TABLE.IMAGE_DATA ? ? ?" );
      while ( blen > spos ) {
        pstmt.clearParameters();
        pstmt.setBytes( 1, bptr );
        pstmt.setInt( 2, spos );
        if ( blen > spos + bsize ) {
          pstmt.setInt( 3, bsize );
        } else {
          pstmt.setInt( 3, blen - spos );
        }
        rs = pstmt.executeQuery();
        if ( rs.next() ) {
          InputStream ins = rs.getBinaryStream( 1 );
          len = ins.read( buffer );
          if ( len != -1 ) {
            fos.write( buffer, 0, len );
          }
          ins.close();
        }
        spos = spos + bsize;
      }
    } finally {
      try { fos.close(); } catch( Exception ignore ) {}
      try { rs.close(); } catch( Exception ignore ) {}
      try { pstmt.close(); } catch( Exception ignore ) {}
      try { con.close(); } catch( Exception ignore ) {}
    }
  }
}

少々長ったらしいプログラムですが、このプログラムではいきなりIMAGE_DATA列を取得するのではなく、まずIMAGE_DATA列に対するテキストポインタとIMAGE_DATA列の長さ（サイズ）を取得しておき、それから、READTEXT文を使ってIMAGE_DATA列を一定バイト数ずつ取得するという流れになっています。

両者は取得してから小分けにするか、小分けにしてから取得するかという違いこそあれ、最終的に得られる結果は同じです。

しかし、パフォーマンスはかなり違います。

10MBと100MBのファイルをIMAGE_TABLEテーブルのIMAGE_DATA列にあらかじめ登録しておき、それらを取得してファイルに出力する際のパフォーマンスを計測してみると以下のような結果が得られました。

ファイルサイズ	テキストポインタを利用しない場合 (OutputImageToFile1.java)	テキストポインタを利用する場合 (OutputImageToFile2.java)
10MB	1.25秒	0.91秒
100MB	10.81秒	6.67秒

何とテキストポインタを利用するほうが３、４割程度速いのです。

処理の手順が違うので比較は難しいのですが、ステートメント単位で処理時間を見ていくと、テキストポインタを利用しない場合はIMAGE_DATA列を取得するSELECT文の実行（executeQuery()）に大半の時間を費やしており、ここが最大のボトルネックになっているということがわかりました。

一方のテキストポインタを利用する場合は小分けにデータを取得するためにSQLの発行回数こそ多くなりますが、そのオーバーヘッドを補って余りあるほど１つ１つのSQLの実行時間は良好です。

Java以外ののプログラミング言語でも同じような現象が見られるのかどうかは未確認ですが、Javaに関して言えば、コーディングは面倒ですが、テキストポインタを利用したほうがよさそうです。

Archive Redo Blog

DBエンジニアのあれこれ備忘録

[SQL Server] image型やtext型はテキストポインタを利用して取得したほうが速い