Faster record processing

[Ken Warthen]

I have a linked Excel table in an Access 2007 database. I need to retrieve
several fields of data from the spreadsheet and update fields in different
tables. In some cases my routines are processing as many as 60,000 records.
This can take up to fifteen minutes or more. Would there be any performance
gain by moving all the linked spreadsheet data into a table before retieving
the missing values?

Ken

 

[Alex Dybenko]

Hi,
yes, I think this will bring an improvement, you can also add keys to this
new table if you doing joint on it for example

--
Best regards,
___________
Alex Dybenko (MVP)
http://accessblog.net
http://www.PointLtd.com

 

[John W. Vinson]

I have a linked Excel table in an Access 2007 database. I need to retrieve
several fields of data from the spreadsheet and update fields in different
tables. In some cases my routines are processing as many as 60,000 records.
This can take up to fifteen minutes or more. Would there be any performance
gain by moving all the linked spreadsheet data into a table before retieving
the missing values?

Ken

Absolutely. Put appropriate indexes on the tables involved (the linking fields
in particular) and you'll see a dramatic improvement. Excel spreadsheets
cannot be indexed so they must be parsed one record at a time.

 

[vanderghast]

Probably, but I suspect your program will still spend most of its time in
APPENDING one row at a time in a loop. Try to add as many rows as possible
in one step (with a query involving the temp table you would create) with an
SQL statement, RATHER THAN LOOPING on a recordset, and adding one row at a
time.


Vanderghast, Access MVP