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

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.