RE: Chaining with a very large file

Properly indexed, SQL will perform much like native I/O.  However, things
get complicated with joins and group by expressions, and the SQL optimizer
may decide on its whim to create a temporary access path.  When this happens
on a file with many rows, expect to wait for some time....  Many factors can
influence the optimizer to alter its "access plan", such as cardinality of
the result set or sub select...  SQL maintains statistics about this stuff,
and as part of it optimization, will review the source table for changes to
these items.  

Eric DeLong
Sally Beauty Company
MIS-Project Manager (BSG)
940-297-2863 or ext. 1863



-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx
[mailto:rpg400-l-bounces@xxxxxxxxxxxx]On Behalf Of Mike Wills
Sent: Wednesday, February 01, 2006 11:53 AM
To: RPG programming on the AS400 / iSeries
Subject: Re: Chaining with a very large file


I was wondering why the iSeries would take that long. Should a properly
indexed file also not take long in SQL?

On 2/1/06, Art Tostaine, Jr. <atostaine@xxxxxxxxx> wrote:
>
> That's a small file.  It should get the record subsecond.  C'mon you
> serious?
>
> Art
>
> On 2/1/06, Douglas W. Palme <dpalme@xxxxxxxxxxx> wrote:
> >
> > My plan at this point would be to juse handle it via straight RPG
> access,
> > via of two possible logical files depending on the first value.
> >
> > It would be interesting to know how long it would take to locate the
> > record
> > with the chain....1 sec 10 sec , etc., especially since this will be an
> > interactive process or at least one portion of it would be.
> >
> > Thanks for the tip, I know sql is out of the question for a file this
> size
> > and the machine we have.
> >
> > Douglas
> >
> >
> > On Wed, 1 Feb 2006 09:55:44 -0600 , DeLong, Eric wrote
> > > Douglas,
> > >
> > > As long as you don't do things that try to build access paths
> > dynamically
> > >
> > > (such as SQL or OPNQRYF) and you stick to keyed access in RPG, you
> > > should be fine.  I would think that breaking this down into smaller
> > > units would only increase the hassles of managing these file objects,
> > >  especially if they'll all be joined into a common access path
> > anyway....
> > >
> > > If you need to access this monster file via SQL, it's important to
> > ensure
> > > that you have the correct indexes already built.  Performance tuning
> > > in SQL is sometimes a bit like sorcery or alchemy...  You might see
> > > what you want today, but tomorrow it won't work the same....
> > >
> > > Eric DeLong
> > > Sally Beauty Company
> > > MIS-Project Manager (BSG)
> > > 940-297-2863 or ext. 1863
> > >
> > > -----Original Message-----
> > > From: rpg400-l-bounces@xxxxxxxxxxxx
> > > [mailto:rpg400-l-bounces@xxxxxxxxxxxx]On Behalf Of Douglas W. Palme
> > > Sent: Wednesday, February 01, 2006 8:08 AM
> > > To: RPG Group
> > > Subject: Chaining with a very large file
> > >
> > > we may be faced with having to allow an interactive program to do a
> > > chain to
> > >
> > > a very large file; one with approximately 1.1 billion records and a
> > > file size of approximately 9.7gb of disk space.
> > >
> > > Are there any performance issues that I should take into
> > > consideration?  Should I consider breaking the data down into
> > > multiple files?
> > >
> > > The file will have a total of three numeric fields, all packed, 5
> > > digits for
> > >
> > > the first two and 4 digits for the third.
> > >
> > > Suggestions, thoughts, helpful hints.....would all be appreciated.
> > >
> > > If you bought it, it was hauled by a truck - somewhere, sometime.
> > >
> > > --
> > > 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.
> >
> >
> > If you bought it, it was hauled by a truck - somewhere, sometime.
> >
> > --
> > 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.
> >
> >
>
>
> --
> Art Tostaine (GMAIL)
> CCA, Inc.
> Jackson, NJ 08527
> --
> 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.
>
>


--
Mike Wills
koldark@xxxxxxxxx
http://mikewills.name
http://theriverbendpodcast.com

"There are 10 types of people in the world: those who understand binary, and
those who don't." -Unknown