Re: Module Design Review -- DB mini language

And when your database gets beyond being toy-sized, how are you going to get the performance tuning done?

For instance, you notice that a particular query takes an inordinate amount of time, how are you going to tell which of your queries generated the SQL? Easy is a single user, single page script, but what about a 45000 line application spread across 80+ CGI scripts, a dozen or so mod_perl handlers, 20+ modules, a clustered Apache enviroment and a SOAP server? (Did I mention that locking/contention issues are hard enough to find and fix without another layer of abstraction?)

Interesting exercise, but remember that SQL has its foundations in relational theory and as such is a very solid base upon which to build.

rdfield

Comment on Re: Module Design Review -- DB mini language

Replies are listed 'Best First'.
Re: Re: Module Design Review -- DB mini language by steves (Curate) on Feb 11, 2003 at 13:16 UTC
I have to agree here. The code I work on is very SQL heavy. We use Oracle for most of it. I've seen poorly written Oracle queries that take hours to run that are pared down to minutes or seconds when the SQL is rewritten. This is one of the big problems I have with most SQL abstractions: They don't allow you to tune the SQL. We typically hand off our poorly performing SQL to DBA's who are well versed in this area. Even if you could hand them off the resulting SQL here, how would you roll their changes back into the abstraction? One approach to how to handle SQL across multiple DB's and allow for tuning is the one OpenInteract uses. They provide a place to "hang" each piece of SQL that allow for per database tuning. SQL, unfortunately, is neither standard or predictable across different databases. I get the same angst when it's suggested we move from database A to database B. The people asking for that generally are not aware of how much work is involved rewriting the SQL when such a move is made. I think abstractions are always worth thinking about and this particular one may address your needs, so I don't think it's necessarily a wasted effort.	[reply]

Replies are listed 'Best First'.

Re: Re: Module Design Review -- DB mini language
by steves (Curate) on Feb 11, 2003 at 13:16 UTC

I have to agree here. The code I work on is very SQL heavy. We use Oracle for most of it. I've seen poorly written Oracle queries that take hours to run that are pared down to minutes or seconds when the SQL is rewritten.

This is one of the big problems I have with most SQL abstractions: They don't allow you to tune the SQL. We typically hand off our poorly performing SQL to DBA's who are well versed in this area. Even if you could hand them off the resulting SQL here, how would you roll their changes back into the abstraction?

One approach to how to handle SQL across multiple DB's and allow for tuning is the one OpenInteract uses. They provide a place to "hang" each piece of SQL that allow for per database tuning.

SQL, unfortunately, is neither standard or predictable across different databases. I get the same angst when it's suggested we move from database A to database B. The people asking for that generally are not aware of how much work is involved rewriting the SQL when such a move is made.

I think abstractions are always worth thinking about and this particular one may address your needs, so I don't think it's necessarily a wasted effort.

[reply]