ADODB SQL extremely slow

[Zakraket]

I have this database, MS Access front end on a SQL database (SQL Express server) with an ODBC-connnection. Everything runs quite nice - even thought the connection to the SQL-server is bad (bad network, cannot fix), however I have one problem/challenge.

Backend used to be Access, but I'm connecting the client to SQL now. In most cases I use DAO (because the client was running on Access-backend before) to get recordsets, but while connecting to SQL I'm sometimes switching to ADODB, mostly because of speed

I have this procedure that needs to update records based on the IDs that are provided by another query

so:

 set rst1 currentdb.openrecordset("select ID from table where .. ") 'DAO
 rst2.open "select ID, field1 from table"
  
 do while not rst1.eof
      rst2.filter = "ID=" & rst1!ID
      if not rst2.eof then rst2.field1=now()
 end if

The table holds only 5000 records. When running the code it takes more than a minute (or two!) to do the .filter which is ridiculous :banghead:

Now, I'm not an export but I know it's often better to just do

 set rst1 currentdb.openrecordset("select ID from table where .. ") 'DAO
  
 do while not rst1.eof
      rst2.open "select ID, field1 from table where ID=" & rst1!ID
      if not rst2.eof then rst2.field1=now()
 end if

and this works, but it still kinda slow (takes around 1,5sec per record, which can take a while when updating 10+ records) so I'm looking for the most efficient way to do this (ADODB, DAO, maybe execute "UPDATE" SQL)

It's just that I cannot believe how slow the first option is (.filter) , I'm thinking I'm just doing something wrong. :banghead:

Of course, the actual situation in my DB is a bit more complex, but I think I have described the most important things.

Anybody have any thoughts? Is using rstADODB.filter something to avoid altogether?
Maybe this option doesn't work properly when using it on a slow network (2Mb/s from PC to SQL-server, which is just what I'm stuck with here, companynetwork)

 

[CJ_London]

1. indexing can make a big difference - so are your tables properly indexed?
2. using criteria rather than filtering reduces network traffic - so look to apply your filter as criteria instead
3. and since you have moved to sql server you might better off using stored procedures - not clear from your dummy code what you are doing

 

[Zakraket]

1. indexing can make a big difference - so are your tables properly indexed?
2. using criteria rather than filtering reduces network traffic - so look to apply your filter as criteria instead
3. and since you have moved to sql server you might better off using stored procedures - not clear from your dummy code what you are doing

Thanks for your quick reply

1. Firstly I'm no expert on indexing. There are a few indexes on the table and the actual code filters on the table-key (which is also set as UID and thus automatically has an index).
I'm assuming that when the UID field has an index (it gets one automatically) and I'm filtering on this field, not much more can be done on indexing?

(BTW, the database is migrated using Microsoft SQL Server Migration Assistant for Access, including keys, relations, table-indexes)

2. that's the second method/code I posted if I understand you correct. This works a lot better (1,5sec opening the recordset), only when the amount of records to be updated get larger (say 100 records) this is taking too much time, but 100x1,5sec is at least a lot better then 100x100sec)

3. Have no real experience yet on that field. Might try that in the near future (but is it possible to use a stored procedure on SQL from Access to filter on ID?? I thought not)


The first option I will try is using an update SQL through a ADODB.connection. I expect it will be a lot faster.

Btw, the whole program runs fine, all kinds of searches, filters, query's, (linked) views, and nothing is really slow despite the bad connection.
But using a filter on a simple (table) recordset?


edit: using method 2 now takes around 0,5sec per record, which is not bad.

 

[CJ_London]

not much more can be done on indexing?

my bad, I should have said recordset rather than table - filtering an ADO recordset does not use indexing unless set in the recordset - it is a sequential process - see these articles

https://support.microsoft.com/en-us/kb/290060
http://www.w3schools.com/asp/ado_ref_recordset.asp

but is it possible to use a stored procedure on SQL from Access??

yes see this article

https://accessexperts.com/blog/2011...-procedure-guide-for-microsoft-access-part-1/

or google to find out more

there are other things that can be done such as bulk update - with or without removing indexes and talk to your IT department - do they have procedures in place to refresh indexes on a regular basis? They can be become very inefficient if the table has lots of inserts/deletes/updates

 

[Galaxiom]

The job should be done in a query.

 

[Zakraket]

I have some thing to work with, thanks for your replies.

 

[The_Doc_Man]

Concur with Galaxiom. If you are updating records and can properly your WHERE clause to limit what an UPDATE query would touch, you do VASTLY better to do it as an UPDATE query - which might incur almost NO network traffic (other than passing in an SQL query and getting back the "handshake" when it is done.)

As to ADO/DAO ... I prefer DAO for this, because if you open the database as a DAO object, you can use the daoDatabase.Execute method to trigger the operation and can use the .RecordsAffected property of that same object to see how much you did. Plus you can set traps using dbFailOnError as the second argument of the .Execute (the first being the actual SQL query) to see if you did anything.

But I emphasize that DAO is my preference and there can be many other reasons why ADO would be preferred.

 

[Simon_MT]

I heard thsat ADO has being deprecated along with SQL OLE Reader.

Indexing is equally important on SQL Server side.

Simon

 

[CJ_London]

I heard thsat ADO has being deprecated along with SQL OLE Reader

are you sure? these links indicate otherwise - can't find anything after 2011

https://redmondmag.com/articles/201...pen-database-connectivity-for-sql-server.aspx

http://blogs.msdn.com/b/adonet/arch...-oledb-provider-deprecation-announcement.aspx

http://weblogs.sqlteam.com/dang/archive/2011/09/04/rip-ole-db.aspx

 

[Simon_MT]

It matters if you are using web applications and accdb files.

Simon