Re: Performance Implications of Using Exceptions

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(at)pobox(dot)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(dot)Denne(at)datamail(dot)co(dot)nz> wrote:
>
> > Stephen Frost wrote
> > > * Ravi Chemudugunta (chemuduguntar(at)gmail(dot)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/static/plpgsql-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(at)postgresql(dot)org)
> > To make changes to your subscription:
> > http://www.postgresql.org/mailpref/pgsql-performance
> >
>
>