JDBC fetch size - commonly forgotten setting
JDBC fetch size parameter can significantly reduce the result set fetching time. It can be set on any Statement or PreparedStatement object. Many frameworks like Spring or Hibernate give you very convenient API to do this. Although this parameter is widely available, I discovered that a lot of projects I have visited did not configure the fetch size and thus was using its default value. Yet, the default value can result in a poor performance on some jdbc drivers. I want to show you how much you can improve your fetching performance if you adjust the fetch size to your statements. The scale of improvement depends on the driver you use. Oracle jdbc driver Assume we have table with 1 million rows and for any reason we have to fetch all records into JVM. How fast you can get all data? What fetch size will Oracle use if you don’t set it explicitly? Figure 1 - fetching 1M rows with different fetchSize values (local oracle database) Figure 1 shows fetching times for differe...