Really slow procedure A2007 ADO
in [General]
|
Prev: A2007 SP2 sending pdf attachment to email comes out portrait
Next: Find duplicate records in two tables.
From: JimfromIndy on 29 Mar 2010 15:48 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
From: Dorian on 29 Mar 2010 17:46 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" wrote: > 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 > . >
From: JimfromIndy on 29 Mar 2010 18:02 On Mar 29, 5:46 pm, Dorian <Dor...(a)discussions.microsoft.com> wrote: > 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" wrote: > > 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 > > .- Hide quoted text - > > - 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.
From: david on 3 Apr 2010 21:14
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) "JimfromIndy" <jim.shaffer(a)gmail.com> wrote in message news:bebdc4ff-edd0-4470-8bfe-8517890287cc(a)r27g2000yqn.googlegroups.com... > 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 |