RE: Record seach options - millions of records

Note that if you are using EXISTS, the fields in the select clause don't matter.

Assuming a logical/SQL index over fld2 in MYOTHFILE..

This:

Select fld1

From myfile A

Where exists ( select *
from myothfile B
where A.fld2 = b.fld2
)

Works the same as

Select fld1

From myfile A

Where exists ( select 1
from myothfile B
where A.fld2 = b.fld2
)

In both cases only the MYOTHFILE index is hit to check for existence. The rest
of MYOTHFILE's fields
are not touched.

Charles

-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx
[mailto:rpg400-l-bounces@xxxxxxxxxxxx] On Behalf Of Walden H. Leverich
Sent: Wednesday, May 02, 2007 2:22 PM
To: RPG programming on the AS400 / iSeries
Subject: RE: Record seach options - millions of records

OK, 4,002,000 rows later (messed up a loop <G>) VisualExplain
shows lookup time on the order of 1 to 2 milliseconds per request.

Given you only care about existence of the row, I'd also make
sure you do the lookup so it's "covered" by the index. In SQL
that simply means not asking for any data that's not in the
index itself. In classic IO IIRC, a SETLL that is _not_
followed by a read doesn't go after the base table, just the logical.

For example, "Select 1 from BigFile where Key1='a' and
Key2='b'" would be covered (assuming you had an index on Key1
and Key2) but "select * from BigFile where Key1=..." wouldn't
be covered since the system would need to return fields that
weren't in the index.

-Walden

--
Walden H Leverich III
Tech Software
(516) 627-3800 x3051
WaldenL@xxxxxxxxxxxxxxx
http://www.TechSoftInc.com

Quiquid latine dictum sit altum viditur.
(Whatever is said in Latin seems profound.)


-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx
[mailto:rpg400-l-bounces@xxxxxxxxxxxx] On Behalf Of Steve Moland
Sent: Wednesday, May 02, 2007 12:02 PM
To: rpg400-l@xxxxxxxxxxxx
Subject: Re: Record seach options - millions of records

No need to be confused. I'm the one in personally strange
territory because I've never personally dealt with a million
+ record file table. After this program mode, I'll be able to
say "It's only several million...."

It may be ghosts and goblins on my part from the very distant
past to when a million records would make a 3x box quiver and
blood would be oozing out

from the base of the processor.

My background is slick stuff (in my mind, [smile]) with data,
not with the brute force required for lots of data.

Steve

I'll admit, I'm confused by this. It's only several million rows,

that's

nothing. I'm loading a 4-million row table using a 4-byte

binary and a

14-char field as the two keys now, but I would expect selection from

it

(assuming you have an index) to be subsecond.

Now, I'm a sql guy so I would first think to select your base items

and

the indicator in a single query that included an outer

join, but even
if

you hit the "big" table once for each line in the subfile

it shouldn't

take long.

-Walden

--
Walden H Leverich III
Tech Software
(516) 627-3800 x3051
WaldenL@xxxxxxxxxxxxxxx
http://www.TechSoftInc.com

Quiquid latine dictum sit altum viditur.
(Whatever is said in Latin seems profound.)


--
This is the RPG programming on the AS400 / iSeries

(RPG400-L) mailing
list

To post a message email: RPG400-L@xxxxxxxxxxxx To subscribe,
unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx Before posting,

please take a

moment to review the archives at

http://archive.midrange.com/rpg400-l.

--
This is the RPG programming on the AS400 / iSeries (RPG400-L)
mailing list To post a message email: RPG400-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/rpg400-l.


--
This is the RPG programming on the AS400 / iSeries (RPG400-L)
mailing list
To post a message email: RPG400-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/rpg400-l.

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.