Coming to think of it.

Would it fine to consider that an UPDATE query that found no records to
update is (performance wise) the same as a SELECT query with the same WHERE
clause ?

As in, does an UPDATE query perform additional overhead even before it finds
the record to work on ?

*Robins*


On Tue, Apr 1, 2008 at 7:53 AM, Robins Tharakan <robins@pobox.com> wrote:

> I get into these situations quite often and use exactly what stephen
> pointed out.
>
> Do an Update, but if not found, do an insert. Its (by and large) better
> than your version 2 since here you may skip running the second query (if the
> record exists) but in version 2, two queries are *always* run. And
> considering that exception is heavy, this may be a good attempt to give a
> try as well.
>
> update person_room set seat = s where (person_id = person) and (room_id =
> room);
> if not found then
> insert into person_room(person_id, room_id, seat) values (person, room,
> s);
> end if
>
> Robins
>
>
>
> On Tue, Apr 1, 2008 at 6:26 AM, Stephen Denne <
> Stephen.Denne@datamail.co.nz> wrote:
>
> > Stephen Frost wrote
> > > * Ravi Chemudugunta (chemuduguntar@gmail.com) wrote:
> > > > Which version is faster?
> > >
> > > In general I would recommend that you benchmark them using
> > > as-close-to-real load as possible again as-real-as-possible data.
> > >
> > > > Does the exception mechanism add any overhead?
> > >
> > > Yes, using exceptions adds a fair bit of overhead. Quote from the
> > > documentation found here:
> > > http://www.postgresql.org/docs/8.3/s...l-control-stru
> > > ctures.html
> > >
> > > Tip: A block containing an EXCEPTION clause is significantly more
> > > expensive to enter and exit than a block without one. Therefore, don't
> > > use EXCEPTION without need.
> > >
> > > > Which is more cleaner?
> > >
> > > That would be in the eye of the beholder, generally. Given
> > > the lack of
> > > complexity, I don't think 'cleanness' in this case really matters all
> > > that much.
> >
> > A third option is to update, if not found, insert.
> >
> > Regards,
> > Stephen Denne.
> >
> > Disclaimer:
> > At the Datamail Group we value team commitment, respect, achievement,
> > customer focus, and courage. This email with any attachments is confidential
> > and may be subject to legal privilege. If it is not intended for you please
> > advise by reply immediately, destroy it and do not copy, disclose or use it
> > in any way.
> > __________________________________________________ ________________
> > This email has been scanned by the DMZGlobal Business Quality
> > Electronic Messaging Suite.
> > Please see http://www.dmzglobal.com/dmzmessaging.htm for details.
> > __________________________________________________ ________________
> >
> >
> >
> > --
> > Sent via pgsql-performance mailing list (
> > pgsql-performance@postgresql.org)
> > To make changes to your subscription:
> > http://www.postgresql.org/mailpref/pgsql-performance
> >
>
>