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

[Larry Linson]

Re: File bloating/compression problem in Jet


"Riley DeWiley" <riley.dewiley@ gmail.com> wrote in message
news:v4-dnQHkhpUWPV3fRV n-uw@seanet.com.. .[color=blue]
> 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[/color]
DBMS[color=blue]
> stuff (I handle all the UI)?[/color]

You need to ask these questions in a Foxpro newsgroup.

It might help if you'd say what your front-end is, when you do.

Jet databases, and many others, need to be compacted from time to time. I've
never found that need to come close to the time and effort of administering
a true server database. Fox is in the same "category" of databases as Jet,
so may also require compacting -- someone surely can tell you in the Fox
newsgroup.

Another option would be MSDE (in the MDAC) or the new (still in Beta) SQL
Server Express version.

Larry Linson
Microsoft Access MVP

[David W. Fenton]

Re: File bloating/compression problem in Jet

"Larry Linson" <bouncer@localh ost.not> wrote in
news:nt3we.3418 $rE6.690@trnddc 06:
[color=blue]
> "Riley DeWiley" <riley.dewiley@ gmail.com> wrote in message
> news:v4-dnQHkhpUWPV3fRV n-uw@seanet.com.. .[color=green]
>> 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[/color]
> DBMS[color=green]
>> stuff (I handle all the UI)?[/color]
>
> You need to ask these questions in a Foxpro newsgroup.[/color]

Well, I don't know about Foxpro specifically, but dBase files always
needed to be packed, but many xBase developers had a confused idea
of the relationship between record deletions and packing files. I've
seen apps where the developer was using marking records deleted as a
filtering method. The records really weren't being deleted, but
eventually disappeared from the database permanently when the user
packed the files.

But it's exactly the same process of recovering unused space as an
Access compact. The Access file has more overhead, though, since it
stores everything in a single file (including indexes).
[color=blue]
> It might help if you'd say what your front-end is, when you do.
>
> Jet databases, and many others, need to be compacted from time to
> time. . . .[/color]

Are there any db engines anywhere that:

1. recover unused space within its storage files automatically, AND

2. maintain the most efficient ordering for the data pages
automatically

I don't know of any, but my experience is pretty narrow.
[color=blue]
> . . . I've never found that need to come close to the time and
> effort of administering a true server database. Fox is in the same
> "category" of databases as Jet, so may also require compacting --
> someone surely can tell you in the Fox newsgroup.
>
> Another option would be MSDE (in the MDAC) or the new (still in
> Beta) SQL Server Express version.[/color]

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

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.

Also, front end bloat is not something to worry about -- you can
just replace the front end.

But one should definitely make sure one is doing nothing in the
front end to violate item 2) above.

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

[Larry Linson]

Re: File bloating/compression problem in Jet

"David W. Fenton" wrote
[color=blue]
> Are there any db engines anywhere that:
>
> 1. recover unused space within its storage
> files automatically, AND
>
> 2. maintain the most efficient ordering for the data pages
> automatically
>
> I don't know of any, but my experience is pretty narrow.[/color]

Yes, I believe some of the industrial-strength server databases "compact on
the fly".

In a previous incarnation as a mainframer, I wrote a couple of
special-purpose data base handlers which, by virtue of the way they were
designed, did not need compacting and since every access was to a specific
cylinder, head, and record, they did not need "reordering ".

[Riley DeWiley]

Re: File bloating/compression problem in Jet


"David W. Fenton" <dXXXfenton@bwa y.net.invalid> wrote in message
news:Xns9683782 F4B77Adfentonbw aynetinvali@24. 168.128.90...[color=blue]
> "Larry Linson" <bouncer@localh ost.not> wrote in
> news:nt3we.3418 $rE6.690@trnddc 06:
>[color=green]
>> "Riley DeWiley" <riley.dewiley@ gmail.com> wrote in message
>> news:v4-dnQHkhpUWPV3fRV n-uw@seanet.com.. .[color=darkred]
>>> 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.[/color][/color][/color]
[color=blue]
>
> Well, I don't know about Foxpro specifically, but dBase files always
> needed to be packed, but many xBase developers had a confused idea
> of the relationship between record deletions and packing files.[/color]

Yeah, including me :-)

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

[color=blue]
> 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[/color]

How can I detect this corruption, if this is the problem I am having?
[color=blue]
>
> 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.[/color]


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.

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.

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. 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.

Thanks again.

RDeW

[Riley DeWiley]

Re: File bloating/compression problem in Jet


