We are using WebLogic 8.1 SP2 and interacting with both Oracle and DB2 databases through JDBC.
Facing some performance issues here and i was wondering about how to improve performance using the pre-fetch size of the prepared statement and resultset.How does one determine the optimum size and what impact would it have?
Thanks in advance
A lot of factors need to be taken into account:
1) How many columns is the result set
2) Types of those columns (any blobs?)
3) JVM gc profile
4) total set of rows you're trying to retrieve
There probably isn't a simple generic optimisation that can be applied, its likely to be different for every query you wish to optimize, and depends on the database you're interacting with.
Some complications to consider, lets say you're reading 3000 records per second from the oracle db - well the OCI drivers let you specify the row caching for both the server side cursor and the client side cursor, so you need to emperically test for varying cache levels what the optimum rate one can achieve. Note also, that when you're reading that many records very quickly, thats a lot of garbage you're creating. If your eden is too small, you'll find the first 50000 might read just fine but after exhausting eden, you'll see a noticeable slowdown.
There are a lot of variables to consider, designing a good test framework, and then attempting to both mathematically and emperically test scenarios should yield good optimisations.