Re: What's the best alternative to this pl/sql ?

Karsten Farrell wrote:

> damorgan_at_exesolutions.com said...
> (snip)
> >
> > If the source of the SQL is some front-end I'd advise against putting any SQL
> > there at all ... compiled, prepared, or otherwise. Passing SQL statements across
> > a wire is not an efficient use of resources.
> >
> > Daniel Morgan
> >
>
> I agree with you 100% ... in principle, at least. However, if I wrote a
> stored procedure (or even if I put them all in a single package) for
> every one of the hundreds (thousands?) of source SQL we're passing from
> our Java apps to the db, I'd have to get very creative with the
> procedure names - Get<colnames>, Get<colnames>_When_Sorting_<colname>,
> Get<colnames>_When_Joining_With_<tablename>, and so on.
>
> Is it ok if I act as a name-dropper? Paraphrasing what Tom Kyte said,
> they should never have added the Statement to the JDBC spec; only
> allowed PreparedStatement.
> --
> /Karsten
> DBA > retired > DBA

You can drop Tom's name as I have the greatest respect for his opinions. My problem with front-end SQL at its simplest are

(A) Difficulty to optimize during development
(B) Difficulty to debug
(C) Inefficient use of network resources
(D) Impossibility to optimize in production

You can argue "hundreds (thousands)" of SQL statements but my blanket generalization is that if you are dynamically generating them you are making inefficient use of indexes and if the DBA adds or removes indexes while attempting to tune the back-end your SQL is incapable of responding to the change.

A judicious use of dynamic SQL will allow the very same SQL statements to be built and executed in the back-end and will allow the developers to control WHERE clause creation to optimize performance and scalability.

I'll grant you that the above has been the subject of more than a few religious wars so this is just my opinion after looking at a lot of SQL statements written by C, C++ and Java developers. The result of which is usually an overwhelming desire to wash my hands.

Daniel Morgan Received on Thu Feb 27 2003 - 18:37:51 CST