[thelist] Separation of app & db (was: Beginner asp question)

Simon Lee simon.lee at leapforward.net
Thu Oct 17 08:57:00 CDT 2002

> I've been mulling over just this point lately and your tip is
> really provoking some churning of the brain muscles. I am at
> heart a very procedural programmer. However I lean more and
> more on using the database to order my results for me. In the
> last week I've been working on a query to assemble some
> statistics from a grat many results in a booking application.
> I won't go ito details, but I've solved the problem in two
> parts using views in MS SQL2K. The first view links together
> various values in relational tables and the second collects
> total values of sales from the first view for each type of
> item available.
> What I've been pondering over is that I have now rewritten
> this in 3 different ways. If I were using procedural code
> (vbScript in ASP pages) I would have a gut feeling for which
> method is most effective. However in the database there is so
> much that is going on "behind the scenes" that I find it very
> difficult to gain a feeling for which solution is best. I can
> time queries, but beyond that I always find I have a sneaking
> feeling that I could have done it better.
> What this leads me to is whether it is always better to use
> the database, or whether the advantage of clearly-read
> procedural code is worth a performance hit (within certain bounds).
> Is this heresy, or do others find a similar problem?
> .steve
Hi All,

This could be a *real* can of worms we've opened here! ;)

I can see where Rudy's coming from, but I've personally found that using
the database to get back *exactly* what you want is flawed....both from
a performance and scalability point of view. Here's a real problem that
I came across not so long ago:

A client had a web-based application that ran across their intranet. It
used a SQL Server backend, and relied on a custom-written COM object to
build the web-pages that were served by IIS. A number of views, and
functions were used to bring back data, but there were certain
circumstances where the database struggled -- take a view that is
supposed to return back the details of a project -- the sponsor, project
manager, description etc. Each row of data returned dessribed a project.
Now the client says that they would like to see the list of people
working on that project as well as the other information, and here's
where the problem arises. For each project you can have many people
working on it, and so we have a one-to-many relationship. A left join in
this situation is no use as for each project / row returned, you would
end up with as many rows as you had people working on a project. So, SQL
Server now had to find a list of the people working on each project and
then concatenate them all in a string so that this list of people could
be viewed along with the other project information -- this was achieved
by running a function from the select statement.

Now this ran like an absolute dog, for the simple reason that databases
are meant for storing and returning information, *NOT* for concatenating
strings together. If this additional field showing who was working on
the project was removed, then the query / application ran like a dream.
You may be thinking that our database design was flawed, or that we
didn't have the proper indexes setup, but as we had the skills of a SQL
Server database guru working on the project, this wasn't the case.

Unfortunately for this client, their decision to put all the application
*AND* display logic together into this COM object meant that nothing
could really be done without a complete re-write of the application.

This example has only served to reinforce my opinion that a database
abstraction layer is an important *thin* layer to implement on any major
project -- this layer would have handled the above example in the
language of the application, whatever that may be -- PHP, ASP, CF, etc
-- and returned the correct dataset to the application layer. Not only
is performance much better, but your database is now independent of the
application -- scalability is now greatly improved as you can have the
application and database on different servers, or cluster of servers,
*AND* if you have to, you can migrate to another database easily enough
by just changing the database abstraction layer.

There is a slight performance penalty perhaps with this additional
layer, but it keeps code out of the database, which I think is important
for maintainability and readability -- I'm not a big fan of stored
procedures as you can imagine! ;)

Here's how I structure my web applications currently:
	~ Display Logic
	~ Application Logic
	~ Database Abstraction layer
	~ Database

So, that's my tuppence worth.....I'd be interested to hear what all you
gurus out there think of this, and how you organise your web apps!


More information about the thelist mailing list