troubleshooting Question

Improving ADO perofmance

Avatar of mlcktmguy
mlcktmguyFlag for United States of America asked on
Microsoft Access
12 Comments1 Solution84 ViewsLast Modified:
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:
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 Function

I 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 ///////
is replaced by

Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset
rs.Open selectString, CurrentProject.Connection, adOpenKeyset, adLockOptimistic

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, adOpenKeyset, adLockOptimistic

to

rs.Open selectString, CurrentProject.Connection, adOpenForwardOnly, adLockReadOnly

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
Join our community to see this answer!
Unlock 1 Answer and 12 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 12 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros