Solved

Database Size Increases Dramatically

Posted on 2000-05-02
21
474 Views
Last Modified: 2012-06-21
I am using an ADO connection to move data from one MDB file to another. I create SQL statements "on the fly" and use the ADOConnection.Execute(sql) method to do the actual data move.

In one scenario the source database is about 150 megabytes. After the data has moved to the destination, the destination's size is 370 megabytes. Compacting and restoring returns it to 150 megabytes.

In a second scenario the source database is about 4 MB and the resulting destination database is 20 MB.

What is going on? Is there any way I can move the data without getting such a large increase in database size?
0
Comment
Question by:bjames
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 8
  • 4
  • 4
  • +4
21 Comments
 
LVL 7

Expert Comment

by:Believer
ID: 2770612
I doubt it.  (Gee, that's just setting myself up to get blasted!  Oh well...)
What version of Access are you using?  (If 2000 then I can add it to my list of reasons *not* to start using it yet... LOL!)
0
 
LVL 1

Expert Comment

by:pauljk
ID: 2770637
Are you adding and Deleting records?  Access does not do self maintainance and needs to be compacted to reindex itself and remove any ghost records.  It sucks but it's true...  I think 2000 is more impoved.
0
 

Author Comment

by:bjames
ID: 2770675
The databases are 2000.

But when I did DB conversions in '97 and earlier in 2.0 I don't recall running into this. Still, I was using DAO then and not ADO. Does that make a difference?

I know Access does not do self maintenance, but I'm basically just adding records. Where are the ghost records coming from?
0
Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

 
LVL 7

Expert Comment

by:Believer
ID: 2770678
2000 is improved?  I'm expecting just the *opposite* to be true...
0
 
LVL 57
ID: 2770793
One of the big changes in A2000 is that the VBA project file is now totally replaced.

  Before 2000, Access would only recompile and replace the section that was changed.  But MSFT had so many problems creep in, they decided to change it.

  However, that doesn't really fit your problem.  Seems like were talking about replacing records in the target MDB and in that regard, Access has been the same since A95.  

  There was a major change in the way deleted space was handled between A2 and A95.

  I'm afraid your stuck with it.

Jim.
0
 
LVL 54

Expert Comment

by:nico5038
ID: 2771201
Just a wild guess:

Did you try to insert the records "sorted" on primekey?

If not, try it.
If you did, try it without.

My guess is that Access is "fooling" around with overflow area's for filling the database indexes. When the data is inserted in a correct order (should be the database-key-order) it can write them "straight-away", no extra space is needed.

Nico
0
 
LVL 4

Expert Comment

by:wesleystewart
ID: 2773171
I think your problem is all the temp queries Access is creating when you create SQL statements "on the fly".  This is a great way to get a database to bloat.

This is a common problem with Access, and it only seems to get worse with each version.

Since you're using A2000, set the databases to compact on close.

You might want to try calling parameter queries with the criteria that you determine with code.  Or you could build a bunch of stored queries and call them from code.

Other than that, I don't know of a way around it.  I've dealt with this problem for over three years now, so I hope I would have discovered any quick fixes by now.  I'm interested to see if anyone has a better idea?

Wes
0
 

Author Comment

by:bjames
ID: 2773202
The temp queries will make the database bigger, but wouldn't they only make the database which contains them bigger?

In my scenario there are three (3) MDB files involved: 1) My application utility; 2) The Source database; 3) The Destination database.

My application makes an ADO connection to the source and uses that connection to write to the destination. But it is the destination database which is growing.

Thanks
0
 
LVL 4

Accepted Solution

by:
wesleystewart earned 100 total points
ID: 2773250
Maybe the records are growing as they squirt through the network . . .

It sounds like you are moving the entire contents of the source to the destination?  If so, you might want to explore some alternate methods of getting the data there.  Access has never been good at moving large amounts of data, particulary if the data is heavily indexed.  Are your source or destination tables indexed?  If so, you might want to test it by removing all the indices and then executing your SQL.

