Re: Database has gigantic size

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: William \(Bill\) Vaughn (billvaRemoveThis_at_nwlink.com)
Date: 04/19/04 

Date: Sun, 18 Apr 2004 17:38:08 -0700

Ah no. Jet has always worked this way. (I've wrote the doc for the earliest
versions). DAO is simply the direct interface to JET. ADO does not make JET
work this way.

MSDE is the "free" deployment version of SQL Server. There is a whole
newsgroup dedicated to it. 

-- 
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
"dkomo" <dkomoNoSpam@cris.com> wrote in message
news:40828B64.75558AE5@cris.com...
> "William (Bill) Vaughn" wrote:
> >
> > When Access/JET does an UPDATE, it deletes the target row and adds a new
row
> > with the changed data. The deleted row is not removed from the database
> > until you compress/compact. Many applications schedule a periodic
compact to
> > free unused space.
>
> Aha, this then is exactly why the database grows so large.  As I said,
> I did something on the order of 60,000 updates, each update changing
> only a single column of the target row.  Moreover, I'm also adding
> columns periodically to the tables as I go along, so the records are
> of variable size.  This must create a huge number of hidden deleted
> rows.
>
> This size problem *did not* occur when I was still using DAO because
> with DAO I was able to open the tables directly without using an
> intermediate recordset, and so apparently the updates didn't leave
> deleted rows hanging about.
>
> I must say, I'm sadly disappointed with ADO at this point and wishing
> I didn't have to use it.  Why were updates within recordsets so
> stupidly implemented?
>
> > If this continues to be a problem, you might consider switching to MSDE
> > which logs all operations and does not require you to compress the
> > database--space is recovered automatically.
> >
>
> What is MSDE?
>
>
>     --dkomo@cris.com
>
> > --
> > ____________________________________
> > William (Bill) Vaughn
> > Author, Mentor, Consultant
> > Microsoft MVP
> > www.betav.com
> > Please reply only to the newsgroup so that others can benefit.
> > This posting is provided "AS IS" with no warranties, and confers no
rights.
> > __________________________________
> >
> > "dkomo" <dkomoNoSpam@cris.com> wrote in message
> > news:4070A541.5068A677@cris.com...
> > > Jezebel wrote:
> > > >
> > > > The main reason you need to compact is that the space taken up by
> > deleted
> > > > records isn't recovered until you do. So, for example, if your app
> > creates
> > > > reports by creating then deleting records in a work table, the
database
> > can
> > > > grow quite quickly.
> > > >
> > >
> > > Ok, but I'm not doing *any* deletes.
> > >
> > > > Your database will also blow out in size if you don't close your
> > recordsets.
> > > > Every time you run a query, a hidden table is created. This is
deleted
> > and
> > > > the space recovered if you close the recordset -- but if your app
simply
> > > > lets the recordset variable go out of scope, the table may persist
and
> > > > continue to take up space until you do a compact.
> > > >
> > >
> > > This may be worth experimenting with.  I don't do queries.  However,
> > > I've estimated that I do somewhere on the order of 60,000 updates
> > > using atomic rs.Updates during the course of the run.  I do close and
> > > reopen the recordset a few dozen times, but 1000 updates and a few
> > > adds may occur before I close it.  If hidden tables are getting left
> > > behind in the database, that may explain the ballooning size of it.
> > >
> > > One theory I have is that for each update possibly the original record
> > > is getting orphaned inside the database for some unfathomable reason.
> > > 60,000 records of size 152 bytes each adds up to 9,120,000 bytes.  The
> > > records are probably larger due to hidden system fields, so the 15 MB
> > > size is explainable.  But if this is the explanation, the next
> > > question is, why is it happening?
> > >
> > >
> > >     --dkomo@cris.com
> > >
> > >
> > > > The limit is 2GB for an Access database file. The error messages can
be
> > > > quite strange if you hit this limit.
> > > >
> > > > "dkomo" <dkomoNoSpam@cris.com> wrote in message
> > > > news:40707BF4.7067E53D@cris.com...
> > > > > Don Hanfland wrote:
> > > > > >
> > > > > > Hi dkomo,
> > > > > >
> > > > > >   Did you mean the db size was 15KB when it was DAO or is it
15MB?
> > If
> > > > it is
> > > > > > 15MB then 460KB is away lot smaller then 15MB.
> > > > > >
> > > > >
> > > > > I don't understand the question.  The ADO database was 15 MB
before
> > > > > compaction.  The DAO database containing the exact same records
was
> > > > > 460 KB.
> > > > >
> > > > > Perhaps someone can explain why Access databases need to be
> > > > > compacted.  What is in those databases that causes a size bloat?
This
> > > > > might point me in the direction of what is going wrong.
> > > > >
> > > > > > You can use ADO to UPDATE,DELETE,INSERT on tables without have
to
> > have a
> > > > > > recordset, all do is use the connection and execute a SQL string
to
> > > > that;
> > > > > >
> > > > > >   sCMD = "DELETE FROM youtTableWHERE lIDField= " & glngFieldID
> > > > > >   goConn.Execute sCMD
> > > > > >
> > > > > >   sCMD = "INSERT INTO yourTable SELECT * FROM someOtherTable"
> > > > > >   goConn.Execute sCMD
> > > > > >
> > > > >
> > > > > Ok, good to know.  Unfortunately I need to use precise
> > > > > record-by-record operations for which SQL is pretty useless.  I
need
> > > > > to compare dates on records in the tables with dates on records
being
> > > > > read in one at a time from a text file, and add a record to the
table
> > > > > when a date is missing, or update a table record when the dates
> > > > > match.  And I need to keep the records in the tables in sequence
by
> > > > > date!  It's kind of like a file merge operation where the
comparison
> > > > > between files is done on dates.  So I end up executing a lot of
ADO
> > > > > rs.MoveNext, rs.AddNew and rs.Update statements.
> > > > >
> > > > >
> > > > >       --dkomo@cris.com
> > > > >
> > > > >
> > > > > > Hope this helps!
> > > > > > --
> > > > > > Don Hanfland - MVP, Visual Basic
> > > > > > visit http://vbCity.com/ for more FAQ's and friendly help!
> > > > > >
> > > > > > "dkomo" <dkomoNoSpam@cris.com> wrote in message
> > > > > > news:407039E0.A9EB8C2F@cris.com...
> > > > > > > I converted a VB 6 program from using DAO to ADO to create an
> > Access
> > > > > > > 2002 database.  The resulting database built with ADO is
*huge*
> > > > > > > compared to the one built with DAO -- 15 MB vs. 460 KB -- 
using
> > > > > > > exactly the same data.  When I open the tables in the two
> > databases,
> > > > > > > they look identical.  I can compact the ADO database using the
> > Access
> > > > > > > Compact and Repair Database utility and it will compress down
to
> > 416
> > > > > > > KB.
> > > > > > >
> > > > > > > Has anyone seen anything like this before?  Because of the way
the
> > > > > > > database is created, I have to make many thousands of updates
to
> > the
> > > > > > > records, as well as adding hundreds of new records during the
> > build.
> > > > > > > The main difference in the old DAO program was that I made the
> > changes
> > > > > > > directly to the tables without using a recordset.  With ADO I
have
> > no
> > > > > > > choice but to use a recordset.  Are the changes I make with
ADO
> > not
> > > > > > > being made directly to the table somehow (even though Access
still
> > > > > > > displays the records correctly), but sit as some kind of
temporary
> > > > > > > records until I compact the database?  Or does ADO just create
a
> > lot
> > > > > > > of junk inside the database when it runs the adds and updates?
> > > > > > >
> > > > > > >
> > > > > > >      --dkomo@cris.com

