Re: Efficiency vs. code bloat for SELECT wrappers

On Mon, Dec 17, 2007 at 12:49:46PM -0500, Colin Wetherbee wrote:
> Because I know Perl a whole lot better than SQL, PostgreSQL, and even
> the Perl DBI, I'm always inclined to wrap the database stuff in a nice
> little package and forget about it. This method has worked well for me
> in the past, but the project I'm starting is much bigger in terms of
> database use than anything else I've written.

SQL allows you to express some sorts of problems very succinctly and
efficiently, the problem is that you need some experience of SQL before
knowing when is best to use imperative vs relational tools.

Without knowing what sort of thing you're trying to do I'm unable to
give any appropriate examples. I'd definitely say that spending time
getting to know databases has made me much more productive in total.
Mainly because with relational databases you just don't have to worry
about lots of the, mostly distracting, details that you do in imperative
languages.

> You say you write SQL directly in your application code, rather than
> writing task-oriented wrappers. I like that idea, but I can see that
> getting really messy. What is your opinion on how it affects code
> maintenance and things like that?

It's normally very manageable, but it'll depend on where you like to
put your machinery. I seem to be relying more and more on (database)
server side code and most of the UI code just tends to do some initial
simple input validation and then hand all the data over to the database.
My UI code then takes any results back from the database and feeds it
into the appropriate places. I'd guess that maybe 5 to 10% of the lines
in my programs contain some SQL---i.e. two or three lines of SQL for
every twenty or thirty lines. I don't think I'd save much, if any,
lines in total if I tried to put some database abstraction layer in the
middle. I can say with reasonable certainty that it would be much more
complicated to keep everything working as efficiently.

Luckily I've been able to design most of the programs I work on as
relatively simple layers over a database, I'm not sure if you're able to
work like this.

Sam