Trying to speed up the writing of data

[Sandy Hayman]

Hi
I have an Access front end with a Ingres back end using an ODBC connection.

Each month I receive two csv files that need to be imported into the
database. The first contains around 330,000 records. The second contains
over 500,000 records. The data in the csv is mostly numeric and the access
database needs to look up the values for a few of the fields (Eg.

Series data
01121130, 0150, HONG, 16, 1, 15.215, Number, 18.658, 12.2

The first field is a code that needs to be looked up and assigned the
internal code
Second field is a code that needs to be looked up and assigned the internal
code
Third field needs to looked up and converted to a numeric internal code
Seventh field needs to be looked up and converted to a numeric internal
code.
I also add an incrementing id to the destination table.

I have tried several different write methods to speed up this process as
quickly as I can but can't seem to make it go faster.

The first couple of thousand takes 3 seconds per thousand but then each
thousand takes longer and longer because of the following.

Each time a new record is written, it needs to check to see if the series
code already exists. As the table gets bigger, it takes longer to check the
data. I am currently using a DLookup for this part of the functionality.

Can anyone suggest a faster way to get these records into the system. At the
moment it's taking about 3-4 hours for the 330,000 records. The old SQL
method we used (directly in our back end) used to take half an hour and I am
sure I can make this faster but can't work out to get it to go any faster.
We can't use the old method for a variety of reasons that I won't go into.

Thanks in advance.

Sandy

 

[Jeff Boyce]

Sandy

DLookup() is quite slow compared to other approaches.

Have you tried using a query and joining to the 'lookup' tables?

Are the fields on which you'd join indexed? (or is Referential Integrity
set between the lookup table(s) and the main table?)

One approach (you don't mention the approach you are using) would be to
import the raw data directly into Access, then use a query to 'parse' the
raw data into a permanent table (using an append query). You could add
another query to do the joins to the looked up values/tables.

Finally, you could use yet another query to assemble what needs to go to the
Ingres back-end and append the records to Ingres-db.

Good luck

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Mike Painter]

Hi
I have an Access front end with a Ingres back end using an ODBC
connection.
Each month I receive two csv files that need to be imported into the
database. The first contains around 330,000 records. The second
contains over 500,000 records. The data in the csv is mostly numeric
and the access database needs to look up the values for a few of the
fields (Eg.
Series data
01121130, 0150, HONG, 16, 1, 15.215, Number, 18.658, 12.2

The first field is a code that needs to be looked up and assigned the
internal code
Second field is a code that needs to be looked up and assigned the
internal code
Third field needs to looked up and converted to a numeric internal
code Seventh field needs to be looked up and converted to a numeric
internal code.
I also add an incrementing id to the destination table.

Each time a new record is written, it needs to check to see if the
series code already exists. As the table gets bigger, it takes longer
to check the data. I am currently using a DLookup for this part of
the functionality.

Dlookup is slow.
I'd say import the table and relate it to look up any needed codes.
What do you mean when you have to "check to see if the series code already
exists."
For a field, for any field?
What happens if it does exist?

Just importing teh table will also give you an idea as to what might be
slowing the import down. Virtual RAM on a new solid state drive would give
you a bit more space.