Re: stored procedures and dynamic queries

--- Richard Huxton <dev(at)archonet(dot)com> wrote:
> Ivan Sergio Borgonovo wrote:
> > On Tue, 04 Dec 2007 08:14:56 +0000
> > Richard Huxton <dev(at)archonet(dot)com> wrote:
> >
> >> Unless it's an obvious decision (millions of
> small identical
> >> queries vs. occasional large complex ones) then
> you'll have to
> >> test. That's going to be true of any decision
> like this on any
> >> system.
> >
> > :(
> >
> > I'm trying to grasp a general idea from the view
> point of a developer
> > rather than a sysadmin. At this moment I'm not
> interested in
> > optimisation, I'm interested in understanding the
> trade off of
> > certain decisions in the face of a cleaner
> interface.
>
> Always go for the cleaner design. If it turns out
> that isn't fast
> enough, *then* start worrying about having a bad but
> faster design.
>
I don't know about "clean", but the mantra here is
"First you make it provably correct, then you make it
fast."

I am a fan of making things as simple as practicable,
BUT NO SIMPLER. I don't know how that meshes with
"clean", but the general idea is right.

If you look at my code, my C++ and my java code is
infinitely better than my SQL, so with either of
those, I end up making fewer variants, and my starting
point is generally much closer to my end point, and
that is just a function of experience. With my SQL
code, I generally find myself producing a variety of
scripts to support a given task. And these include
exploration of just about everything SQL has to offer.
I TRY everything, from joins to correlated subqueries
to stored procedures to views, and beyond. And I am
still trying to develop an intuition as to which
options will serve best in a given situation,
analogous to design patterns I routinely use in my C++
and Java code.

Your reaction to Richard's advice to test seems to
imply you want a more direct approach to "THE" answer.
That is chasing something that doesn't exist! I tell
you, as a developer (and NOT a sysadmin), there is no
substitute for experience and THAT can ONLY be
obtained through trial and error. I haven't seen any
books about design patterns in SQL (while there are
plenty in C++ and Java, and other languages), so there
is no short cut.

In short, the general idea most developers I know work
with is "code and test, and then code again and test
again, until you've tried the variety of options that
exist."

There is no substitute for testing yourself. I have
seen repeated advice to replace correlated subqueries
by left joins, claiming that ubiquitous experience is
that the joins are faster, and yet, in my tests, in
most cases there was little difference in speed while
in others the correlated subqueries were faster. So
it appears the advice was based on experience with
dated software and the RDBMS in question had
subsequently dramatically improved how it handled
correlated subqueries. And my use of EXPLAIN
confirmed what I was finding through testing.

> > Most of the documents available are from a
> sysadmin point of view.
> > That makes me think that unless I write terrible
> SQL it won't make a
> > big difference and the first place I'll have to
> look at if the
> > application need to run faster is pg config.
>
> The whole point of a RDBMS is so that you don't have
> to worry about
> this. If you have to start tweaking the fine details
> of these things,
> then that's a point where the RDBMS has reached its
> limits. In a perfect
> world you wouldn't need to configure PG either, but
> it's not that clever
> I'm afraid.
>
I am not sure I buy this, if I properly understand it.
Trust me, I have written some really bad but simple
queries that took hours to complete a task that was
completed in less than a minute with smarter code.
And yet the "bad" code I'd written was similar in
nature to examples used in some texts to explain ideas
in SQL. The point is, until you get extensive
experience in SQL programming and optimization, you
won't know what is bad code until you test it.

Personally, I rely on the sysadmin to administer the
RDBMS properly, to ensure it is configured
appropriately for our application, AND I ask his or
her advice and input on how I design and implement my
SQL code, as well as for input on distributed
application architecture. You can't do it all. On my
development machine, I just use whatever the default
configuration is, so I have it up and running in a
flash and can focus on my development. I'll change
that configuration ONLY if the sysadmin tells me there
is a problem with the default. My advice, therefore
is forget about configuration issues and focus on
getting your SQL right, and then fast, and let your
sysadmin advise, and possibly help, with changes to
your configuration should he or she feel it needs to
be modified to better represent how your application
will behave once in production.

> Keep your database design clean, likewise with your
> queries, consider
> whether you can cache certain results and get
> everything working first.
>
Richard, could you possibly clarify what you mean by a
"clean design"? Is it different from what I normally
do with regard to ensuring all the requisite data is
available, properly normalized, with a suitable suite
of indeces, keys, &c., and as simple as practicable?
(That is, over-simplification is avoided.) I also
tend to ensure that all user access to the data is
through either a stored procedure or a read only view
(perhaps with a little paranoia thrown in ;).

Ted