RE: Join 2 files with 1 key from each file.

The other thing that can kill performance are the compile options. We
have an SQL program for doing order inquiries that screams when called
in batch (like from our web sites) but unless ALWCPYDTA is set to *YES
when compiling, it runs poorly when called interactively (it tries to do
table scans instead of using prebuilt indexes). 

Matt

-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx
[mailto:rpg400-l-bounces@xxxxxxxxxxxx] On Behalf Of Ellsberry, Steve
Sent: Wednesday, May 04, 2005 2:32 PM
To: 'RPG programming on the AS400 / iSeries'
Subject: RE: Join 2 files with 1 key from each file.

Hey John,

You have the right idea by creating an existing access path but I'm not
sure it's going to help because of the way you're joining the files.

One thing you can always do to see how to improve performance is put the
job in debug than carefully examine the joblog. The SQL optimizer writes
blocks of records to the log. If you press help on the messages it will
tell you what's going on and if it's building an access path it will
tell you what fields are being used as key fields. 

-----Original Message-----
From: Rusling, John B. (Alliance) [mailto:jbrusling@xxxxxxxxxxxxxxx]
Sent: Wednesday, May 04, 2005 1:22 PM
To: rpg400-l@xxxxxxxxxxxx
Subject: Join 2 files with 1 key from each file.

I have 2 files I want joined and indexed on 1 key from each of the 2
files.

The 2 files are named s1 and s3, both have the common field of Order#
(on) in them

s1 JOIN s3 ON s1on = s3on .

I want the joined file indexed by s1name, then s3itno

ORDER BY s1name, s3itno .


I'm using embedded sql in rpg and everything works fine except the
response time on one of our production machines gets to be 5+ minutes.!

---->
I want to have a prebuilt, index or logical so that when this program is
used, the response time will fall into the acceptable range.

I've built most every combination of logical file (using dds) to this
end but the response time is still horrid.

The s1 file has 2.25 million records and the s3 file has about 2.8
million.

Can anyone give me some guidance on this please?  I've tried everything
I can think of for the last day or 2 and response time is still crappy.


Thanks for any help at all,

John B.

--
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.