Re: Call for an API standard for SQL statements

From: Fredrik Bertilsson <fredrik_bertilsson_at_passagen.se>
Date: 24 Oct 2004 00:50:11 -0700
Message-ID: <31f7e57d.0410232350.34733fcb_at_posting.google.com>


"Marshall Spight" <mspight_at_dnai.com> wrote:
> > > > A second extremly useful feature is converting a two-dimensional query
> > > > result into a hierachical view. Lets say that we have a query joinin
> > > > the tables "Order" and "OrderDetail". My framework returns a list of
> > > > "Order" records, but calling the method "getRelatedRecords" on such
> > > > instance, will return a list of "OrderDetail" records. This feature is
> > > > only possible if the framework has knowledge about wich tables are
> > > > participating in the query.
> > >
> > > Yeah, I know *all about* this style of programming, and it leads
> > > to disaster. You end up with commands that generate *hundreds*
> > > of queries for a request that could be done in 3, but by then
> > > it's too baked in to you architecture to extract yourself from it.
> >
> > I am not talkning about replacing relational queries with network
> > navigation. But network navigation can be a nice complement.
>
> I don't think you got my point. The issue is that once you start abstracting
> out the details of queries, the application code becomes divorced from
> the actual SQL being executed, which quickly leads to bad performance.
> The app coder *must* be aware of the consequences of what he's doing
> in order to go a good job.

Converting a two-dimensional query result (with joins) to a hierachical view does not leads to bad performance. It is still the same SQL query. It is just a transformation of the query result.

You are right that a call on the method "getRelatedRecords" could cause an extra SQL query if the related table was not joined in the original query. But the coder can still be aware of consequences. If he wants to avoid lazy loading, he just include the join in the original query.

If we should follow your advice about not to divorce the application code from the actual *physical thing* being executed, we would still be doing assembler. It is the same problem with SQL. Using SQL you are able to make very slow queries on un-indexed columns. The *consequences* can be very different if the SQL follows an index or not. Forcing the coder to type the SQL strings is absolutely not a guarantee for good performance.

Fredrik Bertilsson
http://butler.sourceforge.net Received on Sun Oct 24 2004 - 09:50:11 CEST

Original text of this message