On 02/08/13 08:24, Kevin Grittner wrote:
[...]
When working as a consultant, one client was doing everything
client-side and engaged me to fix some performance problems. In one
case a frequently run query was taking two minutes. As a stored
procedure the correct results were returned in two seconds. This same
client had a report which ran for 72 hours. A stored procedure was
able to return the correct data in 2.5 minutes, although it took
another 10 minutes for the client side to process it into the output
format. Stored procedures are not a panacea, however. Writing in a
declarative format is, in my experience, much more important. I saw
one case where a SQL procedure written in imperative form, navigating
through linkages a row at a time, was on pace to complete in over a
year. Rewritten in declarative form it ran in a few minutes. As a
side benefit, the declarative form is usually 10% to 20% the number of
lines of code, and less buggy. For retrieval of complex data sets,
the big thing is to learn to write SQL which specifies *what you want*
rather then trying to specify *how to get it*. -- Kevin Grittner EDB:
http://www.enterprisedb.com The Enterprise PostgreSQL Company
Trust the Planner, Luke!
(Apologies to Star Wars)
Very informative, learnt more in the above, and omitted text, than I
have for a long while - certainly clarified my ideas on the subject.
Cheers,
Gavin
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
