RE: -- MIDRANGE-L

-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx [mailto:midrange-l-
bounces@xxxxxxxxxxxx] On Behalf Of Joe Pluta
Sent: Wednesday, April 16, 2008 10:14 AM
To: Midrange Systems Technical Discussion
Subject: 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.

You're correct given the way you've coded the SQL.

However, my conclusions' were based on Michael's SQL. While not 100%, I'm pretty sure his SQL won't
need the temp table or read the index more than once behind the scenes.

But testing would tell us for sure.

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.

My conclusions assume Michaels' SQL will read through the index once.

Thus, the SQL would be faster....

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.

Once consideration, is there any way to get SQL to stop without looking at them all? Given and index
over FIELD, and the use of MIN(), seems like a start query engine could know to stop looking once it
hits the first.

Is DB2 smart enough?

Would OPTIMISE FOR 1 ROW help?

Just some thoughts....

Charles

This e-mail transmission contains information that is intended to be confidential and privileged. If you receive this e-mail and you are not a named addressee you are hereby notified that you are not authorized to read, print, retain, copy or disseminate this communication without the consent of the sender and that doing so is prohibited and may be unlawful. Please reply to the message immediately by informing the sender that the message was misdirected. After replying, please delete and otherwise erase it and any attachments from your computer system. Your assistance in correcting this error is appreciated.