tac@sei.cmu.edu (Timothy Coddington) (03/14/90)
I have an application I'm about implement using Foxpro (a dBase
compatible DBMS). One of the functions I have to perform is to
cross reference a value in a VERY large table.
I would like to get some opinions on how this operation might be
done efficiently.
The Table:
The table is referenced using X and Y values. Associated
with each X/Y combination is a corresponding entry value.
Similar to a 2 dimensional array, but physically organized
as a linear list. It may or may not be sorted on X and
Y.
The table is kept in one (approx 1.8MB) file and organized
as follows:
- 40 character per line (record). All ASCII
- 50K lines (records)
- each line has X, Y, corresponding value, and other
info
- Not all possible X/Y represented in file
- X and Y are both 4 digit numbers
The two most obvious options are:
1. "import" the table into a dbase data file and use Foxpro to
reference table (using FOR clause, FIND, LOCATE, SEEK).
A. Using Index
B. Without index
2. Call an external procedure written in assembly or 'C'. The
procedure would implement a fast search to locate and retrieve X/Y
entry.
Possibly restructure file so it is more efficiently
searched. Would require a utility to do so, since table is
updated periodically. See #1 below under additional info.
Addition information that may be important to know:
1. Table data will not be modified by the application.
However, a new updated version of the table will be
installed periodically.
2. Hardware is PC/AT (compatible), 20MHz, 1MB, no
coprocessor, 40MB ~28ms HD, DOS ~3.2
3. The table lookup operation may have to be performed as
many as 1000 times per report.
Answers to stupid questions:
1. No I can't buy a Cray or other mainframe to do this.
2. No I had nothing to do with how the table is structured.
3. Yes I want to use Foxpro. Referencing the table is only
a small part of what this application must do.
=====> Given your experience, which option(s) do you recommend? Please
elaborate.
1, 2, or Other ???
Some aspects to consider:
Strategy for organizing the dbase file or txt file
Sequence references to take advantage of locality (what
strategy would you implement?)
Index or not to index
To use a dbase file or the original or restructured txt file
Which Foxpro search command to use for dbase file
Other aspects I haven't mentioned
It is unlikely, but "what if" - I installed 2-4MB of memory on the
system. How would your suggestion change?
======> I would greatly appreciate any help(*) you might want to give me.
Thanks in advance!!!!
I'll summarize later.
Tim Coddington (412)268-7712 tac@sei.cmu.edu
(412)244-8557 (8pm-2am EST)
* I need help not hassle. If you plan to nit pick my
application, or respond with useless criticism or suggestions -
don't bother - save the bandwidth!