JDBC Fetch Size Affecting Adhoc Sample Data

When creating an Adhoc report in the Adhoc Designer, user can limit the amount of data used during the design process by using (default) "sample data" selection. The size of the sample data (number of rows used in the designer) is determined by the fetch size of a JDBC driver.

Because different JDBC drivers set their own rule on the fetch size, the user will experience difference performance behaviors and issues when using different data sources during their Adhoc report design. MySQL, MS SQL, and PostgreSQL JDBC drivers retrieve all the rows from database (depending on the cursor type). If dealing with large amount of data, user often experienced OutOfMemory error when JDBC driver trying to load the entire result set into memory. Oracle JDBC drivers on the other hand, use default fetch size of 10. Users often find Oracle retrieval performance is slower than other servers for this reason - it will take multiple trips to get the amount of data from Oracle comparing to a single trip using other JDBC drivers. In DB2, it seems the default fetch size is 32 for its JDBC drivers.

If users wish not to rely on the default JDBC fetch size of a particular driver and/or need to fine tune report data retrieval performance, they must explicitly set the fetch size in JasperReports property file to force JR engine to use it during the data retrieval. To do so, users need to add the following entry into WEB-INFclassesjasperreports.properties file:

net.sf.jasperreports.jdbc.fetch.size=n

where n is the number of rows to fetch at a time

• optimizing n can reduce the number of trips to the database.

Because of the behavior differences between JDBC drivers and amount of data in the databases, users should experiment with different numbers for n (perhaps 100 to 1000) to find the best overall performance and user experience to meet your reporting requirement.

Please make sure restart the server after making the change to jasperreports.properties file to have the change take effect.

(TTC/21397)