If you are moving the entire contents of a table, maybe the transferdatabase method is a more efficient way of moving the data.  Perhaps you could even just leave the data in the source and query it out as your app needs it?  That's a lot of reengineering, but if it saves a lot of headaches in the long run it might be worth investigating.

Wes
0
 

Expert Comment

by:BegnrSteve
ID: 2773443
For WesleyStewart,

Am curious as to where in the code to you have the program to compact the tables on close.  Have always built a separate process for that.  If you prefer that I ask this in a separate question, just let me know so you can have the points.  Thanks for your response.

BegnrSteve
0
 
LVL 7

Expert Comment

by:Believer
ID: 2773518
Access 2000 databases have an option you can set to Compact On Close.
0
 

Author Comment

by:bjames
ID: 2773522
I am moving the entire contents of a database. The destination database has a newer schema with added columns and tables for our application. My mandate is to leave the source database alone for the previous version of our software to run concurrently with the new version.

I had forgotten about indexes. Yes, we have a LOT of indexes in the database. (Most are actually quite useless, but I have had no success in convincing my bosses to drop them.) That probably accounts for at least some of the increase.
0
 
LVL 7

Expert Comment

by:Believer
ID: 2773602
A lot of your indeces were probably automatically created by Access.  By default, Access creates an index over fields which end in "ID", "Num", etc.  Go to tools...options...tables/queries the option labeled "AutoIndex on Import/Create" and *delete* the contents of the text box.  Now Access will *not* automatically create any more indexes for you.
0
 
LVL 4

Expert Comment

by:wesleystewart
ID: 2773645
bjames:

I would try Docmd.TransferDatabase.  It sounds like you have an excellent case for it.

Wes
0
 

Author Comment

by:bjames
ID: 2796556
Sorry I haven't been active on this, but they (designers) made some significant changes which had to be addressed so this issue got put on hold. I am about ready to work with it again, however.

TransferDatabase will not be an option for me as one of my mandates is to create a single source code to work for both SQL Server and MS Access - hence the ADO connection. Even so, I have found there have to be differences in the SQL Statements themselves anyway.

Meanwhile, I have run into a new problem (I am entering a new question so don't answer it here) in which without changing any code at all some tables move data on one try but not on the next.
0
 
LVL 54

Expert Comment

by:nico5038
ID: 2797363
Did you try or sorted or not on the prime-key gives differences?

Nico
0
 

Author Comment

by:bjames
ID: 2800093
I only got back to my original problem late yesterday (my R&D department put through some significant changes to the utility) so I have not had a chance to give your suggestions a go. However, today I should be able to do that.

Thanks

NOTE: I also resolved my new problem, so I am truly back to my original issue.
0
 

Author Comment

by:bjames
ID: 2805149
I tried the ORDER BY clause on the primary key but that had no effect on the size of the destination database.

It still went from about 144 megabytes to about 378 megabytes. Compact and Repair will reduce the destination database back to about 144 megabytes.

I'm going to give the index removal a go next.
0
 

Author Comment

by:bjames
ID: 2811963
I gave WesleyStewart's suggestion of removing indexes prior to the port and that did prevent the increase. However, I removed the relationships and indexes manually with the Access GUI. Had I been using DAO I would have written some code to do this. But I am using ADO, and I have only been using ADO about two or three weeks.

How do I drop and readd indexes with ADO?

Or is that a separate question?

Thanks
0
 

Author Comment

by:bjames
ID: 2817038
This is the solution to my problem. I don't know how to implement it via ADO code, and I fear that when my R&D group sent me their major change and I was forced to leave this on hold for a bit I lost my communication link here.

That is very unfortunate as I now know what needs to be done, but don't know how to do it in ADO.

In any event, the presence of the indexes and relationships was the cause of the database growth.

Thanks
0
 
LVL 4

Expert Comment

by:wesleystewart
ID: 2817358
Happy to help.  Sorry I didn't have more time to contribute, but my real job has kept me pretty busy for the last month or so.

Wes
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

737 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question