Relevant Pages

  • Re: Any REAL reason to use ADO vs. DAO?
    ... download the data via DAO, and trying the same thing via ... ADODB took over 24 hours... ... a localized Access database where a VB5 app (project started ... I understand that the make table queries were Jet, ...
    (microsoft.public.vb.general.discussion)
  • Re: Missing DAO licence on target PC
    ... there is no point to using DAO and Jet. ... you on, that does, in fact, mean he's got a JET database. ... No one is disputing that DAO is obsolete. ...
    (microsoft.public.dotnet.languages.vb)
  • Re: Sign In/Out Program
    ... ADO is capable of MULTI USER that the database is open by many user at ... used DAO and ms access database for single user only. ... DAO works well with Jet. ... but mdb is a file-based database and will have the same ...
    (microsoft.public.vb.general.discussion)
  • Re: Missing DAO licence on target PC
    ... compare SQL + ADO to JET + DAO ... but JET is obsolete; and who wants to use an obsolete DAL just because ... DAO and JET have both been obsoleted for a decade. ... DAO doesn't fit into the 'worlds most popular database' and it hasn't ...
    (microsoft.public.dotnet.languages.vb)
  • Re: So....Forms Recordset to ADO?
    ... If you are using a standard MDB database then all your forms will have DAO ... recordsets, and if you want to work with them then you should use DAO. ...
    (microsoft.public.access.formscoding)