"Roy Hann" <specially@[EMAIL PROTECTED]
> wrote in message
news:iYydnTlMkq9hZ_3VRVnyvwA@[EMAIL PROTECTED]
> > We had something we called "dynamic"
> > SQL as well. But that involved generating SQL at run time, and
letting
> > an
> > SQL interpreter parse it. I suspect you are using "dynamic" in a
> > different
> > sense than I am, but I'm not sure.
>
> I mean something different (I think). Dynamic SQL is somewhere
intermediate
> between embedded SQL and coding to an API directly. With embedded SQL
you
> somehow construct your SQL statement on the fly by concatenating strings
or
> whetever. (It doesn't matter where it comes from; you could even get the
> user to key it by hand at run time.) You then send the SQL statement
off
to
> the server and use the DESCRIBE statement to get a descriptor back that
> tells you what the output (if any) of the query is going to look like.
You
> then set up a buffer with a sufficient amount of storage and a
description
> of where each column value will go in the buffer (the details are host
> language-specific). You then tell the server to execute the statement,
and
> then fetch successive rows into the buffer and iterate over the returned
> columns. It sounds complicated, but having done it once you find (1)
it's
> not that hard after all, and (2) the same bit of code is reusable
virtually
> everywhere you need to do such a thing.
>
Very interesting. It's going to take me a while to absorb this. In the
meantime, a reaction.
"On the fly" could mean several things, and it could be done for several
reasons. In my experience with people using dynamic SQL (with DEC
Rdb/VMS)
there were two kinds: computer linguists who were building expert systems
that verged on artificial intelligence, and people who didn't want to do
a
thorough data analysis before writing code. These two groups had very
different reasons for deferring the construction of the query to the last
possible time.
> > In any event, the interesting thing is, IMO, not so much at what
point
> > the
> > SQL is parsed and bound, but how automatic the process is of
generating
> > the
> > SQL needed to reference and maintain a new code table.
>
> That's a "how long is a bit of string" question. It could be easy or
you
> could want all kinds of bells and whistles.
>
> > Back in the day,
> > new code tables didn't occur often enough to do some heavy automation
of
> > the
> > process, but the necessary coding was so well understood that the
manual
> > effort was relatively trivial.
>
> Agreed, although I suspect the turnaround would still be measured in
days
at
> least, whereas adding a row to a lookup table takes seconds. The case
we
> have to answer is why we think those days are worth it. My
argument--which
> I won't spell out here--is that someone still spends days dealing with
the
> new code, it's just someone else and they do it later, over and over
again.
> The net benefit to the business of being able to improvise new codes
> willy-nilly will usually be near zero or worse in the long term. I
currenty
> deal with two systems that have development teams that are maybe 25-50%
> bigger than they need to be to because productivity is so low owing to
> mysterious bits of the business model that are encoded in EAV and OTLT
> tables instead of being made manifest in the database design. The
knowledge
> of these things is transmitted orally, like the folklore it is, and then
> code written to manifest the real business model at run time.
>
> > So the idea of storing all in one table "to
> > save programming effort" seemed like a bad joke to me. And the
"delay
> > due
> > to the cranky DBA" was not the kind of issue in my context that it is
in
> > some of today's programmers.
> >
> > In all my years of visiting newgroups and other forums, I've never
quite
> > learned why programmers think that database programming is so
difficult.
>
> It is, doing it the way some of them do it. I just finished a re-write
of
a
> batch job that consisted of over 14,000 lines of code and sent 2.5
million
> queries to the server, and ran for 4.5 hours. When I'd finished with
it,
it
> was 600 lines of code and it sent 6 queries to the server and ran in 12
> seconds. And I am pretty sure one of the six queries isn't needed.
>
I think the best I ever did was 15 minutes to 6 seconds. The amazing
thing
to me is that almost all of the truly doggy code I've seen in database
work
was justified as "we did it that way for efficiency". People who try to
optimize an application by doing manually what a good query optimizer
does
mechanically almost always slow themselves down.
Back in the 80s, you saw the same thing among people who spent too much
time trying to write applications that would cause fewer page faults,
instead of getting the logic right.
> > I
> > think this discussion might help me understand that. Thanks for a
> > considered reply.
>
> Roy
>
> Repeat:
> The net benefit to the business of being able to improvise new codes
> willy-nilly will usually be near zero or worse in the long term.
This is another jewel from you, Roy. Thanks for expressing it so clearly.
I think almost all the people who think the opposite way work for a
software
vendor, and not for the client who will end up using and/or struggling
with
the business data.
|
