Re:

Wilt, Charles wrote:

True, however depending on the range of keys, the SQL may perform faster.

If he's only interested in 1-10, it doesn't matter.

If he's interested in 100,000 - 500,000, then the question becomes where does the missing key fall?
If it's at 100,005, then the RPG will find it faster. If it's at 499,995 then SQL will.

How do you come to this conclusion? Intuitively, it seems to me that SQL has to build a temporary table with nearly 500,000 entries, done by reading every record in the table (and this is the BEST case scenario for SQL vs. RPG - where the hole is at the very end of the file). It then has to reread that temporary table, in effect chaining to the original table for each entry.

How would this be faster than just reading 500,000 records sequentially? There is no magic in SQL, except that it doesn't need to bounce back and forth between the HLL and the SLIC. But I can't see how that will help this particular problem.

If on average, the missing key falls halfway through....the RPG could be faster. Especially if you do
the following:

Build an SQL index on FIELD
Create a DDS logical containing just FIELD, and keyed on FIELD.

This way, your RPG program is bringing in the bare minimum of data. In addition, the logical will
share the access path from the SQL index, so the data will be brought into memory in 64K(?) chucks,
which will provide a big performance boost.

In this way, I would be willing to bet that the RPG will always be faster. You're simply reading through the index.

Joe