Performance issue with getInt within ResultSetExtractor

Question

I have a performance issue with calling getInt inside a ResultSetExtractor. GetInt is called 20000 times. One call cost 0.15 ms, the overall cost is 24 seconds, while running inside the profiler. The execution of the SQL statements takes around 8 seconds (access over Primary-Key). I use the mysql driver version 5.1.13, mysql server 5.1.44 and spring-jdbc-3.1.1 Have you an idea to improve the performance?

    mut.getMutEffect()[0]=(rs.getInt("leffect_a") != 0);
    mut.getMutEffect()[1]=(rs.getInt("leffect_c") != 0);
    ...
    mut.getMutEffect()[19]=(rs.getInt("leffect_y") != 0);
    mut.getMutReliability()[0]=rs.getInt("lreliability_a");
    ...
    mut.getMutReliability()[19]=rs.getInt("lreliability_y");

My scheme looks like this

CREATE TABLE mutation (
 ...
 leffect_a BIT NOT NULL,
 lreliability_a TINYINT UNSIGNED NOT NULL,
 ...
 leffect_y BIT NOT NULL,
 lreliability_y TINYINT UNSIGNED NOT NULL,
 ...
) ENGINE=MyISAM;

Edit: Within getInt the methode getIntWithOverflowCheck is called which seems to be expensive. Is it possible to turn of this checks?

Answer 1

Here are some suggestions:

• Set fetch size to a fairly large number: Statement.setFetchSize(). This should reduce the round-trips to the database server while processing the resultset.

• Ensure the select statement is optimal by profiling

• General table optimization, e.g. are you using correct datatypes? It looks like you could change the leffect_a to a BOOLEAN

• Make sure you aren't returning any unnecessary columns in your SELECT statement.

• Use PreparedStatement

• Avoid scrollable and updatable resultsets (neither are the default)