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

[Evan Harris <spanner@xxxxxxxxxx>]

Hi Bruce

A fair reply. Maybe I let my cynicism get a little too much to the fore. 
It's been known to happen on the odd occasion ;)

Regards
Evan Harris

> 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.