Performance

Thread view:

Marco Schwarz - 04 Jun 2004 10:45 GMT

Hi,

I'm new in this newsgroup and perhaps I have a simple question ...

I writing a programm that push data from db1 to db2.

What is faster?

    select * from table1 where id = x
    if ( resultset.next() )
        update
    else
        insert

or

    try {
        insert
    } catch (sqlexcption e) {
        update
    }

Cheers!
Marco

Reply to this Message

Sampsa Sohlman - 04 Jun 2004 10:57 GMT

> Hi,
>
[quoted text clipped - 17 lines]
> update
> }

What about following.

http://dataset.sohlman.com/SynchronizationTutorial.html

It reads all data to memory and then compare it and after that saves
changes to destination db.

- Sampsa

Signature

-------------------------------------------
// Sampsa Sohlman //
// My email can be found on my homepage //
// http://sampsa.sohlman.com //
-------------------------------------------

Reply to this Message

Silvio Bierman - 04 Jun 2004 13:08 GMT

> Hi,
>
[quoted text clipped - 20 lines]
> Cheers!
> Marco

You missed the simplest (and probably cleanest) option:

int updateCount = update_by_key();
if (updateCount == 0) insert_with_key();
else if (updateCount > 1) yikes();

Reply to this Message

Thomas Kellerer - 04 Jun 2004 13:15 GMT

> You missed the simplest (and probably cleanest) option:
>
> int updateCount = update_by_key();
> if (updateCount == 0) insert_with_key();
> else if (updateCount > 1) yikes();

I think this depends on what you expect to happen more often: the insert or the
update. If most of the data is already present then I'd do the update first,
else the insert.

But first checking for the presence using SELECT is definitely much slower.

Thomas

Reply to this Message

Silvio Bierman - 04 Jun 2004 13:45 GMT

> > You missed the simplest (and probably cleanest) option:
> >
[quoted text clipped - 9 lines]
>
> Thomas

That is true but since checking the SQLException in such a way that you can
determine whether the key simply was already there or a more dramatic error
occurred is quite awkward I would only do this if updates are really seldom.

Regards,

Silvio

Reply to this Message

Performance

Free Magazines