Re: SQL for performance (was RE: Changing database to SQL from DDS)

----- Original Message -----
From: "Evan Harris" <spanner@ihug.co.nz>
To: "Midrange Systems Technical Discussion" <midrange-l@midrange.com>
Sent: Wednesday, January 08, 2003 10:15 PM
Subject: SQL for performance (was RE: Changing database to SQL from DDS)


> While it is true that converting to SQL might make the problem go away, in
> reality this would be a side-effect of re-engineering the application
> rather than a direct benefit of SQL itself. It is also possible that
> re-engineering the application without converting an SQL approach might
> achieve the same result.


and here you touch upon the reality.

I have a program I am working on right now. I am removing about 500 lines
from a 900 line program. I am replacing that with about 15 lines of SQL in a
three way join.

The performance actually has improved. Why? Because I am removing lots of
I/O. The program read from 4 tables. It read records based on partial keys,
not one field out of two, part of the key field. It read the records by
building a lower value key and an upper value key, start the file at the
lower, read the record see if it's beyond the upper limit and then check to
see if this record should even be selected.

All of that logic, plus reading other records, all done by the HLL.

It's all in that one SQL statement. And the engine returns only the records
we want and only the fields we want. And it does it at a lower level that
the HLL can. And, since it uses the optimizer, if we add certain indexes, we
could possibly improve the performance of this program even more. The
complexity is pushed down into the engine. The optimizer, given the
opportunity, can usually do a better job than HLL, even if they do the same
job.

But yes, it is a re-engineering of sorts at the code level.

That's what I said at the outset, it's the embedded stuff. And yes, it can
improve performance.

As for the blocked reads, well, I have been told by many people inside and
outside of IBM, that the blocked reads into arrays of structures outperforms
all HLL routines to do the same thing.

===========================================================
R. Bruce Hoffman, Jr.
 -- IBM Certified Specialist - iSeries Administrator
 -- IBM Certified Specialist - RPG IV Developer

"When I die, I want to die like my grandmother who died peacefully
  in her sleep. Not screaming like all the passengers in her car."

    - Author Unknown