"David W. Fenton" <dXXXfenton@bwa y.net.invalid> wrote in message
news:Xns9683782 F4B77Adfentonbw aynetinvali@24. 168.128.90...[color=blue]
> "Larry Linson" <bouncer@localh ost.not> wrote in
> news:nt3we.3418 $rE6.690@trnddc 06:
>[color=green]
>> "Riley DeWiley" <riley.dewiley@ gmail.com> wrote in message
>> news:v4-dnQHkhpUWPV3fRV n-uw@seanet.com.. .[color=darkred]
>>> 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.[/color][/color][/color]
[color=blue]
>
> Well, I don't know about Foxpro specifically, but dBase files always
> needed to be packed, but many xBase developers had a confused idea
> of the relationship between record deletions and packing files.[/color]

Yeah, including me :-)

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

[color=blue]
> 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[/color]

How can I detect this corruption, if this is the problem I am having?
[color=blue]
>
> 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.[/color]


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.

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.

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. 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.

Thanks again.

RDeW

[lylefair@yahoo.ca]

Re: File bloating/compression problem in Jet

[color=blue]
> Please expand upon design issues that can cause/avoid this problem, and how
> I detect corruption. I cannot believe that what I am seeing is "normal" for
> such a successful product as Jet, as it renders the database nearly
> unusable.[/color]

Using OLEDB directly with JET is not so common. More often we have
another buffer, say ADO, which interacts with OLEDB. Unless we can see
all your interaction with the db, it may be difficult to pin down the
cause for bloat.

JET never releases anything without the struggle named Compacting. This
includes records, queries and indexes you create; this can cause the db
to grow. But Jet also creates many temporary objects to do its work.
Unless db operations are planned carefully these can grow to result in
huge chunks of redundant file space, viz BLOAT. My !!!!guess!!!! is
that this is what is happening with your file.

It's common here to have dbs of hundreds of megs described, yet, one
can see from the description that they should be 900 kilobytes.

Jet has been designed to be the home of data and "programmin g"
technologies like Access and VBA. Inclusion of the latter, IMO,
compromises its abilty to be a lean and mean data holder.

I can think of three things I would try to do to rememdy your
situation.

1. Review JET and your operations. Read MSDN about Jet intensively. Try
to identify what it is that you are doing that may result in the
creation of temporary objects. If you can, do these some other way.

2. Compact your db daily or hourly or whatever is suitable to its
working schedule.

