JDBC Performance

[2001 JI Open Discussion]

By: Matt Thompson - mthomp1234

JDBC Performance

2005-11-14 12:33

Pardon me if this is old news.

 

While creating a relatively large report (3000+ rows). I noticed what seemed like very slow turnaround time (about 35 seconds). On analyzing it, most of the time was spent in the FILL phase which is dominated by the JDBC fetch time.

 

I further analyzed this by adding code to do a quick prefetch which did nothing but fetch the rows and count them. This showed about 25 seconds JDBC fetch time leaving about 8 seconds or so for processing the rows. These were pretty crude measurements, but good enough to get an idea where the problem was.

 

It turns out JDBC (under Oracle), by default fetches only 10 rows at a time. A better number for reporting would be 2 or 3 orders of magnitue higher. This number can be changed with the Statement.setFetchSize() routine prior to calling executeQuery.

 

Another way to fix it is to set parameters when connecting. This was the method I used since I did not want to modify the JR source. Here is some code:

 

Properties props = new Properties();

props.put("user",userName);

props.put("password",userPassword);

props.put("defaultRowPrefetch","500");

props.put("defaultBatchValue","100");

Connection conn = DriverManager.getConnection(url, props);

 

I think this may be Oracle specific.

 

This reduced the JDBC fetch time from 25 seconds to less than 1 second.

 

This would indicate that JR should probably be issuing a Statment.setFetchSize() call to something larger than 10. It is better to do it in JR because setting the connection parameters may be platform specific and is less likely to get done if left to the user.

 

Perhaps a FetchSize parameter would be in order with a default of 100 or greater.

 

This seems like a pretty gross thing not to have been covered already so accept my humble appologies if there is something already built in which deals with it.

 

 

 

 

 

 

By: Lucian Chirita - lucianc

RE: JDBC Performance

2005-11-17 00:33

Hi

 

Please post a RFE regarding the fetch size parameter so that we can track it properly.

 

Thank you,

Lucian