RE: SQL vs. READ Performance Opinions

> From: Brian Piotrowski
> 
> Let me give you an example - we have a physical file that contains
over
> 100,000 records.

That's not a lot of records, really.  Using native I/O, you can pretty
easily read 10,000 records a second; probably more with blocking.


> We have a program that searches from a "FROM" date and
> reads to either the end of the file or the "TO" date, whichever comes
> first.  The program that does the READ statement uses a logical file.
> The SQL statement uses a BETWEEN parameter to only select those
records
> that match the indicated criteria.

Yes, but what does it DO with the data?  Are you updating each record?
Writing to another file?  Or are you summing a field and getting a
total?  What you wish to do very much dictates how you should do it.


> >From what understand, there would be a disk I/O for every record it
> needs to read between the FROM and TO dates.  However, with an SQL
> SELECT statement, I would only encounter a single disk I/O.

Again, what would you be SELECTing?  If you're selecting 10,000 records
into a multiply occurring data structure, that's different than
selecting the SUM of some field in those 10,000 records.  But in either
case, the system still needs to go out and get those 10,000 records from
disk.  Either it gets them one at a time or gets them in a block.  When
a block is read, the more records that can be used from that block the
better.  So, if your data on disk is in the same order as the data you
are reading, sequential reads will go much faster, because fewer
physical reads need to occur.  But I've never heard of one read getting
all 10,000 records.


> Is this correct?  In the case of this above program, which is the most
> efficient way of getting the data?

And one more time, the answer depends on what data you are getting.
Although I've written some seriously technical benchmarks, I always tell
people that you need to determine the business goal before you can use a
benchmark.  So tell us what you are trying to do with those records.

Joe

P.S. I will be rerunning my massive benchmark tests later this year when
the new V5R3 box comes in.  We can get some release-to-release
comparisons.  But on V5R1, for a simple CHAIN operation, native I/O ran
14000 records per second, while a single-record SELECT INTO ran 1400
records per second and a FETCH on a cursor ran about 950 records per
second.  I don't have statistics for block reads.