Re: Access DB grows too fast.

From: Nick Cardi (NickCardi_at_discussions.microsoft.com)
Date: 07/01/04 

Date: Thu, 1 Jul 2004 14:37:01 -0700

Nate
You may want try API SQLPrepare statements with smaller chunks of data. You may be overwhelming the access database and in a response it is allocating a huge page file to accommodate your future needs. I am not sure if this possible given your current application architecture. Just a thought. 

-- 
Forever Developing
"Nate Clark" wrote:
> Before anyone invests a lot of time on this ....
> 
> I may have been wrong in my original post in that the updating of an 
> ADODB recordset (strategy 1) causes the huge Access database. I have 
> reverted that code and while it's running right now, I'm not seeing this 
> big DB getting created.
> 
> 
> On the other hand, I believe that I set the VB application's connection 
> up to operate synchronously and that that might be the reason why using 
> the cursor in VB is not growing the DB. If this is true, it would seem 
> like the "huge" database might perhaps be because there are many records 
> of "uncommitted" data in the '.db.
> 
> I will confirm all of this once the current test finishes and after I 
> can setup the C code's connection to be synchronous too and try that out.
> 
> 
> 
> 
> 
> Nate Clark wrote:
> > 
> > Hi All and thanks for the help.
> > 
> > 
> > I have an application that needs to store a large # of records in 
> > multiple tables.
> > 
> > Application is written in VB.
> > 
> > The problem that I am having is that after insert of these records (test 
> > case is 900 K records), I get a '.mdb that is nearly 1 GB in size. I 
> > then compact this database and get it down to about 155 MB in size. The 
> > only activity in getting that huge size is the insert(s) of the large # 
> > of rows.
> > 
> > It seems that Access is wasting huge chunks of space during the import 
> > of these files and/or I am doing something wrong or not doing something 
> > I should be.
> > 
> > I have tried 2 types of "bulk inserts" (BTW I cannot find an actual bulk 
> > insert for Access. Is there one ?)
> > 
> > 
> > 1. Declaring an ADODB recordset using sql such as:
> > 
> >    SELECT * FROM Table WHERE ID = -1
> > 
> > Which is guaranteed to be empty when the rs opens (ID can't be < 0 ), 
> > and then adding to that recordset (.AddNew) and then updating it every 
> > 32 K records or so.
> > 
> > 
> > 2. Writing my data out to a flat file and then calling C code that uses 
> > the ODBC API SQLPrepare technique to create a parameterized SQL 
> > statement which I then use to load data from the flat file.
> > 
> > 
> > (Preliminary results - method 2 much faster)
> > 
> > Both methods give me this huge database after this import.
> > 
> > Further, and somewhat worrisome, after I get about 2+ million records in 
> > the database, I start getting "Invalid Parameter" errors all over the 
> > application (My fields are Long Integer which should provide me with 
> > values in a very large range - well above 2+ million anyway)
> > 
> > 
> > I see from reading my own post that indeed I haven't tried single 
> > "INSERTS". This is something I will try now but I'll go ahead and post 
> > the question as I'm not really confident that is something that's going 
> > to fix this problem.
> > 
> > 
> 
>

Relevant Pages

  • Access DB grows too fast.
    ... Writing my data out to a flat file and then calling C code that uses ... the ODBC API SQLPrepare technique to create a parameterized SQL ... statement which I then use to load data from the flat file. ... Both methods give me this huge database after this import. ...
    (microsoft.public.data.ado)
  • Re: Access DB grows too fast.
    ... "Nate Clark" wrote: ... > ADODB recordset causes the huge Access database. ... Writing my data out to a flat file and then calling C code that uses ... >> the ODBC API SQLPrepare technique to create a parameterized SQL ...
    (microsoft.public.data.ado)
  • Re: Access DB grows too fast.
    ... does not recover space lost when rows are changed or deleted. ... It's only after you compress the database are these ... Writing my data out to a flat file and then calling C code that uses> the ODBC API SQLPrepare technique to create a parameterized SQL> statement which I then use to load data from the flat file. ...
    (microsoft.public.data.ado)
  • Re: Need Help deleting record from text file
    ... In many circumstances retrieval of data from a "home grown" data file will actually be faster than from a general purpose commercial database. ... save his data to disk using a "flat file" unindexed arrangement. ... In fact I never mentioned him saving his data to disk in any format at all. ... Admittedly a nice "ready made" database does make it relatively easy for people to store and retrieve data fairly rapidly without needing to write too much code or to worry about the methods used by the "black box" to perform such feats, and I certainly would not suggest that they should not be used, but I think that for many tasks a "home grown" data format would be as good as, and in some cases better than, a standard database. ...
    (microsoft.public.vb.general.discussion)
  • Re: Just say no to threads [Was: Software architecture]
    ... they knew there was going to be a database in the app. ... Now my colleague just spent a couple/three weeks designing an SQL ... Turns out they have a flat file of sample information, ...
    (comp.object)