Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Spring JDBC template ROW Mapper is too slow

I have a db fetch call with Spring jdbcTemplate and rows to be fetched is around 1 millions. It takes too much time iterating in result set. After debugging the behavior I found that it process some rows like a batch and then waits for some time and then again takes a batch of rows and process them. It seems row processing is not continuous so overall time is going into minutes. I have used default configuration for data source. Please help.

[Edit]

Here is some sample code

this.prestoJdbcTempate.query(query, new RowMapper<SomeObject>() {
            @Override
            public SomeObject mapRow(final ResultSet rs, final int rowNum) throws SQLException {

                System.out.println(rowNum);
                SomeObject obj = new SomeObject();
                obj.setProp1(rs.getString(1));
                obj.setProp2(rs.getString(2));
                ....
                obj.setProp8(rs.getString(8));
                return obj;
            }
    });
like image 601
hard coder Avatar asked Oct 19 '25 10:10

hard coder


1 Answers

As most of the comments tell you, One mllion records is useless and unrealistic to be shown in any UI - if this is a real business requirement, you need to educate your customer.

Network traffic application and database server is a key factor in performance in scenarios like this. There is one optional parameter that can really help you in this scenario is : fetch size - that too to certain extent

Example :

Connection connection = //get your connection
Statement statement = connection.createStatement();
statement.setFetchSize(1000); // configure the fetch size

Most of the JDBC database drivers have a low fetch size by default and tuning this can help you in this situation. **But beware ** of the following.

  • Make sure your jdbc driver supports fetch size
  • Make sure your JVM heap setting ( -Xmx) is wide enough to handle objects created as a result of this.
  • Finally, select only the columns you need to reduce network overhead.

In spring, JdbcTemplate lets you set the fetchSize

like image 64
ring bearer Avatar answered Oct 21 '25 22:10

ring bearer



Donate For Us

If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!