Re: Performance problem hard to solve

On May 21, 10:19 am, Hole <h0lefor..._at_gmail.com> wrote:
> Hi all,
>
> first of all, thanks to anyone on this NG for your support and replies
> to my past and future posts.
>
> Now, the problem:
>
> I work with Oracle 10g XE, Java 1.4.2 on Win XP (but the problem is
> the same with a Unix machine and Oracle 10g).
>
> I need to perform some InsertOrUpdate statements and we thought to the
> following solution:
>
> try to insert, catch the SQL exception if the row already exists then
> perform the update statement.
>
> This solution presents excessive performance problem. Moreover, with
> the current application design and architecture, this solution seems
> the one applicable.
>
> With some profiling tools, we've noticed that the bottleneck is on DB.
> Java code takes the 10% of overall execution timing...It seems the 90%
> is for the violation on DB, when I try to insert a row that already
> exists, and the consequent SQLException.
>
> With a java profiler tool we can't monitor the DB performance.
>
> Anyone can suggest any (free or community edition) performance
> monitoring tools for Oracle 10g XE?
>
> And...anyone can suggest a better solution than "Try to insert, catch
> the exception if the row already exists then update"?
>
> Thanks in advance!
>
> --
> H0le

MERGE INTO immediately comes to mind. Visit:

http://tahiti.oracle.com

or

http://www.psoug.org/library.html

and look up MERGE INTO. It does what you're trying to do in a much better fashion.

David Fitzjarrell Received on Mon May 21 2007 - 11:02:11 CDT