[OT] Re: Data Access Optmizing

[Kirk Wylie <kw...@m7.com>]

Some specific points below, but in general, it's difficult to say 
exactly what's going on. There's a whole lot of industry that's devoted 
around making the exact situation you're talking about work quickly, and 
several products (open source and commercial) designed to help optimize 
database-backed applications, so it's a pretty big area that you're 
talking about. But some specific points.

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: struts-user-unsubscribe@jakarta.apache.org
For additional commands, e-mail: struts-user-help@jakarta.apache.org