Instantly share code, notes, and snippets.

Embed
What would you like to do?
JDBC LOB Prefetching
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);
}
}
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);
}
}
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);
}
}
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);
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment