Performance issue with getInt in ResultSetExtractor

Question

I have a performance issue when calling getInt inside a ResultSetExtractor. GetInt is called 20,000 times. The cost of one call is 0.15 ms, the total cost is 24 seconds, and inside the profiler. Executing SQL queries takes about 8 seconds (access to the Primary-key). I am using mysql driver version 5.1.13, mysql server 5.1.44 and spring -jdbc-3.1.1 Do you have an idea to improve 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 diagram 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: inside getInt, the getIntWithOverflowCheck method is called, which seems expensive. Can these checks be included?

Answer 1

Here are some suggestions:

• Set the sample size to a fairly large number: Statement.setFetchSize () . This should reduce round trips to the database server when processing the result set.

• Make sure the select statement is optimal with profiling

• General table optimization, for example. are you using the correct data types? Looks like you can change leffect_a to BOOLEAN

• Make sure that you do not return extra columns in the SELECT statement.

• Use PreparedStatement

• Avoid scrollable and updatable result sets (neither by default)

Answer 2

Two suggestions:

• Store the result of getMutEffect() and getMutReliability() in local variables since they are reused. The hotspot jit point can inline and remove duplicate expressions, but I think its clearer not to rely on this.
• It can get ResultSet values ​​faster by using their indexes instead of column names. You can even create a local name map for indexing, strange for some jdbc drivers, this is faster than letting the ResultSet do the mapping.