Really slow procedure A2007 ADO

[JimfromIndy]

I've revised my procedure to update POs from an external source trying
to speed it up. What I've found is that a reliable 4 seconds per
transaction is taken up executing a simple ADO "Find" command:

rst.Find "POLineID=" & rstImpLines!POLineID,,adSearchForward,1

The recordset has 44,000 rows. The rstImpLines recordset (with 2,000
rows) is in random order (sorted by PO number, not by PO Line ID....)

Is there a faster approach? DAO faster? POLineID IS the PRIMARY Key
for the table underlying the rst recordset....

Ideas?

Jim

 

[Dorian]

Is your data in Access or SQL Server?
You could try using SEEK instead of FIND.
-- Dorian
"Give someone a fish and they eat for a day; teach someone to fish and they
eat for a lifetime".

 

[JimfromIndy]

Is your data in Access or SQL Server?
You could try using SEEK instead of FIND.
-- Dorian
"Give someone a fish and they eat for a day; teach someone to fish and they
eat for a lifetime".








- Show quoted text -

Data's in a linked table stored in a backend accdb database on a
network share. Front end is on local machine. I'd pull it into a temp
table locally, but I update every row I go after. I think I'm going to
have to go to a sql update. Perhaps I'll create a local table with all
the updates then post it batch.

 

[david]

Dreadful performance if rst doesn't fit inside the data cache.
Can you adjust that?

4 seconds it too long, so almost any other method will
probably be faster. Can you do a SQL update?

(david)