Speed improvements for cyclic data processing and transformation tasks

[The Frog]

Hi everyone,

I am trying to refine some ADO code that is used to extract data from
excel spreadsheets and do a simple ETL type process to place the data
into a table. The code works fine and is seemingly reliable enough. It
is also as slow as <insert metaphor>.

Basically the process is taking the excel sheet and reading it into a
disconnected recordset. Easy done. The next step is done with a
combination of ADO and DAO code. The incoming rows are checked one at
a time against the existing import data table - new rows are added as
needed and existing rows are updated as needed. This is achieved by
using two loops, one nested in the other.

Before I go and make this more 'efficient' I thought that I would ask
peoples opinions on speeding this up. I was thinking of using filters
to reduce the size of the loops, but maybe there is even a better way.

It was suggested that I load the ADO recordset into an array for
processing, but I really cant see any advantage here for that
approach.

Any suggestions would be greatly appreciated before I re-vamp this
piece of code. Is there a speed difference between find and filter
methods for example....

Thanks in advance

The Frog

[Tom van Stiphout]

On Fri, 28 Sep 2007 12:27:09 -0000, The Frog
<Mr************@googlemail.comwrote:

I would rather look at a completely different method. For example
creating the Excel file as an attached table, and running an Append
query to copy the data to the destination table.

-Tom.

>Hi everyone,

I am trying to refine some ADO code that is used to extract data from
excel spreadsheets and do a simple ETL type process to place the data
into a table. The code works fine and is seemingly reliable enough. It
is also as slow as <insert metaphor>.

Basically the process is taking the excel sheet and reading it into a
disconnected recordset. Easy done. The next step is done with a
combination of ADO and DAO code. The incoming rows are checked one at
a time against the existing import data table - new rows are added as
needed and existing rows are updated as needed. This is achieved by
using two loops, one nested in the other.

Before I go and make this more 'efficient' I thought that I would ask
peoples opinions on speeding this up. I was thinking of using filters
to reduce the size of the loops, but maybe there is even a better way.

It was suggested that I load the ADO recordset into an array for
processing, but I really cant see any advantage here for that
approach.

Any suggestions would be greatly appreciated before I re-vamp this
piece of code. Is there a speed difference between find and filter
methods for example....

Thanks in advance

The Frog

['69 Camaro]

Hi.

The incoming rows are checked one at
a time against the existing import data table - new rows are added as
needed and existing rows are updated as needed. This is achieved by
using two loops, one nested in the other.

No wonder it's so slow. You're cursoring through two datasets. It's much
faster to import the data into a temporary table, then use a single UPDATE
query that includes an OUTER JOIN to join the temp table to the table where
you want the data to be stored. The UPDATE query will update any existing
rows and add new rows as needed.

Please see the following Web page for former Access MVP Joe Fallon's
instructions on how to build the UPDATE query:

http://groups.google.com/group/micro...162298434d4396

It was suggested that I load the ADO recordset into an array for
processing, but I really cant see any advantage here for that
approach.

Let the database engine do the work for you, and do it far quicker, too.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
Blogs: www.DataDevilDog.BlogSpot.com, www.DatabaseTips.BlogSpot.com
http://www.Access.QBuilt.com/html/ex...ributors2.html for contact
info.

[The Frog]

Hi Fellas,

Thanks for the suggestion on the importing routine, it works a treat.
I still have an issue with the exporting of the data though. The
'export' is actually done in Excel (as an add-in). The worksheet that
is processed depends on the data in this database to do its work.
Effectively, for each row on the sheet I have to hunt down the
appropriate record in the database. I am still curious to know if
there is a difference between the speed of operation of the filter
method and the find method. I can really on do the second part in
Excel and not in Access, so I am using ADO code to keep a disconnected
recordset of the 'Product' table that the macro relies on.

The second part certainly works okay, its just not that quick. I am
currently using the filter method but am open to any suggestions.

Cheers

The Frog

ps: the append method is working in a fraction of the time of the
previous - many thanks indeed.