Really slow procedure A2007 ADO

  • Thread starter Thread starter JimfromIndy
  • Start date Start date
J

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
 
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".
 
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 -
Click to expand...

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.
 
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)
 
Back
Top