Solved

Database Size Increases Dramatically

Posted on 2000-05-02
21
438 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
  • 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
 
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
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 
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

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

760 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now