File bloating/compression problem in Jet

[Riley DeWiley]

I have a project that is using a Jet backend and having trouble with Jet's
tendency to bloat it's MDB file. I can compact it but it is a hassle. I am
considering switching to a Fox backend but have not used one. I want to
know:

1) Does the Foxpro backend also bloat like this?
2) Is it hard to distribute fox binaries if all you want is bare bones DBMS
stuff (I handle all the UI)?

RDeW

[David W. Fenton]

"Riley DeWiley" <ri***********@ gmail.com> wrote in
news:MP******** ************@se anet.com:

"David W. Fenton" <dX********@bwa y.net.invalid> wrote in message
news:Xn******** *************** ***********@24. 168.128.90...

"Larry Linson" <bo*****@localh ost.not> wrote in
news:nt3we.3418 $rE6.690@trnddc 06:
[]

It might help if you'd say what your front-end is, when you do. I am writing an embedded app with OleDB, C++ on Windows. The front
end is therefore all C++ code of my own creation.

Anyone having bloat problems probably has a design problem. I've
never seen unacceptable bloat in any MDB that:

1. was not corrupted in some way, OR
How can I detect this corruption, if this is the problem I am
having?

Well, the kind of corruption I was thinking of was in VBA code, but
if your MDB is being used by a C++ app, then there isn't any code in
it to corrupt.

You might try recreating the MDB in code and appending the data from
the original MDB. I don't know how you do that with C++ -- I only
know how to manipulate Jet via DAO.
2. was not being used in a way that unwisely added/deleted
bunches of records in the data file instead of in a temp file.

I am hitting the problem after doing a large number of inserts,
and only a few deletions, in three tables:
- A table with an Autonumber field and a text field, with up to
tens of thousands of records, indexed on the autonumber;
- A table with an Autonumber field and a text field, with up to
about two million records, indexed on the autonumber and the text;
- A junction table for the above, comprising exactly three 32 bit
integers (one is a count), with up to about four million records,
indexed on the two ID fields as a combined unique key.

That sounds like quite a bit of data to me, enough to make me
consider going to a non-Jet back end.
Referential integrity is enforced through the junction table;
every entry must have a corresponding entry in each of the other
tables.

Insertions are made as follows:
LOOP(500 times)
START TRANSACTION

if(entry exists in left table)
fetch left_id;
else
insert into left table and fetch left_id;

if(entry exists in right table)
fetch right_id;
else
insert into right table and fetch right_id;

if(exists record (left_id, right_id) in junction table)
update count field for that record with new count;
else
insert into junction table (left_id, right_id, count)

COMMIT TRANSACTION
END LOOP

When I am nowhere near "full", my DB reaches a state where it will
compact down twenty-fold, that is, from about 800 MB to about
40MB. If I baby it, compressing as I go, when it is "full" (tables
as above), it is about 700 MB and seems to work fine. A DB that
has grown to 5000 x 500,000 x 50,000 records can easily take up a
gigabyte. There are other tables in the DB but they do not have
many records, and are not as "busy" as these three by far.
Well, how often are you going to be inserting this amount of data?
If it's often enough that over the life of the file (say in 2 years)
you'll be bumping up against the 2GB limit on compacted size, then
I'd say Jet isn't a very good choice for your back end.

Another thing to try is to use a Jet 3.5 MDB file, instead of Jet 4
-- bloat in Jet 4 MDBs is an order of magnitude worse than in Jet
3.5, in my experience.
If I don't compact it all the time as I fill it, it goes over 2GB
and becomes irreparably corrupt.

Please expand upon design issues that can cause/avoid this
problem, and how I detect corruption. . . .
There isn't any way to really detect corruption of the kind of that
would be relevant to you. The only way to get around it is to create
a fresh Jet MDB (not with Access) and append the data from the
problem database and see if it exhibits the same problems.
. . . I cannot believe that what I
am seeing is "normal" for such a successful product as Jet, as it
renders the database nearly unusable. I hope to discover I am
doing something wrong.

Try the Jet 3.5 format instead of Jet 4.

And do all your MDB creation in code, not with Access itself.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

[Riley DeWiley]

"David W. Fenton" <dX********@bwa y.net.invalid> wrote in message
news:Xn******** *************** ***********@216 .196.97.142...

"Riley DeWiley" <ri***********@ gmail.com> wrote in
news:MP******** ************@se anet.com:

"David W. Fenton" <dX********@bwa y.net.invalid> wrote in message
news:Xn******** *************** ***********@24. 168.128.90...

"Larry Linson" <bo*****@localh ost.not> wrote in
news:nt3we.3418 $rE6.690@trnddc 06:
[]
It might help if you'd say what your front-end is, when you do. I am writing an embedded app with OleDB, C++ on Windows. The front
end is therefore all C++ code of my own creation.

Anyone having bloat problems probably has a design problem. I've
never seen unacceptable bloat in any MDB that:

1. was not corrupted in some way, OR

How can I detect this corruption, if this is the problem I am
having?

Well, the kind of corruption I was thinking of was in VBA code, but
if your MDB is being used by a C++ app, then there isn't any code in
it to corrupt.

You might try recreating the MDB in code and appending the data from
the original MDB. I don't know how you do that with C++ -- I only
know how to manipulate Jet via DAO.

2. was not being used in a way that unwisely added/deleted
bunches of records in the data file instead of in a temp file.

I am hitting the problem after doing a large number of inserts,
and only a few deletions, in three tables:
- A table with an Autonumber field and a text field, with up to
tens of thousands of records, indexed on the autonumber;
- A table with an Autonumber field and a text field, with up to
about two million records, indexed on the autonumber and the text;
- A junction table for the above, comprising exactly three 32 bit
integers (one is a count), with up to about four million records,
indexed on the two ID fields as a combined unique key.

That sounds like quite a bit of data to me, enough to make me
consider going to a non-Jet back end.

I'm considering it, thanks.


Another thing to try is to use a Jet 3.5 MDB file, instead of Jet 4
-- bloat in Jet 4 MDBs is an order of magnitude worse than in Jet
3.5, in my experience.

Turning off record locking, a Jet 4.0 feature, has really helped.

[David W. Fenton]

"Riley DeWiley" <ri***********@ gmail.com> wrote in
news:Pu******** ************@se anet.com:

Turning off record locking, a Jet 4.0 feature, has really helped.

I've *always* turned off record-level locking in Access 2K, because
it just isn't needed except when you're using pessimistic locking,
which is something that very few applications actually need.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc