jdbc performance testing for getting CLOB/BLOB data

Question

I am trying to compare the performance of the different calls (getBytes/getBinary/getBlob) for getting data out of a BLOB column.

What I am doing right now is tracking the time to execute the statement via the jdbc driver and iterating through the resultset.

//Mark time
ResultSet resultSet = stmt.executeQuery(query);
resultSet.getBytes(1) // or resultSet.getBinary or resultSet.getBlob
//Mark time again

This is a very inaccurate way of doing things because once I run the first query, the consecutive calls tend to be faster because of the DB engine's caching.

Is there a better way to do a performance evaluation of these different calls?

Answer 1

Reboot between each benchmark run? <j/k>

Honestly, I think the best approach would be just to load up a table with lots of clob/blob data. Ie, perhaps a fake table like this 'create table testtable (id integer primary key, testdata blob);'. Once you've loaded it up with a few gigs of test data (sequentially increasing ids), have your program query randomly (using Math.random) and use that for your comparison.

That should eliminate most caching related variations, and give you a good picture of whether there really is a significant different in performance between the various blob retrieval methods.