• Odbc Fetch Buffer Size

Odbc Fetch Buffer Size

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. Download killer bean unleashed mod apk unlimited. How fast you can get all data? What fetch size will Oracle use if you don’t set it explicitly?

Figure 2 - fetching 1M rows (remote oracle database) In this experiment (Oracle database) when using fetchSize 500 I was able to fetch 1M rows 12 times faster than with default fetchSize of 10. You can ask what it means: • does a larger fetch size imply better performance? • if not – what is optimal value? I will try to answer this for Oracle JDBC driver in my next post. Glencoe introduction to business textbooks. For now I can only say that a larger fetch size: • usually gives you faster fetching • requires more memory for buffers • with some configurations consumes surprisingly much memory (oracle driver) In my projects I use the following settings: • fetch size 50 – 200 - as global setting • fetch size 200 – 2000 - for some specific queries Final notes Experiment conditions: • local and remote Oracle 11g database • jdbc driver used: 11.1.0.7.0 • only 1 fetching thread running at a time.