RE: SQL Syntax -- MIDRANGE-L

This is a multipart message in MIME format.
--
[ Picked text/plain from multipart/alternative ]
If you think this is fun, you ought to try figuring out blocking factors
and what not for traditional database access.

Or the programmer who worked here many years ago who didn't know that RPG
had a SETLL and READ.  No sh!t.  You should have seen her prompt screen
logic for a look up on a two digit keyed file.  Chain 99 times and fill
the array if the record is found, (S/36 - before subfiles).

Yet some, (not necessarily you) would have the belief that we should not
try to teach people new stuff.  Instead we should dumb down our code to
the lowest common denominator.

I am curious, do you think that SQL should build indexes, (logical files),
on the fly and leave them there?

Do you suppose that traditional DB access would say, "Instead of reading
all records, then sorting them in a large array by a particular field,
have you thought of building a logical file that would access them in that
fashion?".  Oh heck no.

Rob Berendt
--
"They that can give up essential liberty to obtain a little temporary
safety deserve neither liberty nor safety."
Benjamin Franklin




"Joe Pluta" <joepluta@PlutaBrothers.com>
Sent by: midrange-l-admin@midrange.com
10/29/2002 01:17 PM
Please respond to midrange-l

        To:     <midrange-l@midrange.com>
        cc:
        Fax to:
        Subject:        RE: SQL Syntax


> From: jpcarr@tredegar.com
>
> As you said earlier,  you can write bad native DB2 accesses too.
> Instead of a chain,  I could read the whole file comparing the
> key value to the one just read.
> That would be stupid.

Yeah, but this is similar to Leif's security issue.  One is a programming
flaw - one that can be fixed by the application programmer.  The other is
"under the covers", and requires some sort of SQL troubleshooting to
identify.  Perhaps those are the same to you, but they're not to me.
Because to do the latter, everybody in my shop has to become an SQL
performance expert.


> I didn't say I didn't know, I just didn't know with the info you
> provided.   Go into Ops Nav play with the visual explain. it will
> show/tell you every step of the way what is being built and why.

Not to my eyes, and I'm usually pretty good at reading dense, obtuse log
files.  After wandering around for a bit in OpsNav (how cool - an
additional
PC required to debug my code!), I found the Visual Explain.  Nothing in it
told me I was doing anything wrong.  Each contains hundreds of lines with
information such as "Derived Selection Performed YES".  Hmmm.  That's
pretty
intuitive.

So I decided to compare the results, one with the logical view in place
and
one without.  Since OpsNav doesn't allow me to dump the contents of the
files to an output file, I had to manually review them.  Hundreds of
lines.
But I dogged along and found out the crucial difference:

The slow one (without the logical view) says:

ODP Implementation NONREUSABLE
Dynamic Replan Reason Code ACCESS PLAN WAS NOT REBUILT

as opposed to the fast one (with the logical view):

ODP Implementation REUSABLE
Dynamic Replan Reason Code A NEW INDEX EXISTS OVER A TABLE IN THE QUERY

Well, there it is plain as day!  My ODP implementation was NONREUSABLE!
Heck, I shoulda known that.

Yes, I'm being a bit sarcastic, but really, unless you're an SQL expert,
how
in the world aere you going to find, among the hundreds of lines of
gobbledygook in the Visual Explanation, the reason why your SQL syntax is
bad?

Finally, just for fun, I went back to my original syntax, the one that was
fast, expecting to see that it was REUSABLE.  And you know what?

It says:

ODP Implementation NONREUSABLE

Huh?  But... but... NONREUSABLE was what made the first one slow.  Or so I
thought.  What the heck is going on here?  Oh wait, the reason code is
different:

Dynamic Replan Reason Code PLAN REBUILT DUE TO SYSTEM PROGRAMMING CHANGES

Evidently NONREUSABLE with PLAN REBUILT DUE TOP SYSTEM PROGRAMMING CHANGES
is better than NONREUSABLE with ACCESS PLAN WAS NOT REBUILT.  Another leff
than intuitive result.  And still, nothing to really explain why one
version
take 50 times longer than the other, even though both were NONREUSABLE.

Besides that, how could I have known just from the information in the
first
one that it was wrong?

If this doesn't explain my frustration with SQL, then nothing will.
Perhaps
you think this is a normal way to program - write statements, find the
ones
that don't perform well, then dig through the SQL performance monitor to
find out which syntax is correct.  Me, I'd much rather have a decent
database and somebody who still remembers how to program.


> With SQL you could add an Index and "It's not stupid anymore"
> with my "read the whole file" scenerio,  "It's stupid for life"

I think I touched on this.  With SQL, even s amart programmer can do
stupid
things without knowing it.  With native DB2, it requires aggressive
stupidity to do something that bad.


> The debug info gives suggestions as to what would make it run
> faster.   No message will tell you how to make Native Methods faster.

If you need a debugger to tell you how to write your code, you might want
to
find a new line of work.


> I use Native DB2 methods everyday for the last 20 years,
> I also use and really appreciate what SQL can give me
> for adhoc DB searches/retreivals

Here we agree.  SQL is unparalleled for ad hoc.  But native is better for
production in many cases.


> PowerSaw, HandSaw,   I have both in my toolbox and use both.

Obviosuly I wouldn't be asking these questions if I weren't trying to use
SQL.  Unfortunately, it's less a power saw than a nuclear-powered laser
torch with random yields.


> Remember also;   SQL = No Level Checks

For those that think this is a good thing, then I guess that's a plus.

Joe

_______________________________________________
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
list
To post a message email: MIDRANGE-L@midrange.com
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/cgi-bin/listinfo/midrange-l
or email: MIDRANGE-L-request@midrange.com
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.