Re: Subfile "sort" thoughts

I don't think that 9 million rcds could fit into a space whose max size is
still 16mb; then you can't use qsort or similar.

With usridx each entry can be splitted into key and data up to 2000 bytes
total, than you can put all the data to display directly into each entry
without using the associated space (also limited to 16mb).You can fetch
multiple records at one time into a ds and write it directly into usridx (up
to 4095 entries at a time) but in this case you have to read sql 7 times; or
you can fetch records one at a time and load entries one at a time too. Here
we use usridx by managing entries with ile/mi builtin and performances are
much better than db,  especially if you have to reset entries.

However I think that in your case, an index over a view could be the best
approach.

---
Giuseppe

----- Original Message ----- 
From: "Bob cozzi" <cozzi@xxxxxxxxx>
To: "'RPG programming on the AS400 / iSeries'" <rpg400-l@xxxxxxxxxxxx>
Sent: Wednesday, August 13, 2003 6:05 PM
Subject: RE: Subfile "sort" thoughts


> That's an interesting idea.
> I've used user indexes before, but only in the C language on the 400.
> So, the thinking is to stuff the value for the "sort by this field" into
the
> index and put the rest of the data into the other location in the user
> index. I seem to recall that User Indexes have an associated space so that
> might be a good solution.
> Another option I was considering is the SORT APIs. But since I've never
used
> them...
>
>
> Bob Cozzi
> Cozzi Consulting
> www.rpgiv.com
>
>
> -----Original Message-----
> From: rpg400-l-bounces+cozzi=rpgiv.com@xxxxxxxxxxxx
> [mailto:rpg400-l-bounces+cozzi=rpgiv.com@xxxxxxxxxxxx] On Behalf Of
> Chevalier, Rick
> Sent: Wednesday, August 13, 2003 10:54 AM
> To: 'RPG programming on the AS400 / iSeries'
> Subject: RE: Subfile "sort" thoughts
>
> Bob,
>
> I'm not completely convinced this would perform well enough given the
number
> of records you are working with but I'll throw it out as an idea anyway.
>
> What about using a user index to order the records?  The index portion of
> each entry can contain the field to sort by which gives flexibility there.
> Entries can be written or read in large blocks since the API's work with
> buffers so I/O is minimized.  API functionality mimics chain, read, reade
> file access.  There are the API calls though.
>
> Just a thought,
>
> Rick
>
> -----Original Message-----
> From: Bob cozzi [mailto:cozzi@xxxxxxxxx]
> Sent: Wednesday, August 13, 2003 9:52 AM
> To: rpgiv@xxxxxxxxxxxxxxx; 'RPG programming on the AS400 / iSeries'
> Subject: Subfile "sort" thoughts
>
> I have a situation were I need to help implement a subfile sort.
>
> There is a Work-with panel that contains about 7 fields, all of which the
> end-user wants to be able to sort by.
>
> Nothing to unusual about this so far.
>
> But in this situation the file is actually a dynamic file created with an
> SQL UNION ALL statement that ends up producing about 9 million records.
>
> I was thinking about a page=size subfile and will offer the end user a
> filtering option to weed out unwanted transactions.
>
> What I'm wondering is how to attack the subfile sort in this context; 9
> million records+ doesn't lend itself well to any conventional techniques,
> such as sorting a multiple occurrence data structure or even
dynamic/runtime
> querying via SQL or Open Query File as the performance will suck.
>
> So I'm wondering about building an SQL view out of the UNION ALL
statement,
> and then using SQL further (pre-runtime) to create an index over each of
the
> 7 fields. But I'm not sure if this is the right solution.
>
> Comments? Suggestions?
>
>
>
> Bob Cozzi
>
> Cozzi Consulting
>
> www.rpgiv.com
>
> _______________________________________________
> 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.