Re: Fastest record create method with JET 4.0
- From: "Bob Barrows [MVP]" <reb01501@xxxxxxxxxxxxxxx>
- Date: Tue, 12 Sep 2006 07:16:31 -0400
pauladams777 wrote:
I need advice on the fastest method for adding data to an existing
Access database.
Dramatically simplified, I have an application written in C++ that
need to push records into an existing Access database.
At what point does this push need to occur? At runtime? Or are you planning
to ship a populated mdb file to the customer?
If the latter, I would be using the import functionality built into Access
to do this.
However, I suspect this is more of an upgrade, i.e., you intend to ship a
setup file that moves the data from an existing database into a new Jet
daabase as part of an application upgrade. Since this is a one-time move,
why be so concerned with performance?
Due to the increased size of the install, I wish to avoid the need
for the Access Runtime, and so am trying to use the Jet 4.0 engine.
Basically, all my application does with the Access database is
connect, open the table, add a record, fill in the field values, save
the record, close the table and disconnect. (I actually add lots of
records in each table open / connection, but that isn't the point.)
This makes it sound as if you are using a cursor (recordset). This is your
first target for improvement: use a sql DML (INSERT, UPDATE, DELETE)
statement rather than a cursor. My preference would be to use a saved
parameter query and pass the data to be inserted as parameters. However, if
you'd rather avoid saved parameter queries, the task can be performed by
using a Command object to pass parameter values to a string containing a sql
statement and parameter markers. I don't do C++ so I have no C++ examples of
doing this. Here are some links showing how to do it in vbscript, which
should be translatable to C++:
Saved parameter queries:
http://groups.google.com/group/microsoft.public.inetserver.asp.db/msg/7d6e8544e2fd6889?oe=UTF-8
Parameter markers:
http://groups-beta.google.com/group/microsoft.public.inetserver.asp.db/msg/72e36562fee7804e
If you are committed to using a cursor, are you at least using a
disconnected recordset and using UpdateBatch to send all your new records at
once? Given that Jet does not support batched statements, the benefits of
this approach might not be huge, but I suspect there will be at least some
benefit.
Currently I use ADO to connect to Jet 4.0. However, I have found
that the speed is really slow compared to other databases I am using.
I understand that DAO would be faster, as it is Access / Jet specific.
There are many cases where DAO outperformed ADO, but running sql statements
was not one of them IIRC. Cursor performance definitely was one area where
DAO performed better (when using a Jet backend that is).
However, I can't find any DAO specific discussion group.
Since DAO is now unsupported, and was mainly used with VBA when it was
supported, you will be very fortunate indeed to find a C++ group concerning
DAO. However, there is a VBA/DAO discussion group in the Access hierarchy
where you might be able to find someone who has used DAO in C++.
Can anyone give me some pointers (or pointers to information sources)
on the very basics of how to approach use of DAO - I haven't got the
first clue.
Sorry. Again, I don't do C++. I'm sure you found a multitude of sources that
showed how to do it in VBA ... can't those simply be translated to C++?
--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"
.
- Prev by Date: Re: DefinedSize in ADOX
- Next by Date: Re: Fastest record create method with JET 4.0
- Previous by thread: Re: Fastest record create method with JET 4.0
- Next by thread: Re: Fastest record create method with JET 4.0
- Index(es):
Relevant Pages
|
|