3. Use a DBF file (Like Foxpro). These files are (or used to be ...
haven't used them for years), simple fixed length files. The Foxpro
executable support file used to 2 megs in size. But do you need this?
If you are programming in C then you can manipulate the file yourself,
no need for Foxpro. You will need an indexing component (unless you
write your own which is not beyind a C programmer); there used to be
many of these about on the net which were more powerful than FoxPro's.
One of the great advantages of X-Base is, of course, that one can turn
indexes on and off, and one can make them conditional. When they are
off, things like insertions are many times faster. Indexes can be
updated and applied when needed; Ditto with relations. Of course. This
creates lots of danger in the Referential Integrity area.

4. Depending on circumstances and your budget you might be able to use
MS-SQL. I rent web enabled MS-SQL dbs. They are cheap and I can acess
them anywhere there is an internet connection.

I would never use JET for a non-Access application.
Ewwwwwwwwwwwwww wwwww!

[lylefair@yahoo.ca]

Re: File bloating/compression problem in Jet

[color=blue]
> Please expand upon design issues that can cause/avoid this problem, and how
> I detect corruption. I cannot believe that what I am seeing is "normal" for
> such a successful product as Jet, as it renders the database nearly
> unusable.[/color]

Using OLEDB directly with JET is not so common. More often we have
another buffer, say ADO, which interacts with OLEDB. Unless we can see
all your interaction with the db, it may be difficult to pin down the
cause for bloat.

JET never releases anything without the struggle named Compacting. This
includes records, queries and indexes you create; this can cause the db
to grow. But Jet also creates many temporary objects to do its work.
Unless db operations are planned carefully these can grow to result in
huge chunks of redundant file space, viz BLOAT. My !!!!guess!!!! is
that this is what is happening with your file.

It's common here to have dbs of hundreds of megs described, yet, one
can see from the description that they should be 900 kilobytes.

Jet has been designed to be the home of data and "programmin g"
technologies like Access and VBA. Inclusion of the latter, IMO,
compromises its abilty to be a lean and mean data holder.

I can think of three things I would try to do to rememdy your
situation.

1. Review JET and your operations. Read MSDN about Jet intensively. Try
to identify what it is that you are doing that may result in the
creation of temporary objects. If you can, do these some other way.

2. Compact your db daily or hourly or whatever is suitable to its
working schedule.

3. Use a DBF file (Like Foxpro). These files are (or used to be ...
haven't used them for years), simple fixed length files. The Foxpro
executable support file used to 2 megs in size. But do you need this?
If you are programming in C then you can manipulate the file yourself,
no need for Foxpro. You will need an indexing component (unless you
write your own which is not beyind a C programmer); there used to be
many of these about on the net which were more powerful than FoxPro's.
One of the great advantages of X-Base is, of course, that one can turn
indexes on and off, and one can make them conditional. When they are
off, things like insertions are many times faster. Indexes can be
updated and applied when needed; Ditto with relations. Of course. This
creates lots of danger in the Referential Integrity area.

4. Depending on circumstances and your budget you might be able to use
MS-SQL. I rent web enabled MS-SQL dbs. They are cheap and I can acess
them anywhere there is an internet connection.

I would never use JET for a non-Access application.
Ewwwwwwwwwwwwww wwwww!

[Riley DeWiley]

Re: File bloating/compression problem in Jet


<lylefair@yahoo .ca> wrote in message
news:1121599041 .341396.178100@ g44g2000cwa.goo glegroups.com.. .[color=blue]
>[color=green]
>> Please expand upon design issues that can cause/avoid this problem, and
>> how
>> I detect corruption. I cannot believe that what I am seeing is "normal"
>> for
>> such a successful product as Jet, as it renders the database nearly
>> unusable.[/color]
>
> Using OLEDB directly with JET is not so common. More often we have
> another buffer, say ADO, which interacts with OLEDB. Unless we can see
> all your interaction with the db, it may be difficult to pin down the
> cause for bloat.
>
> Jet has been designed to be the home of data and "programmin g"
> technologies like Access and VBA. Inclusion of the latter, IMO,
> compromises its abilty to be a lean and mean data holder.
>[/color]
I have nothing in that DB but tables and indexes.
[color=blue]
> I can think of three things I would try to do to rememdy your
> situation.
>
> 1. Review JET and your operations. Read MSDN about Jet intensively. Try
> to identify what it is that you are doing that may result in the
> creation of temporary objects. If you can, do these some other way.
>[/color]
Well, I turned off record locking, and that helped a lot, at least at first.
[color=blue]
> 2. Compact your db daily or hourly or whatever is suitable to its
> working schedule.[/color]

Eh, I am now forced to do it every five minutes ... :-(

Here is the situation now:
I turned off record locking and now get much nicer performance, until the
first compaction. After that, the database bloats horribly, forcing repeated
compactions.

My interactions?

To open(error traps omitted):

dbinit[0].AddProperty(DB PROP_AUTH_CACHE _AUTHINFO, true);

dbinit[0].AddProperty(DB PROP_AUTH_ENCRY PT_PASSWORD, false);

dbinit[0].AddProperty(DB PROP_AUTH_MASK_ PASSWORD, false);

dbinit[0].AddProperty(DB PROP_AUTH_USERI D, OLESTR("Admin") );

dbinit[0].AddProperty(DB PROP_INIT_DATAS OURCE, wszDBFile);

dbinit[0].AddProperty(DB PROP_INIT_MODE, (long)DB_MODE_S HARE_DENY_NONE) ;

dbinit[0].AddProperty(DB PROP_INIT_PROMP T, (short)DBPROMPT _NOPROMPT);

dbinit[0].AddProperty(DB PROP_INIT_PROVI DERSTRING, OLESTR(""));

dbinit[0].AddProperty(DB PROP_INIT_LCID, (long)1033);

dbinit[1].AddProperty(DB PROP_JETOLEDB_D ATABASELOCKMODE ,
(long)DBPROPVAL _DL_OLDMODE);


dbinit[1].AddProperty(DB PROP_JETOLEDB_D ATABASEPASSWORD , sPwd);

hr = db.Open(this->sDBProviderNam e, (DBPROPSET*)&db init,
(ULONG)ELEMCOUN T(dbinit));

To compact, I close the session and the DB, then execute this, then reopen:

//Specify the source DSO

CDataSource ds;

HRESULT hr = 0;

//these are the same params as used to open above


hr = ds.Open(this->sDBProviderNam e, (DBPROPSET*)&db init,
(ULONG)ELEMCOUN T(dbinit));



CDBPropSet propset_compact[2];

//for basic stuff

propset_compact[0].SetGUID(DBPROP SET_DBINIT);

propset_compact[0].AddProperty(DB PROP_INIT_DATAS OURCE, dest);

long x = GetJetEngineTyp e(_Module.sDBPr oviderName, src);

propset_compact[1].SetGUID(DBPROP SET_JETOLEDB_DB INIT);

propset_compact[1].AddProperty(DB PROP_JETOLEDB_E NGINE, x);


propset_compact[1].AddProperty(DB PROP_JETOLEDB_D ATABASELOCKMODE ,
(long)DBPROPVAL _DL_OLDMODE);

propset_compact[1].AddProperty(DB PROP_JETOLEDB_D ATABASEPASSWORD , sPwd);

CComPtr<IDBCrea teSession> spSession =NULL;

// Have we connected to the database?

ATLASSERT(ds.m_ spInit != NULL);

hr = ds.m_spInit->QueryInterface (IID_IDBCreateS ession, (void**)&spSess ion);



//IJetCompact only supported in Jet 4.0 and above

CComPtr<IJetCom pact> spJetCompact = NULL;

hr = spSession->QueryInterface ( __uuidof(IJetCo mpact),
(void**)&spJetC ompact);



//Delete the destination file if it exists

DeleteFileW(des t);


//Ok compact

hr = spJetCompact->Compact(ELEMCO UNT(propset_com pact), propset_compact );

[David W. Fenton]

Re: File bloating/compression problem in Jet

"lylefair@yahoo .ca" <lylefair@yahoo .ca> wrote in
news:1121599041 .341396.178100@ g44g2000cwa.goo glegroups.com:

[]
[color=blue]
> Jet has been designed to be the home of data and "programmin g"
> technologies like Access and VBA. Inclusion of the latter, IMO,
> compromises its abilty to be a lean and mean data holder.[/color]

This is complete malarky.

The Jet database engine has *not* be "compromise d" to accomodate
Access. Access objects and all their non-Jet properties are stored
in Jet data tables.

Now, it could be that using a Jet MDB created with Access is less
efficient that creating the Jet MDB without Access. I wouldn't know.
I've never seen enough problems with bloating to think that
Access-created MDBs were in any way problematic.

[]
[color=blue]
> I would never use JET for a non-Access application.
> Ewwwwwwwwwwwwww wwwww![/color]

Microsoft seems not to agree with you on that one (cf. Active
Directory).

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

[David W. Fenton]

Re: File bloating/compression problem in Jet

"Riley DeWiley" <riley.dewiley@ gmail.com> wrote in
news:MPmdndeC6p CkMUTfRVn-3A@seanet.com:
[color=blue]
> "David W. Fenton" <dXXXfenton@bwa y.net.invalid> wrote in message
> news:Xns9683782 F4B77Adfentonbw aynetinvali@24. 168.128.90...[color=green]
>> "Larry Linson" <bouncer@localh ost.not> wrote in
>> news:nt3we.3418 $rE6.690@trnddc 06:[/color][/color]

[]
[color=blue][color=green][color=darkred]
>>> It might help if you'd say what your front-end is, when you do.[/color][/color]
> I am writing an embedded app with OleDB, C++ on Windows. The front
> end is therefore all C++ code of my own creation.
>[color=green]
>> 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[/color]
>
> How can I detect this corruption, if this is the problem I am
> having?[/color]

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.
[color=blue][color=green]
>> 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.[/color]
>
> 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.[/color]

That sounds like quite a bit of data to me, enough to make me
consider going to a non-Jet back end.
[color=blue]
> 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.[/color]

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.
[color=blue]
> 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. . . .[/color]

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.
[color=blue]
> . . . 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.[/color]

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]

Re: File bloating/compression problem in Jet


"David W. Fenton" <dXXXfenton@bwa y.net.invalid> wrote in message
news:Xns969CC1F 61FE12dfentonbw aynetinvali@216 .196.97.142...[color=blue]
> "Riley DeWiley" <riley.dewiley@ gmail.com> wrote in
> news:MPmdndeC6p CkMUTfRVn-3A@seanet.com:
>[color=green]
>> "David W. Fenton" <dXXXfenton@bwa y.net.invalid> wrote in message
>> news:Xns9683782 F4B77Adfentonbw aynetinvali@24. 168.128.90...[color=darkred]
>>> "Larry Linson" <bouncer@localh ost.not> wrote in
>>> news:nt3we.3418 $rE6.690@trnddc 06:[/color][/color]
>
> []
>[color=green][color=darkred]
>>>> It might help if you'd say what your front-end is, when you do.[/color]
>> I am writing an embedded app with OleDB, C++ on Windows. The front
>> end is therefore all C++ code of my own creation.
>>[color=darkred]
>>> 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[/color]
>>
>> How can I detect this corruption, if this is the problem I am
>> having?[/color]
>
> 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.
>[color=green][color=darkred]
>>> 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.[/color]
>>
>> 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.[/color]
>
> That sounds like quite a bit of data to me, enough to make me
> consider going to a non-Jet back end.[/color]

I'm considering it, thanks.

[color=blue]
>
> 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.[/color]

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

[David W. Fenton]

Re: File bloating/compression problem in Jet

"Riley DeWiley" <riley.dewiley@ gmail.com> wrote in
news:PuydnQUDo6 3KZX_fRVn-pQ@seanet.com:
[color=blue]
> Turning off record locking, a Jet 4.0 feature, has really helped.[/color]

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