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 <[EMAIL PROTECTED]> 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 < > [EMAIL PROTECTED]> wrote: > > > Stephen Frost wrote > > > * Ravi Chemudugunta ([EMAIL PROTECTED]) 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@postgresql.org) > > To make changes to your subscription: > > http://www.postgresql.org/mailpref/pgsql-performance > > > >
