how best to populate a large table

[Erwin Kalvelagen]

Hi:

I have written an application in Delphi that generates a
number of large ascii files. Another group has written
a VB application that reads these text files, populates
Access tables and then does some analysis/visualization.
The problem is that the VB app is very slow in the loading
phase (read ascii, populating Access tables). I have heard
about my app taking seconds and then the load step in VB
taking more than a minute. I was asked whether I could
create Access tables directly in my app.

May be you can help me with some questions I have:
- I suspect populating Access is slow, thus diminishing
the probability I can do this faster?
- How to populate a large table efficiently? Is there
something like bulk insert? What are best settings
wrt locking etc to do many inserts?
- I can populate directly from my app, e.g. through
ADO or I could write say CSV files and use Access COM
object to read them in. Any expected difference in
performance?

Thanks very much.

Erwin

 

[John Vinson]

I have written an application in Delphi that generates a
number of large ascii files. Another group has written
a VB application that reads these text files, populates
Access tables and then does some analysis/visualization.
The problem is that the VB app is very slow in the loading
phase (read ascii, populating Access tables).

I would guess you're looping through the files, doing an AddNew for
each record?

Consider instead using the TransferText method to run an Append
operation into the table; or (again) use TransferText to link to the
text files, and run an Append query based on the linked table. This
will usually be MUCH faster than looping and adding one record at a
time.

 

[Erwin Kalvelagen]

Thanks very much. TransferText worked like a charm.

----------------------------------------------------------------
Erwin Kalvelagen
GAMS Development Corp., http://www.gams.com
(e-mail address removed), http://www.gams.com/~erwin