JDBC LOB Prefetching

InsertBlob.java

package io.aregger.lob_prefetch;

import java.sql.SQLException;
import java.util.Arrays;

import org.springframework.jdbc.core.JdbcTemplate;

import oracle.jdbc.pool.OracleDataSource;

public class InsertBlob {
    private static JdbcTemplate jdbcTemplate;
    
    private static final String CONNECTION_STRING = "jdbc:oracle:thin:@oracle-database-183:1521:orcl1803";
    private static final String DATABASE_USER = "lobtest";
    
    public static void main(String[] args) throws SQLException {
        OracleDataSource ds = new OracleDataSource();
        ds.setURL(CONNECTION_STRING);
        ds.setUser(DATABASE_USER);
        ds.setPassword(DATABASE_USER);
        jdbcTemplate = new JdbcTemplate(ds);
        
        byte b = 1;
        byte[] ba = new byte[10485760];
        Arrays.fill(ba, b);

        jdbcTemplate.update("insert into blobtest values(?, ?)", 1, ba);
    }

}

LobPrefetchBenchmark.java

package io.aregger.lob_prefetch;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Properties;

import org.openjdk.jmh.annotations.Benchmark;
import org.openjdk.jmh.annotations.BenchmarkMode;
import org.openjdk.jmh.annotations.Measurement;
import org.openjdk.jmh.annotations.Mode;
import org.openjdk.jmh.annotations.Param;
import org.openjdk.jmh.annotations.Scope;
import org.openjdk.jmh.annotations.Setup;
import org.openjdk.jmh.annotations.State;
import org.openjdk.jmh.annotations.Warmup;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.ResultSetExtractor;

import oracle.jdbc.OracleConnection;
import oracle.jdbc.pool.OracleDataSource;

@State(Scope.Benchmark)
public class LobPrefetchBenchmark {
    
    private JdbcTemplate jdbcTemplate;
    
    private static final String CONNECTION_STRING = "jdbc:oracle:thin:@oracle-database-183:1521:orcl1803";
    private static final String DATABASE_USER = "lobtest";
    
    @Param({"10", "100", "1000"})
    public String rowPrefetch;
    
    @Param({"-1", "0", "2000", "4000"})
    public String lobPrefetch;
    
    @Setup
    public void setup() throws SQLException {
        OracleDataSource ds = new OracleDataSource();
        ds.setURL(CONNECTION_STRING);
        ds.setUser(DATABASE_USER);
        ds.setPassword(DATABASE_USER);
        Properties p = new Properties(2);
        
        p.setProperty(OracleConnection.CONNECTION_PROPERTY_DEFAULT_ROW_PREFETCH, rowPrefetch);
        p.setProperty(OracleConnection.CONNECTION_PROPERTY_DEFAULT_LOB_PREFETCH_SIZE, lobPrefetch);
        ds.setConnectionProperties(p);
        jdbcTemplate = new JdbcTemplate(ds);
    }
    
    @Benchmark
    @BenchmarkMode(Mode.SingleShotTime)
    @Warmup(iterations = 2)
    @Measurement(iterations = 10)
    public void lobTest() throws SQLException {
        jdbcTemplate.query("select * from lobtest where rownum <= 1000", new ResultSetExtractor<Void>() {

            @Override
            public Void extractData(ResultSet rs) throws SQLException, DataAccessException {
                while (rs.next()) {
                    @SuppressWarnings("unused")
                    int id = rs.getInt("ID");
                    @SuppressWarnings("unused")
                    String dataAsString = rs.getString("DATA");
                }
                return null;
            }
        });
    }
    
    public static void main(String[] args) throws Exception {
        org.openjdk.jmh.Main.main(args);
    }
}

LobPrefetchBenchmarkBlob.java

package io.aregger.lob_prefetch;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Properties;

import org.openjdk.jmh.annotations.Benchmark;
import org.openjdk.jmh.annotations.BenchmarkMode;
import org.openjdk.jmh.annotations.Measurement;
import org.openjdk.jmh.annotations.Mode;
import org.openjdk.jmh.annotations.Param;
import org.openjdk.jmh.annotations.Scope;
import org.openjdk.jmh.annotations.Setup;
import org.openjdk.jmh.annotations.State;
import org.openjdk.jmh.annotations.Warmup;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.ResultSetExtractor;

import oracle.jdbc.OracleConnection;
import oracle.jdbc.pool.OracleDataSource;

@State(Scope.Benchmark)
public class LobPrefetchBenchmarkBlob {
    
    private JdbcTemplate jdbcTemplate;
    
    private static final String CONNECTION_STRING = "jdbc:oracle:thin:@oracle-database-183:1521:orcl1803";
    private static final String DATABASE_USER = "lobtest";
    
    @Param({"10", "100"})
    public String rowPrefetch;
    
    @Param({"-1", "0", "8000", "10485760"})
    public String lobPrefetch;
    
    @Setup
    public void setup() throws SQLException {
        OracleDataSource ds = new OracleDataSource();
        ds.setURL(CONNECTION_STRING);
        ds.setUser(DATABASE_USER);
        ds.setPassword(DATABASE_USER);
        Properties p = new Properties(2);
        
        p.setProperty(OracleConnection.CONNECTION_PROPERTY_DEFAULT_ROW_PREFETCH, rowPrefetch);
        p.setProperty(OracleConnection.CONNECTION_PROPERTY_DEFAULT_LOB_PREFETCH_SIZE, lobPrefetch);
        ds.setConnectionProperties(p);
        jdbcTemplate = new JdbcTemplate(ds);
    }
    
    @Benchmark
    @BenchmarkMode(Mode.SingleShotTime)
    @Warmup(iterations = 2)
    @Measurement(iterations = 10)
    public void lobTest() throws SQLException {
        jdbcTemplate.query("select id-1+rownum as id, data from blobtest connect by rownum <= 100", new ResultSetExtractor<Void>() {

            @Override
            public Void extractData(ResultSet rs) throws SQLException, DataAccessException {
                while (rs.next()) {
                    @SuppressWarnings("unused")
                    int id = rs.getInt("ID");
                    @SuppressWarnings("unused")
                    byte[] b = rs.getBytes("DATA");
                }
                return null;
            }
        });
    }
    
    public static void main(String[] args) throws Exception {
        org.openjdk.jmh.Main.main(args);
    }
}

setup.sql

create table lobtest (id number(10), data clob);
insert into lobtest select rownum, lpad('*', 8000, '*') from dual connect by rownum <= 10000;
commit;

create table blobtest (id number(10), data blob);