|
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
As an Amazon Associate we earn from qualifying purchases.
This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].
Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.