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 ?
RobinsOn 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
RobinsOn 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/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.
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.
__________________________________________________________________
