RE: RPGIV: Subfile "sort" thoughts

We have had similar problems with our order system, the users want to sort
by everything. We did some analysis and determined the top 10 most common
requested key sets and built the required indexes. Then we built a file that
contained the keysets so that we could limit the "un-prepared" queries to
specific users/groups and to indicate to the users with this capability that
they were about to execute a long running query. Later we even changed the
job priority so that these jobs didn't suck up all of the system resources
during the query run. (Of course we change the priority back after the query
finished)
 
Duane
  

-----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: RPGIV: 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

 


Yahoo! Groups Sponsor   

ADVERTISEMENT
 
<http://rd.yahoo.com/M=259538.3705203.4965331.1261774/D=egroupweb/S=17050067
64:HM/A=1712983/R=0/SIG=11u38u3s2/*http://hits.411web.com/cgi-bin/hit?page=1
374-105951838331032> click here 
 
<http://us.adserver.yahoo.com/l?M=259538.3705203.4965331.1261774/D=egroupmai
l/S=:HM/A=1712983/rand=343857826>       

RPG World 2003 in Anaheim.
November 10 to 12, 2003
More info at: http://www.rpgworld.com <http://www.rpgworld.com> 


To unsubscribe from this list send and email to:
rpgiv-unsubscribe@xxxxxxxxxxxxxxx


Your use of Yahoo! Groups is subject to the Yahoo!
<http://docs.yahoo.com/info/terms/> Terms of Service. 





NOTICE: This electronic mail transmission may contain confidential
information and is intended only for the person(s) named.  Any use, copying
or disclosure by any other person is strictly prohibited. If you have
received this transmission in error, please notify the sender via e-mail.