Re: Access DB grows too fast.

From: William \(Bill\) Vaughn (billvaRemoveThis_at_nwlink.com)
Date: 06/28/04 

Date: Mon, 28 Jun 2004 14:55:25 -0700

Nate,
    Unlike SQL Server (or most other business-class databases), Access/JET
does not recover space lost when rows are changed or deleted. Any alteration
of the database results in the original row being marked for deletion and
the new row (even if it's exactly the same) being added. Add a row, you get
one more row. Change the row and now you have an additional row. Delete a
row and the row remains. It's only after you compress the database are these
extra rows removed and then only in the process of locking down the file and
copying only the "active" (undeleted) rows. 

-- 
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
"Nate Clark" <ntclark@attglobal.net> wrote in message
news:40E04E43.50602@attglobal.net...
>
> Hi All and thanks for the help.
>
>
> I have an application that needs to store a large # of records in
> multiple tables.
>
> Application is written in VB.
>
> The problem that I am having is that after insert of these records (test
> case is 900 K records), I get a '.mdb that is nearly 1 GB in size. I
> then compact this database and get it down to about 155 MB in size. The
> only activity in getting that huge size is the insert(s) of the large #
> of rows.
>
> It seems that Access is wasting huge chunks of space during the import
> of these files and/or I am doing something wrong or not doing something
> I should be.
>
> I have tried 2 types of "bulk inserts" (BTW I cannot find an actual bulk
> insert for Access. Is there one ?)
>
>
> 1. Declaring an ADODB recordset using sql such as:
>
>     SELECT * FROM Table WHERE ID = -1
>
> Which is guaranteed to be empty when the rs opens (ID can't be < 0 ),
> and then adding to that recordset (.AddNew) and then updating it every
> 32 K records or so.
>
>
> 2. Writing my data out to a flat file and then calling C code that uses
> the ODBC API SQLPrepare technique to create a parameterized SQL
> statement which I then use to load data from the flat file.
>
>
> (Preliminary results - method 2 much faster)
>
> Both methods give me this huge database after this import.
>
> Further, and somewhat worrisome, after I get about 2+ million records in
> the database, I start getting "Invalid Parameter" errors all over the
> application (My fields are Long Integer which should provide me with
> values in a very large range - well above 2+ million anyway)
>
>
> I see from reading my own post that indeed I haven't tried single
> "INSERTS". This is something I will try now but I'll go ahead and post
> the question as I'm not really confident that is something that's going
> to fix this problem.
>
>

Relevant Pages

  • Access DB grows too fast.
    ... Writing my data out to a flat file and then calling C code that uses ... the ODBC API SQLPrepare technique to create a parameterized SQL ... statement which I then use to load data from the flat file. ... Both methods give me this huge database after this import. ...
    (microsoft.public.data.ado)
  • Re: Need Help deleting record from text file
    ... In many circumstances retrieval of data from a "home grown" data file will actually be faster than from a general purpose commercial database. ... save his data to disk using a "flat file" unindexed arrangement. ... In fact I never mentioned him saving his data to disk in any format at all. ... Admittedly a nice "ready made" database does make it relatively easy for people to store and retrieve data fairly rapidly without needing to write too much code or to worry about the methods used by the "black box" to perform such feats, and I certainly would not suggest that they should not be used, but I think that for many tasks a "home grown" data format would be as good as, and in some cases better than, a standard database. ...
    (microsoft.public.vb.general.discussion)
  • Re: Just say no to threads [Was: Software architecture]
    ... they knew there was going to be a database in the app. ... Now my colleague just spent a couple/three weeks designing an SQL ... Turns out they have a flat file of sample information, ...
    (comp.object)
  • RE: Cisco LEAP
    ... aren't meant to work this way; that's largely why database applications work ... For that, sure, a flat file is faster. ... Network with over 10,000 of the brightest minds in information security ... most highly-anticipated industry event of the year. ...
    (Pen-Test)
  • Re: Access DB grows too fast.
    ... "Nate Clark" wrote: ... > ADODB recordset causes the huge Access database. ... Writing my data out to a flat file and then calling C code that uses ... >> the ODBC API SQLPrepare technique to create a parameterized SQL ...
    (microsoft.public.data.ado)