Re: Reading sequential files in COBOL vs SQL

Mike wrote:
"Mike" <mike@xxxxxxxx> wrote in message news:UygBi.30925$RX.30834@xxxxxxxxxxxxxxxxxxxxxxxxxxxxx
|
| "Elliot" <eselick@xxxxxxxxx> wrote in message news:1188399391.593464.227160@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
||
|| Hi everyone,
||
|| I'm writing an OPM COBOL program that needs to process a large number
|| of sequential files (around 4,000 records). It's a parsing algorithm
|| that needs to find a few records at an unpredictable location in the
|| file. Obviously in COBOL I'd having to allow for reading a high
|| percentage of the records in each of the files.
||
|| Alternativey I could embed the SQL.code inside of the COBOL.
||
|| Which would be faster? Is the answer obvious or should I test it both
|| ways. Would the relative speed depend on other factors, ie size of
|| computer, memory, hard disk and OS400 version? The program will be run
|| on many different machines.
||
|
| Elliot,
|
| I think any experiments on any hardware / software configuration will yield results like those I predict below.
|
| If the files are already in the database and have an index that would allow keyed retrieval of the records you seek then SQL would
| be much faster than a COBOL or RPG sequential search.
|
| If the data is already in database files but there are no useful indexes, an SQL search might be a little faster because it uses
| lower level file access code than COBOL sequential processing but it will still have to read the whole file to find all the records.
|
| If the files are in the IFS and would need to be copied into a database for an SQL search, then a sequential search of them where
| they sit will be about twice as fast as a copy to DB2 followed by a search.
|
| I hope this helps,
|
| Mike Sicilian

Elliot,

One additional thought. If your program can quit the search as soon as you find the record you are looking for, then a COBOL sequential search will be almost twice as fast as SQL because on average you will quit after reading half the file and SQL will always read the whole file.

Mike Sicilian


Why will SQL "always read the whole file"? If the program declares an SQL cursor and does FETCH requests (for example), it should be possible to quit before fetching all rows.

The OP's problem details are a little sketchy, but the best alternative may depend on whether SQL can be used to some advantage. If the records of interest are "at unpredictable locations" in the file, such that by definition rows must be read sequentially from beginning of file as input to the parsing algorithm, there may not be much advantage to fetching rows via SQL vs native I/O. If OTOH part or all of the algorithm could be implemented by SQL (eg the WHERE clause of a SELECT), it may be more efficient because SQL could filter out extraneous rows/columns and only return pertinent data to the program.

If the data resides in keyed physical files, or in PFs having associated keyed logical files, keyed access may be possible using native I/O (vs sequential reads).

Re SQL clauses to specify needed results, these might apply:
FETCH FIRST n ROWS
OPTIMIZE FOR n ROWS
http://publib.boulder.ibm.com/infocenter/iseries/v5r4/index.jsp?topic=/db2/rbafzmsth2clcu.htm

--
Karl Hanson

.

Relevant Pages

  • Re: Reading sequential files in COBOL vs SQL
    ... Obviously in COBOL I'd having to allow for reading a high ... |> | Mike Sicilian ... |> sequential search will be almost twice as fast as SQL because on average you will quit after reading half the file and SQL will ...
    (comp.sys.ibm.as400.misc)
  • Re: Definition of FACTORY
    ... > taking different elements in the record to create keys. ... COBOL provided nice syntactic file access support initially. ... the SQL part. ... > At a later stage, as need arises, I can include SQL syntax in other ...
    (comp.object)
  • Re: Conversion of data & associated logic from ISAM to RDB
    ... embedded SQL is not necessarily the best solution. ... feed it your ISAM COBOL definition (Source code) and it creates a Relational ... Database in third normal form. ...
    (comp.lang.cobol)
  • Re: Dynamiv SQL and multi-row access SQL from COBOL... some quick questions
    ... EXECUTE IMMEDIATE is for one time dynamic SQL. ... Perhaps you were referring to something other than host variables. ... COBOL using ACCESS 2003. ...
    (comp.lang.cobol)
  • Re: Definition of FACTORY
    ... COBOL code, but see if the following helps. ... invoke os-CustomerFile "readRecord" using Customer-Key returning ... > funny stuff around the SQL queries. ... Anyway thanks for responding. ...
    (comp.object)