Re: Data Access Optmizing

[Zoran Avtarovski <zo...@conads.com>]

> 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. 

It's actually a little more complex than that. What happens is that I
perform a number of calculations, which use variables stored in the database
and depending on the result more information is retrieved from the database
and added to the response. It's not a standard 1-n or n-n relationship, but
more extracting data on the fly.

One option is to store the calculation variables in a hash table stored in
application scope and only change them when they have been updated.

Will using something like iBatis or even Hibernate improve the situation?

Zoran


---------------------------------------------------------------------
To unsubscribe, e-mail: struts-user-unsubscribe@jakarta.apache.org
For additional commands, e-mail: struts-user-help@jakarta.apache.org

Re: Data Access Optmizing

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

Zoran Avtarovski wrote:

>  > 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.
> 
> It's actually a little more complex than that. What happens is that I
> perform a number of calculations, which use variables stored in the 
> database
> and depending on the result more information is retrieved from the database
> and added to the response. It's not a standard 1-n or n-n relationship, but
> more extracting data on the fly.

Well, if the calculation is purely based on database information, you 
could use a stored procedure (which Milind Kulkarni recommended in 
another email) or you could also create a virtual column in the database 
schema (such as through a View or a direct calculated column in some 
databases like DB/2) and do a query on that, which would then allow the 
calculation to go into one database call.

Have you tried pushing the calculation logic into the database (as with 
a Stored Procedure or a View) yet? That might allow you to again get 
into a single-query model, but without knowing the type of calculations 
you're making I can't make a definite recommendation.

> One option is to store the calculation variables in a hash table stored in
> application scope and only change them when they have been updated.

This would help, but the next logical extension would be to do 
additional caching in the database level, and control your cache updates 
programmatically when the rest of the system changes, which could again 
reduce the number of calls that you're making.

> Will using something like iBatis or even Hibernate improve the situation?

It could. Depending on the transactional requirements, OR-Mapping 
systems can often do some pretty intensive between-transactional caching 
in the application level which can help out. Without knowing the 
particulars of your application and its transactional requirements it's 
difficult to know for sure, but it's possible.

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