>Doug,
>
>It seems that in your case, you might want to think about a databaseserver (like SQL-server) which is able to handle more data than VFP.

I tested this application with MSSQL v7.0, and in that format, it seems to be several orders of magnitude slower in retrieving the datasets.


>As you probably know a VFP table has a size limit of 2 GIG,

Yes Indeed. As a result, my import program breaks the import parsing at 1 million records (approx 1 GIG) as a safety margin.

>This will dramaticly improve your performance, since your programs only has to deal with 1 table. Then you can create a parameterized view to gather your data which match certain filters.

I originally thought this too, but it did not work out in practice. The multiple tables allow me to almost instantly skip over the unneeded records.
because the INDEXSEEK command on a single field table containing not more than 720 records is really fast.


>I'll be probably shot for this one, but what the heck:
>In stead of joining tables in a SQL select, you might want to test a strategy which uses SET RELATION which under certain circumstances can be much faster than a SQL - SELECT with several joins. However determining where you want to use a SQL-SELECT or SET RELATION strategy might be a daunting task.
>
>Walter,

The part of the application that works with the joins, is not critical, and the method I am using (after many tests with configuration and methods) seems to be fast enough. I tested several methods on the data retrieval, and using that [date naming convention index table) seemed to allow for almost intantaneous in its search for table names which meet the date criteria, and then applying the sql to the appropiate tables. (It takes afrom 15 to 40 seconds to process each table in which there are records that meet the search criteris.) Doing the query on one huge SQL database seemed to really take a performance hit and the methods I posted has been the fastest retrieval I have found, but In the event I missed some cutesy function, I posted it for comment.
I do not claim to be a Database GURU, but this R&D project has been a real learning experience and a lot of fun as well. Especially now that there is a commercial application coming out of it all. I did try to use arrays, but not only had problems with getting them to display, the datasets became so large, it sent the program to the swap file area on the hard drive instead if keeping them in RAM and that was another performance hit. Arrays are great, as long as you don't try to stuff them with a GIG or so of data and still expect get speed.

One more thing that I didn't mention, is that there is another application, a global mapping program, (not FoxPro) which will then take the result
(temp) table and plot it by location and other characteristics on a global map. This is in addition to the indexed view that can be displayed in a grid. The workstation has three 47 GIG SCSI drives plus a CDROM tower which contain the libraries of image files.