help to reduce db round-trip

[jochen <so...@gmail.com>]

Hi all,

When I was using the "validationQuery" and "testOnBorrow" properties to
ensure connection works fine, did it mean two round-trip happened?
Validation query and my SQL query? thus, how to reduce the db round-trip
while the query became huge?

I knew iBatis to mysql would not ensure connection valid while disabling
testOnBorrow, if it is the same toward oracle? is there any workaound to
reduce db round-trip? any help is appreciated, thanks.

Best regards,
Joe

Re: help to reduce db round-trip

[jochen <so...@gmail.com>]

I know the situation that the reliable check is to validate connection by
executing simple SQL query; Actually we used the simple SQL - select 1 from
dual, but the total soft parse is 48%, and 700 queries per second happened
from oracle awr report, which certainly would bring impact to db
performance; Currently we are looking for some workaround to reduce the
frequency of SQL query, Of course, we can reduce the connection borrowing in
order to get transactions down; that is the way which can be used;

I wonder if any other workaound exist to make our db performance better,
more is better for your poor db performance,

On Wed, Jun 10, 2009 at 6:12 PM, Nicholoz Koka Kiknadze
<ki...@gmail.com>wrote:

> I think the only reliable way to check whether connection is valid is to
> execute something using the connection (so it requires roundtrip to db).
> However
> a) this execution happens only once for your transaction (which may include
> numbers of CRUD operations etc)
> b) Validation query is chosen to be simple (like select 1 from dual for
> oracle), so that performance penalty is minimal.
>
> So IMO unless you are designing some realtime application you can stop
> worrying about additional roundtrip stealing maybe a dozen of
> milliseconds...
>
>
> On Wed, Jun 10, 2009 at 7:35 AM, jochen <so...@gmail.com> wrote:
>
>> Hi all,
>>
>> When I was using the "validationQuery" and "testOnBorrow" properties to
>> ensure connection works fine, did it mean two round-trip happened?
>> Validation query and my SQL query? thus, how to reduce the db round-trip
>> while the query became huge?
>>
>> I knew iBatis to mysql would not ensure connection valid while disabling
>> testOnBorrow, if it is the same toward oracle? is there any workaound to
>> reduce db round-trip? any help is appreciated, thanks.
>>
>> Best regards,
>> Joe
>>
>
>

Re: help to reduce db round-trip

[Nicholoz Koka Kiknadze <ki...@gmail.com>]

I think the only reliable way to check whether connection is valid is to
execute something using the connection (so it requires roundtrip to db).
However
a) this execution happens only once for your transaction (which may include
numbers of CRUD operations etc)
b) Validation query is chosen to be simple (like select 1 from dual for
oracle), so that performance penalty is minimal.

So IMO unless you are designing some realtime application you can stop
worrying about additional roundtrip stealing maybe a dozen of
milliseconds...

On Wed, Jun 10, 2009 at 7:35 AM, jochen <so...@gmail.com> wrote:

> Hi all,
>
> When I was using the "validationQuery" and "testOnBorrow" properties to
> ensure connection works fine, did it mean two round-trip happened?
> Validation query and my SQL query? thus, how to reduce the db round-trip
> while the query became huge?
>
> I knew iBatis to mysql would not ensure connection valid while disabling
> testOnBorrow, if it is the same toward oracle? is there any workaound to
> reduce db round-trip? any help is appreciated, thanks.
>
> Best regards,
> Joe
>