SonicBase. In-memory embedded or distributed streaming sql database

SQL Batch Read


Batch identity reads can greatly improve the performance of looking up a batch of records by a batch of keys. Batch identity reads can be achieved in two ways. The first approach is to use an "in expression". The second approach is to use a series of "or expressions". For both approaches, a batch size of 200 is good. Use a larger batch size for a larger cluster.

In Expression

This approach only works if you have a single field in the keys you are looking up.

Example


PreparedStatement stmt = conn.prepareStatement("select * from persons where id1 in (1, 2, 3, 4, 5, 6, 7, 8, 9) order by id1 asc");

ResultSet rs = stmt.executeQuery();
for (int i = 0; i < 10; i++) {
  rs.next();
  System.out.println(rs.getString("name"));
}
		

Or Expressions

Or expressions can by used to lookup records by a composite key.

Example


PreparedStatement stmt = conn.prepareStatement("select * from persons where id1 = 1 and id2 = 2 or id1 = 1 and id2=3 order by id1 asc");

ResultSet rs = stmt.executeQuery();
for (int i = 0; i < 2; i++) {
  rs.next();
  System.out.println(rs.getString("name"));
}