Received: from localhost (unknown [200.46.204.183]) by developer.postgresql.org (Postfix) with ESMTP id E527C2E0042 for ; Mon, 31 Mar 2008 23:26:37 -0300 (ADT) Received: from developer.postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 68025-03 for ; Mon, 31 Mar 2008 23:26:34 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.5 Received: from hu-out-0506.google.com (hu-out-0506.google.com [72.14.214.224]) by developer.postgresql.org (Postfix) with ESMTP id E486D2E002D for ; Mon, 31 Mar 2008 23:26:33 -0300 (ADT) Received: by hu-out-0506.google.com with SMTP id 22so157253hug.23 for ; Mon, 31 Mar 2008 19:26:32 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=beta; h=domainkey-signature:received:received:message-id:date:from:to:subject:in-reply-to:mime-version:content-type:references; bh=WNq3YXZf2dlbtx5waNZFzw4HwolORjR07ch4G/gWUlo=; b=U1T2hiDekUPF6lVDIqVtWGU1wWRrshzFHP9drw9UaESYOv1dpLGilE+cMbxt3uelZoQg//6qL0buC70yYgBitwAlJYQs+OQt3jfxBvPWNIs1NhE7Oc7+zAfDWuAdQ+mLISy7w3kANGNyoa6QcI/VMOEmP0+s9XMae5kqxHO6vX8= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=beta; h=message-id:date:from:to:subject:in-reply-to:mime-version:content-type:references; b=H7coRHTfXCV8SBqLtWCNXhXHcrKRbhDKFHeZU7sYfUB7Vq4G/bMm2S09qxuEF+1FvwHuFKaFGoV8Z1j+7fHpvxh87X+7vVJiFjAKr9Qbw3zlA+aPAtb+vRL15bCxSFAHcJG+gQP4UVksQ21/poGEi/5ldAwEkRK0th+3KS+8bHk= Received: by 10.82.167.9 with SMTP id p9mr18962448bue.7.1207016791530; Mon, 31 Mar 2008 19:26:31 -0700 (PDT) Received: by 10.82.112.18 with HTTP; Mon, 31 Mar 2008 19:26:31 -0700 (PDT) Message-ID: <36af4bed0803311926v20f9c0cbkbd2869232dc547a@mail.gmail.com> Date: Tue, 1 Apr 2008 07:56:31 +0530 From: "Robins Tharakan" To: pgsql-performance@postgresql.org Subject: Re: Performance Implications of Using Exceptions In-Reply-To: <36af4bed0803311923u67641f1w489acad42412fb3d@mail.gmail.com> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_26446_25451344.1207016791474" References: <7a4208ef0803311720s1dac8362gff2477d3dc534968@mail.gmail.com> <20080401005235.GM4999@tamriel.snowman.net> <36af4bed0803311923u67641f1w489acad42412fb3d@mail.gmail.com> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200803/539 X-Sequence-Number: 29493 ------=_Part_26446_25451344.1207016791474 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline 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 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/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 > > > > ------=_Part_26446_25451344.1207016791474 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline 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/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


------=_Part_26446_25451344.1207016791474--