Re: Access DB grows too fast.

From: Nate Clark (ntclark_at_attglobal.net)
Date: 06/28/04

  • Next message: Mr. B: "Re: ADO.net: 3 Data Set Questions (Issues)" 
    Date: Mon, 28 Jun 2004 19:58:31 GMT

    Indeed, the problem seems to be in the strategy of calling C and using
    SQLPrepare and SQLExecute to insert the rows. Doing it this way is
    causing a huge database that can then be compacted down to the same size
    as doing it the other way.

    This is unfortunate because it is substantially faster doing it this way.

    So the question becomes, does anybody know of something about using this
    strategy that might cause such a thing ?

    Nate Clark wrote:
    > Before anyone invests a lot of time on this ....
    >
    > I may have been wrong in my original post in that the updating of an
    > ADODB recordset (strategy 1) causes the huge Access database. I have
    > reverted that code and while it's running right now, I'm not seeing this
    > big DB getting created.
    >
    >
    > On the other hand, I believe that I set the VB application's connection
    > up to operate synchronously and that that might be the reason why using
    > the cursor in VB is not growing the DB. If this is true, it would seem
    > like the "huge" database might perhaps be because there are many records
    > of "uncommitted" data in the '.db.
    >
    > I will confirm all of this once the current test finishes and after I
    > can setup the C code's connection to be synchronous too and try that out.
    >
    >
    >
    >
    >
    > Nate Clark wrote:
    >
    >>
    >> 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.
    >>
    >>
    >

  • Next message: Mr. B: "Re: ADO.net: 3 Data Set Questions (Issues)"

    Relevant Pages

    • RE: function doesnt return value - SOMETIMES!
      ... It seems the calling stack has some issues. ... If not, I suggest that you create a new database, and import the ... |> thing I can see is that you are declaring 3 of your numerical values as ... |> when it does work correctly that would be because this data type is ...
      (microsoft.public.access.modulesdaovba)
    • Problem calling Fill() method
      ... daAddress dataset, the update works fine and even enters a new row inside ... conflict occurred in database 'OFS', table 'Address', column 'address_id'. ... discovered this error message: ... Now, the zip column is indeed populated upon calling the Updatemethod, ...
      (microsoft.public.dotnet.framework.aspnet.datagridcontrol)
    • Problem calling Fill() method
      ... daAddress dataset, the update works fine and even enters a new row inside ... conflict occurred in database 'OFS', table 'Address', column 'address_id'. ... discovered this error message: ... Now, the zip column is indeed populated upon calling the Updatemethod, ...
      (microsoft.public.sqlserver.programming)
    • Problem calling Fill() method
      ... daAddress dataset, the update works fine and even enters a new row inside ... conflict occurred in database 'OFS', table 'Address', column 'address_id'. ... discovered this error message: ... Now, the zip column is indeed populated upon calling the Updatemethod, ...
      (microsoft.public.dotnet.framework.aspnet)
    • Problem calling Fill() method
      ... daAddress dataset, the update works fine and even enters a new row inside ... conflict occurred in database 'OFS', table 'Address', column 'address_id'. ... discovered this error message: ... Now, the zip column is indeed populated upon calling the Updatemethod, ...
      (microsoft.public.dotnet.framework.adonet)