Unpredictable db size increase during INSERTs - Memos?

[deathyam]

Hi,

I have an Excel 97 app in which I'm using SQL INSERTs to insert data
into an Access 97 database table.

The table structure is as follows:
VersionID (PK) (Long)
MetricID (PK) (Long)
MetricNumber (Double)
MetricText (Memo)

I chose this structure because each time I write to the database I'm
saving the contents of over 400 cells (metrics). Some are numeric, some
are text > 255 characters.

My code executes an INSERT command for each of the metrics (ie 400
INSERTs). For testing purposes, I'm inserting 200 numeric ones and 200
text ones (text length only 7 or 8 chars).

Here's what happens:
I compact the database and run the 400 INSERTs.
On this first run, the db jumps from 8000K to 14674K. I have no idea
why.
On the second run (same data each time) the db jumps 14674 to 14930 =
256k diff
3: 14930-15122=192k diff
4: 15122-15362=240k diff
5: 15362-15602=240k diff
6: 15602-15858=256k diff

Note: I am using BeginTrans and CommitTrans to ensure all 400 go
through or none at all.

Questions I have:
1. Why the big jump in db size from compact to first write?
2. Why doesn't the database increase in size in a consistent manner for
each run of the same data?
3. Why is it >200k per insert? I calculate 4 bytes + 4 bytes + 8 bytes
+ 8bytes(due to 8 test chars) = 24 bytes per row, x 400 rows =
9600bytes = 9.375k. Is it the Memo? I thought it sized to the amount of
text stored...
4. I will have multiple users reading and writing to this table in 400
row chunks from their Excel Workbooks. What do you suggest I do to
handle collisions/sharing errors? Currently my plan is to handle any
read/write errors and retry behind the scenes a few times until the
operation goes through.

Any help is greatly appreciated.

Michael

[Tony Toews]

de******@yahoo.com wrote:

Questions I have:
1. Why the big jump in db size from compact to first write?
I've noticed the exact same behaviour at a client. I compacted the MDB down to 200
Mb. After the first day it jumped by 20 Mb. Each day thereafter it increased by a
few hundred Kb or a half Mb or so.

I strongly suspect that Jet is making empty or near empty pages available in indexes
and tables to allow for efficient inserting of records and updating of indexes.
2. Why doesn't the database increase in size in a consistent manner for
each run of the same data?
Your results for the increases from runs 2-6 are "close enough" to be consistent in
my opinion. I wouldn't worry about those. Part of the reason is that space is
allocated in 2 kb pages. But I've also seen the MDB file allocated only in
increments of 16 kb (or some other similar size) such as you report.
3. Why is it >200k per insert? I calculate 4 bytes + 4 bytes + 8 bytes
+ 8bytes(due to 8 test chars) = 24 bytes per row, x 400 rows =
9600bytes = 9.375k. Is it the Memo? I thought it sized to the amount of
text stored...
Correct, memo fields are stored in thier own 2 kb pages and are not stored with the
data pages.
4. I will have multiple users reading and writing to this table in 400
row chunks from their Excel Workbooks. What do you suggest I do to
handle collisions/sharing errors? Currently my plan is to handle any
read/write errors and retry behind the scenes a few times until the
operation goes through.

Ensure all your users are at the same Jet version is the biggest thing. What I've
done is use the various API calls available and am checking the version number and
date/time of a crucial dll, msjetxx.dll, to ensure it matches what I have on my
system. See the Verify Appropriate Jet Service Pack is installed page at my website
for more details including sample code: www.granite.ab.ca\access\verifyjetsp.htm

I have had problems in the past when multiple users are inserting lots of records
into the same tables with page locks and such. But to get around the problem we
changed procedures slightly.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm

[deathyam]

Tony Toews wrote:

de******@yahoo.com wrote:

Questions I have:
1. Why the big jump in db size from compact to first write?
I've noticed the exact same behaviour at a client. I compacted the

MDB down to 200 Mb. After the first day it jumped by 20 Mb. Each day thereafter it increased by a few hundred Kb or a half Mb or so.

I strongly suspect that Jet is making empty or near empty pages available in indexes and tables to allow for efficient inserting of records and updating of indexes.

So really the compacting is bringing the database to a smaller size
than it needs to be operational, and then actually using the database
brings it back to operational size. Fair enough.

2. Why doesn't the database increase in size in a consistent manner foreach run of the same data?

Your results for the increases from runs 2-6 are "close enough" to be

consistent in my opinion. I wouldn't worry about those. Part of the reason is that space is allocated in 2 kb pages. But I've also seen the MDB file allocated only in increments of 16 kb (or some other similar size) such as you report.

Okay.

3. Why is it >200k per insert? I calculate 4 bytes + 4 bytes + 8 bytes+ 8bytes(due to 8 test chars) = 24 bytes per row, x 400 rows =
9600bytes = 9.375k. Is it the Memo? I thought it sized to the amount oftext stored...

Correct, memo fields are stored in thier own 2 kb pages and are not

stored with the data pages.
Still: 2048bytes (memo) + 16bytes (other fields) = ~2k per row x 400
rows = 800k, which is about 4x bigger than what I'm experiencing. It
must not allocate the entire 2k for each row. Perhaps it doesn't
allocate any space if a Memo is Null as is the case for 200 of my 400
test rows...

4. I will have multiple users reading and writing to this table in 400row chunks from their Excel Workbooks. What do you suggest I do to
handle collisions/sharing errors? Currently my plan is to handle any
read/write errors and retry behind the scenes a few times until the
operation goes through.

Ensure all your users are at the same Jet version is the biggest

thing. What I've done is use the various API calls available and am checking the version number and date/time of a crucial dll, msjetxx.dll, to ensure it matches what I have on my system. See the Verify Appropriate Jet Service Pack is installed page at my website for more details including sample code: www.granite.ab.ca\access\verifyjetsp.htm
I have had problems in the past when multiple users are inserting lots of records into the same tables with page locks and such. But to get around the problem we changed procedures slightly.
Thanks for the tips Tony.

Cheers.
Michael
Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm

[Tony Toews]

de******@yahoo.com wrote:

Perhaps it doesn't
allocate any space if a Memo is Null as is the case for 200 of my 400
test rows...

Yes, I strongly suspect that is the case. As in about 99.999% sure.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm