RE: Subfile "sort" thoughts

rpg400-l-request@xxxxxxxxxxxx wrote:

>   1. Subfile "sort" thoughts (Bob cozzi)
>
>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. 

Bob,

A couple thoughts come to mind immediately. First, we all realize that 9 
million records does not a subfile make. What we're talking about is an 
intermediate work file (work index, work space, whatever). And second, even if 
it were ever necessary to present all of those records through a subfile, at a 
quarter-second per page-down, we're talking over 30 hours. (Wait... 300 hours? 
Ahh, who cares.)

Therefore, the real problem must be in how to access a dynamic subset of the 
rows, i.e., create a dynamic index and then position to a starting row 
somewhere within that index and fetch sequentially for some number of rows. 
Probably optimized for a certain number of rows.

I don't think there's any doubt that you need pre-built indexes to even get a 
good start at this. The question will be what indexes should be built over what 
tables? 

How many tables in the UNION? What are their "normal" sizes? Do any indexes 
already exist? Will there be any selection criteria entered by the user? 
Position-To logic? Is row selection any less important than row order? What are 
the distributions of values within the index columns? (E.g., one potential 
index column might be ShirtSize and have only three potential values -- SMALL, 
MEDIUM and LARGE. I'd figure that's not a good candidate as the major sort 
column while possibly being a selection column; the first million or more 
records would always be LARGE -- it clearly needs to be indexed for selection 
or position-to.)

Distribution of values might even lead to the fringes by bringing in an EVI or 
two to get performance.

Tom Liotta

-- 
Tom Liotta
The PowerTech Group, Inc.
19426 68th Avenue South
Kent, WA 98032
Phone  253-872-7788 x313
Fax    253-872-7904
http://www.powertechgroup.com


__________________________________________________________________
McAfee VirusScan Online from the Netscape Network.
Comprehensive protection for your entire computer. Get your free trial today!
http://channels.netscape.com/ns/computing/mcafee/index.jsp?promo=393397

Get AOL Instant Messenger 5.1 free of charge.  Download Now!
http://aim.aol.com/aimnew/Aim/register.adp?promo=380455