[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 502
  • Last Modified:

Database Size Increases Dramatically

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
bjames
Asked:
bjames
  • 8
  • 4
  • 4
  • +4
1 Solution
 
BelieverCommented:
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
 
pauljkCommented:
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
 
bjamesAuthor Commented:
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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
BelieverCommented:
2000 is improved?  I'm expecting just the *opposite* to be true...
0
 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
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
 
nico5038Commented:
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
 
wesleystewartCommented:
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
 
bjamesAuthor Commented:
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
 
wesleystewartCommented:
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
 
BegnrSteveCommented:
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
 
BelieverCommented:
Access 2000 databases have an option you can set to Compact On Close.
0
 
bjamesAuthor Commented:
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
 
BelieverCommented:
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
 
wesleystewartCommented:
bjames:

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

Wes
0
 
bjamesAuthor Commented:
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
 
nico5038Commented:
Did you try or sorted or not on the prime-key gives differences?

Nico
0
 
bjamesAuthor Commented:
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
 
bjamesAuthor Commented:
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
 
bjamesAuthor Commented:
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
 
bjamesAuthor Commented:
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
 
wesleystewartCommented:
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

Independent Software Vendors: 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!

  • 8
  • 4
  • 4
  • +4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now