Skip site navigation (1) Skip section navigation (2)

Re: Performance Implications of Using Exceptions

From: "Robins Tharakan" <tharakan(at)gmail(dot)com>
To: pgsql-performance(at)postgresql(dot)org
Subject: Re: Performance Implications of Using Exceptions
Date: 2008-04-01 02:26:31
Message-ID: 36af4bed0803311926v20f9c0cbkbd2869232dc547a@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-performance
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
> >
>
>

In response to

Responses

pgsql-performance by date

Next:From: Tom LaneDate: 2008-04-01 04:29:35
Subject: Re: Performance Implications of Using Exceptions
Previous:From: Ravi ChemuduguntaDate: 2008-04-01 01:23:00
Subject: Re: Performance Implications of Using Exceptions

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group