Conv. AC97/2K3 increase size by 15-20 times.

  • Thread starter Poul Max Christensen
  • Start date
P

Poul Max Christensen

I have made an application in AC97, that imports between 150000 and 200000
rows from a text file, with aprox. 300000 rows - this done with VBA : Open,
Input, Line Input
Under normal circumstances the database is 50Mb - we have now done a upgrade
to AC2003 and then the database is now 800-900 Mb when an import is done.
Searching by Google has given two points:
* I have newest service pack for JET
* All text fields in tables has Unicode compression = Yes

Still I have a very large database and the time doing the import is also
increased considerably.
Does any one have an idea where to look for a solution.

T.i.a.
Poul Max
 
A

Albert D.Kallal

I would check to ensure that record locking is turned off, specifically row
locking.

(ms-access pads the frame to ensure that one recode is in the frame, and
this case cause bloat).

tools->advanced tab->check setting for "open databases using record level
locking" - you want this option off
Under normal circumstances the database is 50Mb
Click to expand...

What was the db size AFTER you did a import in a97?

Without question, temp space is NOT recovered until you do a compact and
repair.

I assume that after you do a compact and repair, the size returns to normal
(and, you can set the database to do this on close if you want).

If you got any recordset code, then make sure you go:

restReocrdSet.Close
set rstReocrdSet = nothing

There is a good faq on bloating you can read here:

http://www.granite.ab.ca/access/bloatfe.htm
 
P

Poul Max Christensen

tools->advanced tab->check setting for "open databases using record level
locking" - you want this option off
Click to expand...
Hi Albert

That did the trick - it wasn't activated in the 97 base but in in the
convereted 2003 - after a couple of test run the databse don't grow.

Tanks a lot.

Best regards
Poul Max
 
D

david epsom dot com dot au

This was supposed to fixed in MDAC 2.1 SP2!

"This behavior no longer occurs in MDAC version MDAC 2.1.2.4202.3 (GA),
which you may also see referred to as MDAC 2.1 SP2, and later"

http://support.microsoft.com/kb/q239527/
ACC2000: Database Bloats When Importing Large Text File

(david)
 
A

Albert D.Kallal

david epsom dot com dot au said:
This was supposed to fixed in MDAC 2.1 SP2!

"This behavior no longer occurs in MDAC version MDAC 2.1.2.4202.3 (GA),
which you may also see referred to as MDAC 2.1 SP2, and later"

http://support.microsoft.com/kb/q239527/
ACC2000: Database Bloats When Importing Large Text File
Click to expand...

Hum, interesting, but I was not aware that extra bloat due to row locking
can be fixed.
It is possible that the KB article means that import is fixed, but bloat due
to record locking can't really be fixed.

Since access has to pad the record, then extra bloat occurs.
 
D

david epsom dot com dot au

It's an append statement, so even with record level
locking, the records could be consolidated into a page
before they are added to the database. Exactly the
same problem if appending from a native table as
appending from a text IISAM.

(david)
 
Top