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]

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

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)?

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]

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

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

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)?

You need to ask these questions in a Foxpro newsgroup.

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

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]

"David W. Fenton" wrote

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.

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]

"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:

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

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.

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.
Yeah, including me :-)

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?

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.

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]

"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:

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

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.

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.
Yeah, including me :-)

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?

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.

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]

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.

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]

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.

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]

<ly******@yahoo .ca> wrote in message
news:11******** **************@ g44g2000cwa.goo glegroups.com.. .

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

I have nothing in that DB but tables and indexes.
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.
Well, I turned off record locking, and that helped a lot, at least at first.
2. Compact your db daily or hourly or whatever is suitable to its
working schedule.

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]

"ly******@yahoo .ca" <ly******@yahoo .ca> wrote in
news:11******** **************@ g44g2000cwa.goo glegroups.com:

[]

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

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

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