Also, I'm assuming that you've rolled your own JDBC system since you didn't mention a specific O-R Mapping system.
Zoran Avtarovski wrote:
By nested calls I mean that say I retrieve a Collection of a couple of
thousand user response objects (which are retrieved quite quickly) and then
I process the responses by iterating through them to produce a Collection of
user feedback objects. During each cycle I make a number of database calls
to get information related to processing the individual user response. This
is where things get really sloooooooow.
Had you thought about using a single SQL statement to populate everything? In other words, if your cycle is:
- Get a bunch of user response objects
- For each user response object, get its user feedback objects
This is something which is very easy to do with two SQL statements, rather than one plus one per user response object. Since there's overhead in each SQL Statement you issue, a good place to start might be in doing this.
Or, if you don't want to play with your logic that much, you can probably improve things by:
- Holding open the Connection for all user feedback queries; and
- Doing one PreparedStatement for all the user feedback queries, to save on query preparation.
This won't be as good as the above, but it'll help quite a bit.
My thinking is that it's not the data access method (simple DAO's) but more the fact that I'm opening and closing so many connections during the processing phase.
That shouldn't be a big problem if your connection pool is working properly, because while your code is opening a closing connections, the connections actually aren't being opened and closed. But just preparing and executing statements will use up a substantial amount of resource. This is assuming your connection pool is working. You're not leaking connections anywhere?
However, you're right, even if you've got a connection pool that's optimized, there's still scope for optimization by not interacting too much with it.
Again, it's difficult to give that much in the way of concrete advice without knowing your particulars, but here are some places to start.
Kirk Wylie M7 Corporation
--------------------------------------------------------------------- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
