Improving ADO perofmance
I have been tasked with improving the performance of an existing application written long ago by someone else.
In the application there are many 'lookup' routines that only return a single value.
For example a routine could be passed a unique PaymentStatusID to return the text description the status (For example: 'Paid In Full') as stored in tblPaymentStatus
Usually I use DAO but this application application uses ADO exclusively and extensively. I really would rather not change all of the lookups to DAO.
In DAO my routine to lookup the payment status would look like this:
I open the input query forward only and read only.
In the old application I am tasked with improving, the DAO definition
For some reason the previous developer used adOpenKeyset, adLockOptimistic on all file IO, even if ReadOnly and Forward only were all that was required.
There are dozens of these 'Lookup' routines, all coded with ADO and the adOpenKeyset, adLockOptimistic parameters. I'd rather not change all of them to DAO.
I'm pretty sure I could improve the performance just by revising
rs.Open selectString, CurrentProject.Connection,
to
rs.Open selectString, CurrentProject.Connection,
but I wanted to check with the EE experts before I make all of those changes.
Other than revising then all to DAO, any alternate/better suggestions to improve the performance of these lookup routines would be welcome.
In the application there are many 'lookup' routines that only return a single value.
For example a routine could be passed a unique PaymentStatusID to return the text description the status (For example: 'Paid In Full') as stored in tblPaymentStatus
Usually I use DAO but this application application uses ADO exclusively and extensively. I really would rather not change all of the lookups to DAO.
In DAO my routine to lookup the payment status would look like this:
Public Function getPaymentStatusStringFromID(passedID As Long) As String
'
getPaymentStatusStringFromID = "UnKnown"
'
selectString = "Select [Description] From qryADrop_PayStatus Where [UseID] = " & passedID
Set db = getCurrentDbC
'
Dim rs As DAO.Recordset
Set rs = db.OpenRecordset(selectString, dbOpenForwardOnly, dbReadOnly) ''''/////// Read Only ///////
If rs.EOF Then
Else
'
getPaymentStatusStringFromID = Nz(rs!description, 0)
'
End If
'
rs.Close
Set rs = Nothing
End FunctionI open the input query forward only and read only.
In the old application I am tasked with improving, the DAO definition
Dim rs As DAO.Recordset
Set rs = db.OpenRecordset(selectString, dbOpenForwardOnly, dbReadOnly) ''''/////// Read Only ///////Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset
rs.Open selectString, CurrentProject.Connection, adOpenKeyset, adLockOptimisticFor some reason the previous developer used adOpenKeyset, adLockOptimistic on all file IO, even if ReadOnly and Forward only were all that was required.
There are dozens of these 'Lookup' routines, all coded with ADO and the adOpenKeyset, adLockOptimistic parameters. I'd rather not change all of them to DAO.
I'm pretty sure I could improve the performance just by revising
rs.Open selectString, CurrentProject.Connection,
to
rs.Open selectString, CurrentProject.Connection,
but I wanted to check with the EE experts before I make all of those changes.
Other than revising then all to DAO, any alternate/better suggestions to improve the performance of these lookup routines would be welcome.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
This mass of single field lookups indicates a complete lack of understanding of how queries with joins work. It will take more work, but the solution is probably going to be to change the form's RecordSource query to join (probably left joins) to the lookup tables. There really isn't any way to make this design more efficient.
This change would be recommended regardless of whether the BE is Jet/ACE or ODBC.
ADO was all the rage for a single version of Access. Prior to that version and subsequent to that version, the default was DAO. I never jumped on that band wagon so all my apps use primarily DAO regardless of what the BE database is.
This change would be recommended regardless of whether the BE is Jet/ACE or ODBC.
ADO was all the rage for a single version of Access. Prior to that version and subsequent to that version, the default was DAO. I never jumped on that band wagon so all my apps use primarily DAO regardless of what the BE database is.
Hi,
performance is a very common word, it can mean anything and can have a lot of reasons. I would not just look to queries which are trying to get a single value, that should be in general no big difference for performance if you use DAO, ADO or a DLookup (which is also DAO in ACCDB but ADO in ADPs).
More interesting is where you use these single value lookup procedures. Also, like Mark Edwards said above, what's the backend used. "CurrentProject.Connection
The usual performance killers are more the work with bigger amount of data, like JOINs between tables where Access does the join work locally or bad indizes on tables etc. Of course, if such functions like the one in your posting is used in a query of Access it can also be a performance killer - in that case a DLookup would be better. In case of a database server backend it would be the better way to use a stored procedure or user defined function to get a SELECT executed using a parameter value so everything can be done on the DB-server directly without Access interaction.
So you see, you need to explain more about this database for real performance recommendations.
Cheers,
Christian
performance is a very common word, it can mean anything and can have a lot of reasons. I would not just look to queries which are trying to get a single value, that should be in general no big difference for performance if you use DAO, ADO or a DLookup (which is also DAO in ACCDB but ADO in ADPs).
More interesting is where you use these single value lookup procedures. Also, like Mark Edwards said above, what's the backend used. "CurrentProject.Connection
The usual performance killers are more the work with bigger amount of data, like JOINs between tables where Access does the join work locally or bad indizes on tables etc. Of course, if such functions like the one in your posting is used in a query of Access it can also be a performance killer - in that case a DLookup would be better. In case of a database server backend it would be the better way to use a stored procedure or user defined function to get a SELECT executed using a parameter value so everything can be done on the DB-server directly without Access interaction.
So you see, you need to explain more about this database for real performance recommendations.
Cheers,
Christian
ASKER
Thanks for the responses. I should have explained more about the application and the assigned task.
It is a split FE and BE application. Both the FE and BE are purely Access MDB's and were developed using Access 2003. There are approximately 30 users in the application at all times.
In addition to trying to improve performance, the primary focus of this task is to make sure the application will run in Access 2013 on a Windows 10 machine as an accdb.
There are not a lot of hours in the budget so, even though I agree this is a poorly designed application, a complete redesign is not possible. The client asked that when upgrading the application "see if there are any performance improvement you can make".
In all of the 'lookup' routine I noticed that way the input query was being opened 'adOpenKeyset, adLockOptimistic' could put locks or restrictions on the base table that might affect the response other users, accessing the same table might notice. My thinking was that even if changing the parameters used to open the table in the lookup routine to 'adOpenForwardOnly, adLockReadOnly' didn't improve the performance of the lookup routine, it might improve the overall performance of the application by reducing locks on the base table.
I wasn't sure if any of my assumptions were valid which is why I requested input from the Experts.
Hopefully that clears up the focus of my task and the thinking behind my original questions.
It is a split FE and BE application. Both the FE and BE are purely Access MDB's and were developed using Access 2003. There are approximately 30 users in the application at all times.
In addition to trying to improve performance, the primary focus of this task is to make sure the application will run in Access 2013 on a Windows 10 machine as an accdb.
There are not a lot of hours in the budget so, even though I agree this is a poorly designed application, a complete redesign is not possible. The client asked that when upgrading the application "see if there are any performance improvement you can make".
In all of the 'lookup' routine I noticed that way the input query was being opened 'adOpenKeyset, adLockOptimistic' could put locks or restrictions on the base table that might affect the response other users, accessing the same table might notice. My thinking was that even if changing the parameters used to open the table in the lookup routine to 'adOpenForwardOnly, adLockReadOnly' didn't improve the performance of the lookup routine, it might improve the overall performance of the application by reducing locks on the base table.
I wasn't sure if any of my assumptions were valid which is why I requested input from the Experts.
Hopefully that clears up the focus of my task and the thinking behind my original questions.
I would do the conversion. There may be other issues that require you attention, so - on a limited budget - get it running first, then optimise if budget allows.
My guess is still, that where you could optimise would not be the lookups which - with an accdb backend - should be pretty fast anyway.
My guess is still, that where you could optimise would not be the lookups which - with an accdb backend - should be pretty fast anyway.
Hi,
I also would say that changing the parameters to read-only would not affect the performance at all. Also in case of "OpenKeyset" or "LockOptimistic" no locks are used until you begin changing anything. As long as you read only a value there should be absolutely no difference especially because you read out one single value only and then closing the recordset. If you would add, edit or delete a record in such a recordset it would use a lock. The readonly/forwardonly parameters are very much faster if you try to read out a big number of records i.e. using a Do..Until loop with MoveNext in the recordset.
Think of a form's recordset. It also uses a Keyset/LockOptimistic parameter to be able to change records in the form. But of course it would not lock anything until the record is edited and in case of optimistic lock the lock would be applied in the moment of saving only (that's why it is optimistic) and then immediately released.
Try to find out if any of these lookups are used directly in i.e. another query, this could indeed be a performance killer. In that case, use a DLookup in the query instead (if the query should be still updatable after that) or in a read-only query use a JOIN. Also if you have i.e. a textbox in a continous form which has a function definition starting with "=" and the name of such a lookup function this could make the form very slow. In this case also, don't exchange it with "=DLookup...", instead, insert the "DLookup" into the query the form is based on and then bind the textbox to this field which makes the form much faster.
Cheers,
Christian
I also would say that changing the parameters to read-only would not affect the performance at all. Also in case of "OpenKeyset" or "LockOptimistic" no locks are used until you begin changing anything. As long as you read only a value there should be absolutely no difference especially because you read out one single value only and then closing the recordset. If you would add, edit or delete a record in such a recordset it would use a lock. The readonly/forwardonly parameters are very much faster if you try to read out a big number of records i.e. using a Do..Until loop with MoveNext in the recordset.
Think of a form's recordset. It also uses a Keyset/LockOptimistic parameter to be able to change records in the form. But of course it would not lock anything until the record is edited and in case of optimistic lock the lock would be applied in the moment of saving only (that's why it is optimistic) and then immediately released.
Try to find out if any of these lookups are used directly in i.e. another query, this could indeed be a performance killer. In that case, use a DLookup in the query instead (if the query should be still updatable after that) or in a read-only query use a JOIN. Also if you have i.e. a textbox in a continous form which has a function definition starting with "=" and the name of such a lookup function this could make the form very slow. In this case also, don't exchange it with "=DLookup...", instead, insert the "DLookup" into the query the form is based on and then bind the textbox to this field which makes the form much faster.
Cheers,
Christian
Based on your DAO query in your initial post and your further explanation, it looks like you're using a pure Access be with linked tables, as your DAO query does not contain any connection or database location path. Your comment "There are approximately 30 users in the application at all times" makes it sound like everyone is using the same front-end (fe) file. Also, are you running this setup over a LAN (hopefully not over a VPN)? and is the front-end being run from the server? (Sounds like it is if you have about 30 users at one time.)
Just a few basics to think about...
First, a network is an automatic performance drag on a scale of 10x, so you want to keep traffic flow to a minimum. The BEST performance of a straight Access setup over a network is realized when everyone has their own copy of the fe on their machine and just pulling the table data over the network. Keeping the fe on the server and having everyone run it from there is a REAL performance drag.
With a pure Access setup, everyone is having to use their own local machine's processor to run everything - not just drag all the data over the network to the local machine to crunch the numbers, but dragging the front-end screens and all over the network too, not to mention your one poor 'ole fe file having to keep everything coordinated and separated among all the users.... that takes processing, memory, and hard drive read/write time! If your setup is like that, then push a button and go to lunch.... and hope the process is finished by the time you return! The performance issues you are experiencing (if any) are more likely due to this issue. I doubt any change in a ADO or DAO query could ever make up for this.
Here in my lab, I have a "Using Access with SQL Server" demo/testing app that I use to test ideas and compare the different technologies against each other. In my testing with SQL Server, I found the Access ODBC pass-thru query to be much more efficient and faster than using an ADO recordset, especially for loading forms with records, but you don't notice the difference unless dealing with a lot of records or the machine is under load.
Just a few basics to think about...
First, a network is an automatic performance drag on a scale of 10x, so you want to keep traffic flow to a minimum. The BEST performance of a straight Access setup over a network is realized when everyone has their own copy of the fe on their machine and just pulling the table data over the network. Keeping the fe on the server and having everyone run it from there is a REAL performance drag.
With a pure Access setup, everyone is having to use their own local machine's processor to run everything - not just drag all the data over the network to the local machine to crunch the numbers, but dragging the front-end screens and all over the network too, not to mention your one poor 'ole fe file having to keep everything coordinated and separated among all the users.... that takes processing, memory, and hard drive read/write time! If your setup is like that, then push a button and go to lunch.... and hope the process is finished by the time you return! The performance issues you are experiencing (if any) are more likely due to this issue. I doubt any change in a ADO or DAO query could ever make up for this.
Here in my lab, I have a "Using Access with SQL Server" demo/testing app that I use to test ideas and compare the different technologies against each other. In my testing with SQL Server, I found the Access ODBC pass-thru query to be much more efficient and faster than using an ADO recordset, especially for loading forms with records, but you don't notice the difference unless dealing with a lot of records or the machine is under load.
p.s. Before anyone else says it: pass-thru queries are READ_ONLY, so an ADODB recordset with a client-side cursor would be the way to go if you need edit ability.
p.s.s Are we having fun yet?
p.s.s Are we having fun yet?
When forms are correctly defined to retrieve the minimum number of rows, binding forms to queries of linked tables works fine. The problem with straight conversions of Jet/ACE to ODBC is that typical Access apps rely on local filtering rather than queries that use selection criteria. This dramatically affects performance once the BE is switched to ODBC because it prevents the server from doing the heavy lifting and forces Access to download entire tables from the server to memory on the local PC increasing network overhead which impacts all other users.
ASKER
Thanks to all for the information and input. For the time being I am focusing on the conversion and looking for other areas to improve performance. It sure doesn't seem that I would get much improvement by simply changing the open parameters.
It sure doesn't seem that I would get much improvement by simply changing the open parameters.Depends on how large your tables are and how much useless data you are downloading over and over and over again for every single user.
Is this a straight Access-Only app with Access tables, and if so, are the Access tables in an external file and linked to the front-end (app) Access file, or are the tables NOT linked?
Is there another back-end like SQL Server being used?
Sorry for all the questions. I remember when the use of ADO was all the rage with Access developers long ago, but you don't usually see an "exclusive use of ADO" with local Access tables or linked Access tables. With SQL Server I can understand.....