Re: Efficiency vs. code bloat for SELECT wrappers

--- Sam Mason <sam@samason.me.uk> wrote:

> On Mon, Dec 17, 2007 at 01:29:10PM -0500, Ted Byers
> wrote:
> > I routinely keep my SQL code distinct from my
> Perl,
> > java or C++ code.  When a client program needs to
> do
> > something with the database, then either a child
> > process executes a script I have written, if the
> > client program doesn't need to do anything with
> data
> > drawn from the database, or I have all the SQL
> code in
> > one or more stored procedures, and use the
> appropriate
> > client interface to invoke the stored
> procedure(s).
> > Whether the SQL is in a specific script or in a
> stored
> > procedure, my SQL code is kept distinct from the
> > client code, regardles of the language I have used
> for
> > that.  I find this even MORE useful as my projects
> get
> > bigger.
>
> Humm, this discussion is horribly domain specific.
> Abstractions are the
> bread and butter of programming and designing them
> appropriately makes
> the difference between things turning into a mess
> later on or not.  The
> only solid rules I stick to is that when I'm working
> to solve a problem
> I've not solved before, I will get it (i.e. the way
> I go about solving
> the problem, getting the right answer is generally
> pretty easy) wrong
> the first time and probably only get it reasonable
> the third or fourth
> time.
>

Right.  But there is a quicker way.  I like to work
with people who have experience that is different from
mine, expecting they will have seen a different suite
of problems and found solutions I have come to rely
on.  In that way, in discussing my current problems, I
can learn something new, and that much faster than
trial and error.  Of course, there are always test or
proof of concept programs, where I test ideas.  For
example, I have routinely been advised that left joins
are faster than correlated subqueries.  In the past
month, I have found two problems requiring either a
correlated subquery or a left join, and in one case
the correlated subquery was more than ten times faster
while in the other the left join was about twice as
fast.  In both cases, the results returned by the two
approaches were identical, but there were significant
differences in performance; most astonishing in the
one case that proved to be quite different than
expected.

> > Each kind of abstraction has its place.
> > It is up to the analyst or architect to figure out
> how
> > many layers and what abstractions are appropriate
> for
> > a given project.
>
> Indeed.  But to be able to choose a suitable set of
> abstractions, it
> helps for the designer to know the ins and outs of
> the tools being
> used.  I was trying to encourage Colin to look at
> using databases
> in a different way, different styles of programming
> suit different
> applications and hiding the database can have
> detrimental effects as
> well as positive effects.
>
I find the opportunity to look at problems in a
different light priceless.

But some practices generally turn out to be counter
productive.  Producing overly long functions, or
mixing code involving different languages into the
same file, often leads to an unmaintainable mess.
More often than not, such practices are a consequence
of poor design.  But even here, there is no hard and
fast rule, since some algorithms, such as numeric
quadrature or QR factorization of general real
matrices can not be written in a short, simple
function although they logically ought to be one
function in the simplest cases; but even these admit
enhancements that warrant more interesting data
structures and ancillary functions.  But for these
algorithms, which admittedly have nothing to do with
databases, the top consideration is the speed of
provably correct code.  If that means it is too
complex for junior or even intermediate programmers,
so be it.  I have seen and used library code for
number crunching that only a specialist in numeric
methods have a hope of easily understanding, and been
glad I didn't have to write those functions myself.
;-)

For Colin's situation, what I would have suggested
would be a simple extension of what he normally does,
and that is look at an additional layer that gets the
data from the database as efficiently as possible, and
cache it in that layer, providing it to the UI layer
as required.  Similarly, perhaps in a different layer,
get user data from the interface and cache it, sending
it to the database back end at the most appropriate
time in the most efficient way.  And perhaps with all
this, some form of database connection pooling;
although this latter option will depend on how many
concurrent users there may be in the worse case
scenario (or maybe that is the best case scenario
since it would mean that there is significant demand
for what the app does ;-).

I haven't attempted this in Perl, but it is dirt
simple in Java/J2EE.  In a web application, based,
e.g. on Tomcat, it is easy to set this up with as many
layers as one can justify, especially with an IDE like
NetBeans6.  Not having thought about how to do this
using perl, I wouldn't know what to advise regarding
how to put this into practice if one wants to stick
with perl.

HTH

Ted