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