VFP table size limit

[grahamrhind]

Hi all,

I've noted that VFP 9 does not address the issue of the maximum circa 2 Gb table size issue. The response to the moans about this on Microsoft's own website is that users with larger tables should use SQL Server instead. Others (unhelpfully) suggest that users shouldn't need to have such large tables.

I do need to have such large tables as I use VFP's character manipulation features over more than 22 million records, which all need to be "within sight" of the program at one time. I'm only able to do this as the moment as I have been able to split the data into 10 tables by country, as all my incoming data also contains a country code, so it can be processed on that basis. However, it's not particularly handy, to say the least.

Has anybody else found another way of handling very large databases in VFP, or (sacrilege though it may be to mention it here), is there an alternative database program to VFP which is able to handle the large file sizes and which also has VFP's magnificent data/character manipulation talents?

Thanks for any thoughts.

 

[Mike Gagnon]

grahamrhind

I've noted that VFP 9 does not address the issue of the maximum circa 2 Gb table size issue.

is there an alternative database program to VFP

This maximum size is not only attributed to VFP9.0 (or FoxPro in general), it applies to Tables and Databases in general for other platforms as well.(Other than SQL).
If you need to handle such large data sets, although you seem reluctant to it, use the SQL back-end and VFP as a front end.



Mike Gagnon

If you want to get the best response to a question, please check out FAQ184-2483 first.

 

[letsgotovegas]

Hi

There is a product called WinDev at

http://www.windevus.com

that will hold around 600 billion records and no limit on the file size and each record can be 65,000 bytes. Your hard drive is the only limit. But I don't think you can address there database threw fox. I don't know, but they have laaaaaaaaarge files. It's a 5GL langauge and it is a fairly fast langauge but proably doesn't compare to vfp in speed? It doesn't compare to VFP OOP's and data centric langauge. They use thier own Hyper File system which I think no one could ever reach the limit. So if you need large files this is your answer. Ken Levey in an interview in december said foxpro will never be going to larger files. Hope this helps.

Kind Regards
John

To much of a good thing - is a good thing!

 

[Mike Lewis]

Graham,

Instead of splitting the tables by country, have you thought of splitting them "vertically"? After all, the number of countries is arbitrary; you are processing ten at the moment, but you might have to deal with many more in the future. Also, if you make a change to your field structure, you don't want to have to replicate that across many tables.

Is it possible to split the table by field rather than by records? For example, if you have three or four long-ish character fields and a couple of dozen numerics and logicals, it might make sense to put all the character fields in one table and the others in a second table, with an integer key linking them.

Or, if you have a long character field, it might be worth maing it into a memo (the 2 GB limit is per file, so the memo file will can be another 2 GB).

I appreciate this might not be possible, or might not solve the problem, but it might be worth considering.

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

My sites:
Visual FoxPro (www.ml-consult.demon.co.uk)
Crystal Reports (www.ml-crystal.com)

 

[grahamrhind]

Thanks for the comments.

The tables already cover the whole world, so some tables contain data for many countries, others for half of the available records for a single country (for those countries I have to make a further selection by postal code). Splitting the tables vertically would be difficult as some tables only contain two fields - country code and string. For other tables that is possible, and that's been a solution I've used. I hadn't thought of the memo option .....

 

[darrellblackhawk]

Here's an article discussing this very issue:

http://advisor.com/Articles.nsf/aid/MATIV02

Darrell

 

[grahamrhind]

Thanks for all the pointers. As I need to browse the files and use SEEK etc., I think I'm going to have to stick with the horizontal partitioning I'm using at the moment.

Graham