From: Josh Berkus <josh@agliodbs.com>
To: Gary Stainburn <gary.stainburn@ringways.co.uk>
Cc: pgsql-sql@postgresql.org
Subject: Re: multiple lookup per row
Date: Fri, 20 Jul 2001 08:58:45 -0700
Message-ID: <web-88758@davinci.ethosmedia.com> (raw)
In-Reply-To: <01072016271901.18816@gary.ringways.co.uk>
Gary,
> I had the same problem the first time I went from writing sequential
> batch
> mainframe apps to event-driven interactive windows apps. Different
> mindset
> completely.
I grapple with the procedural <--> declarative switchover all the time.
In a way, procedural is easier, since set theory does not come
intuitively to any humans I know. In my biggest pgsql app there's quite
a number of things I did with LOOPs that could have been done with
properly structured queries.
> BTW, which would run fastest, or would there be much difference? (I
> would
> have thought not much as the plpgsql function still has to do the
> same select
> as your sub-selects are.)
Yes, but plpgsql does the selects one at a time, evaluates the response,
and proceeds on to the next. The declarative statement does them all in
batches, as the SQL engine is designed to do. Plus my way and Tom's way
can be built as views, allowing further optimization. Not to mention
that pure SQL will always be faster than even a trusted language.
In other words, my and Tom's queries should run about 50 times as fast
as the procedural method for large tables with indexes.
-Josh
______AGLIO DATABASE SOLUTIONS___________________________
Josh Berkus
Complete information technology josh@agliodbs.com
and data management solutions (415) 565-7293
for law firms, small businesses fax 621-2533
and non-profit organizations. San Francisco
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: josh@agliodbs.com, gary.stainburn@ringways.co.uk
Subject: Re: multiple lookup per row
In-Reply-To: <web-88758@davinci.ethosmedia.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox