Re: Access DB grows too fast.
From: Nate Clark (ntclark_at_attglobal.net)
Date: 06/28/04
- Next message: William \(Bill\) Vaughn: "Re: Trigger functionality without trigger"
- Previous message: Nate Clark: "Access DB grows too fast."
- In reply to: Nate Clark: "Access DB grows too fast."
- Next in thread: Nate Clark: "Re: Access DB grows too fast."
- Reply: Nate Clark: "Re: Access DB grows too fast."
- Messages sorted by: [ date ] [ thread ]
Date: Mon, 28 Jun 2004 17:41:42 GMT
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.
>
>
- Next message: William \(Bill\) Vaughn: "Re: Trigger functionality without trigger"
- Previous message: Nate Clark: "Access DB grows too fast."
- In reply to: Nate Clark: "Access DB grows too fast."
- Next in thread: Nate Clark: "Re: Access DB grows too fast."
- Reply: Nate Clark: "Re: Access DB grows too fast."
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
|
