CDX... growing to crash level in 4 hours

[RFanslow]

I have had this situation happen twice in the past 5 months
a foxpro table with 23 fields, 3 index fields, (order - char(10), Client - char(10), eDate - (datetime)

61 records, has a CDX of 2.8 gigs in 4 hours...

I dont mean to be short with the description, and i can supply data structure and index structure if you want to see it but it clearly makes no sence why a cdx would grow to 2.8 gig in 4 hours
Anyone have any idea on where to even start looking for this...?

Fanz

 

[Mike Lewis]

Was this a one-off occurrence, or is it something you are seeing regularly?

If one-off, I'm sure you know that a PACK will cause the indexes to be rebuilt, which should solve the problem. But if it keeps on happening, you will have to track down the cause.

(Note that a PACK will not rebuild the indexes if the table contains no deleted records. If necessary, insert a dummy record and then delete it before you pack.)

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Mike Lewis]

By the way, I believe there was a bug in VFP 7.0 that caused indexes to get severely bloated very quickly. Sorry I don't recall the details. If you happen to be using that version, it would be worth investigating further.

Later: I just found this article that explains the bug in more detail.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[RFanslow]

Thanks Mike
I am working in VFP 7, and it has happened several times, and the bloat is severe,
61 records with 2.8 gig cdx, seems to growing out of control and have had to remove the index in order to have the code run with crashing

Fanz

 

[Mike Lewis]

Fanz, do you have access to a later version of VFP? If so, try to reproduce the behaviour there. If the problem goes away, you will know that it is the bug I mentioned that is at the root of the problem.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[RFanslow]

Mike

I do, but this client is only using VFP7, as they are moving over to C#/SQL. I might have to bring the data home to check for the Bloating problem..

Fanz

 

[Olaf Doschke]

One thing in your description can't be true. 2.8GB is not a file size VFP would support. If you really mean that literally there is something else but VFP manipulating the file.

Bye, Olaf.

 

[EzLogic]

have you checked for any data corruption?

Ez Logic
Michigan

 

[RFanslow]

Olaf

i have had this on other files before...

eShuttle.DBF - 20.0 KB (20,480 bytes)
eShuttle3.CDX - 2.67 GB (2,869,104,640 bytes)
eShuttle.FPT - 1.00 MB (1,052,672 bytes)

eShuttle3.CDX - 3.26 GB (3,507,261,440 bytes) Previous error at 2am on Saturday



61 records contained within the file (packed from 12,000 records )
build all (3) tags this morning at 7:00 am

i hear what you say about 2gig limit... but clearly this is what the file looks like

 

[Olaf Doschke]

Well, VFP can't write more than 2GB, so there is something else writing to the file.

As it's called eShuttle3.cdx it's also not the CDX that VFP maintains automatically. So you have to have some INDEX commands explicitly creating a separate CDX file, that can exist and be named different, but like any IDX needs to be maintained and synced with the DBF manually. Like with IDX such separate CDX files only update if used in parallel to the DBF.

So look out for any code creating this separate CDX.

Bye, Olaf.

 

[Mike Lewis]

this client is only using VFP7, as they are moving over to C#/SQL. I might have to bring the data home to check for the Bloating problem..

I wasn't suggesting that the client should upgrade from 7.0, but rather that you should run some tests from a later version. That would confirm or otherwise that the problem is caused by the bug I mentioned. If it is, you would know better what direction to take.

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Olaf Doschke]

My guess about the separate CDX is either you create a TAG again and again, perhaps in a sortable grid creating index tags on the fly, and some bug of VFP I don't know yet actually bloats a file beyond the 2GB limit, or the customer addresses the dbf with a third party tool already, and that creates that separate cdx. You should be able to answer at least with a full project search with code references, whether your code creates the separate CDX or not. a second or third CDX for sure isn't done automatically by VFP to overcome the 2GB limitation.

Bye, Olaf.

 

[dbMark]

Does the code anywhere repeat the command to "INDEX ON..."? I had noticed some overly large index files with some tables where a programmer used the INDEX ON command on every call and the index bloated or concatenated such as what can happen with edited MEMO fields in FPT files. I replced that code with REINDEX which does not concatenate onto the end of the CDX file. (Or first delete the tags before using the INDEX ON command format.)

 

[RFanslow]

eShuttle3 was clearly just a renaming done by me in order to keep the file, but drop the CDX reference so i could use the dbf file once again

this is clearly a bloating CDX issue, just not sure how to get around it as it is a growing problem with production data

Fanz

 

[Olaf Doschke]

OK, still you should look out for INDEX ON statements.

Bye, Olaf.

 

[Mike Lewis]

Fanz, you have twice said that the table contains 61 records. Was this a test version of the table, or the production data?

The reason I ask is that if the production table is really that small, do you really need the index? It would take no time at all to search, filter or sort the table, even without an index - and it would involve very little changes to your code.

If - as I suspect - the table is in fact very much larger, that won't be an option of course. But you did twice mention the figure of 61, so I thought it might be worth following up.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Olaf Doschke]

Indeed it's a fact that points out there has to be permanently reindexing and adding indexes to the cdx to get it that large in just 4 hours. So if you don't do anything in your VFP code that again would point to some external influence. It should be easy to use process monitor to monitor access to this file. If it's the vfp9r.dll the EXE using the runtime DLL should also be found, if it's some other DLL or EXE you're fine anyway.

Bye, Olaf.

 

[DSummZZZ]

Hey Fanz,

Are there a lot of updates/replaces on that table?
As Mike alluded to, constantly updating index keys has been an issue at times. As in, when recycling records in the DBF the index keys in the CDX don't always get changed, but added. So if there are a ton of updates to the indexed fields in the DBF, the CDX could grow fast. But even so, that seems like an outrageous rate of growth.



-Dave Summers-

Even more Fox stuff at:

http://www.dave-summers.net/foxprolinks.htm

 

[DSummZZZ]

Also, there ain't by chance an index on a memo field is there?


-Dave Summers-

Even more Fox stuff at:

http://www.dave-summers.net/foxprolinks.htm

 

[NasibKalsi]

If possible(not multi user), use table in exclusive mode.

use bloattable in 0 alias bt exclusive