Results 1 to 3 of 3
  1. #1
    Join Date
    Feb 2004
    Location
    Chicago, IL
    Posts
    1,312

    Unanswered: Minimize Database Bloating

    I have a problem with my database size. The database is 1GB (1 table, 2.1 Million records). I am trying to update a field for every record using an ADO recordset. After updating ~200,000 records the database reaches its maximum size. I have tried a couple of different cursor types but see no difference. Does anyone have any tips or tricks to minimize the bloat?

    I think it might be better using an Update query but the code will get more complicated (I can't use one UPDATE query to update all records.)

  2. #2
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Quote Originally Posted by DCKunkle
    I have a problem with my database size. The database is 1GB (1 table, 2.1 Million records). I am trying to update a field for every record using an ADO recordset. After updating ~200,000 records the database reaches its maximum size. I have tried a couple of different cursor types but see no difference. Does anyone have any tips or tricks to minimize the bloat?

    I think it might be better using an Update query but the code will get more complicated (I can't use one UPDATE query to update all records.)
    Are you setting this field to different values depending on the row being acted upon? Or, are you setting the column to the same value (for all rows)? If the latter, then using a recordset is the wrong way to go ... Just Execute an update query. If the former, you COULd do something like:

    Make a table keeping track of the last row processed.
    Run your update process for X # of records.
    Update your last row processed.
    compact the db
    open DB and continue until all rows done.
    Back to Access ... ADO is not the way to go for speed ...

  3. #3
    Join Date
    Feb 2004
    Location
    Chicago, IL
    Posts
    1,312
    I am breaking the 2.1M into groups of 500. The first 500 need to be assigned the equivalent of 1. 501 to 1000 the value 2. 1001 to 1500 the value of 3.

    I was able to do the processing using a Select query (Top 500 records) and then an Update query that updated the select query. It only added 20 Kb to the size of the database. Which is what I expected. I want to be able to do this process using a recordset (I will have more control and flexibility) but the database bloats by 1GB for every 200,000 records I modify.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •