Use the Spring JdbcTemplate’s or Spring ResultSet fetchSize to your Advantage
Are we experiencing the slow response when doing a JDBC call using some API call ?Everything checks out such as Network calls , running query explicitly in the database etc. then may be it’s time to check your existing JdbcTemplate or ResultSet method below :
void setFetchSize(int rows) throws SQLException
So by default the JdbcTemplate has a fetch size of (-1) which means 10 rows will be fetched at a time from database cursor. That being said for example, if any response were to return 200 rows the Request will make 20 trips to the database cursor.
So for any API call such as http://localhost:8080/your-service/api/bulk pointing to X database and it returns 10,000 Records. So this does make a high number of trips to database cursor and hence the slow response will be observed. But bumping up the fetch size from 10 to 300 or higher based on your need will decrease the response time.
I think this approach needs to be looked case by case because not all API returns that larger number of rows, as well as we don’t want to allocate more memory for a call and then not use it as the response may return less rows.