We've got a table in our application that can (has) reach the foxpro limit of 2GB and/or reach 1 billion records for some of our clients. In the past we've manually archived the data for them.

Right now I'm working on a mechanism that will allow our clients to archive their own data. In a nutshell, the mechanism will move records from "mybigtable" to "mybigarchivetable1". If "mybigarchivetable1" is full it will create "mybigarchivetable2" and start moving the records in that table.

I've got it all figured out except for one thing. I have to check if "mybigarchivetableX" has reached the 2GB or 1 billion record limit before adding a record to it. I figured I can use RECCOUNT() to check the record count and use HEADER() + (RECSIZE() * RECCOUNT() + 1) to check the file size. But to me it would seem that it would really slow down the process if it performed those checks before inserting every record. There's got to be a way that I could check every 1000th record or something like that. So something like:

LOCAL lnRecCount, lnFileSize
lnRecCount = RECCOUNT() + 1000
lnFileSize = HEADER() + (RECSIZE * lnRecCount) + 1)
IF !(lnRecCount < 1 billion and lnFileSize < 2 GB)
  * create new archive table
ENDIF
* archive records

The thing I'm not sure of is should I check every 1000th record or something higher or lower? If anyone has done something like this before I would like to hear your advice.

Thanks,
Chris