Opening DAO Recordset dbSnapshot vs dbForwardOnly

I have a number of routines in a Access 2003 application I am working on that are purely for data retrieval.  No data editing or revision and the records are read in the order returned.  

The structure and function of these routines is all the same.  They are called interactively from another routine to pull information from a specific record in either a table or query.  In most cases only the first record of the dataset is read to get the information required.  If the dataset is EOF, the information was not found.

Since I'm making changes anyway I want to code these type of routines in the most efficient way.

In these routines, I noticed some cases the developer used
set rs = db.Openrecordset (selectstring, dbOpenSnapshot, dbReadonly)  

and in others the developer uses

set rs = db.Openrecordset (selectstring, dbOpenForwardOnly, dbReadonly)  

My question is:  In this scenario and type of usage which of these options (dbOpenSnapshot or dbOpenForwardOnly) is the most efficient, or is there any real difference?


Here's an example of this type of routine.
NOTE: In the example below I realize that MoveLast and MoveFirst would need to be removed if dbForwardOnly is used but that is easily done and the recordcount will always return 1.  I think those statements are remnants of the original ADO recordset handling.

Example:
Public Sub getInstallPlanInfoFromPropertyID(passedPropertyID As Long, _
                                               returnPAY_AGREE_TYPE As String, _
                                               returnPAY_AGREE_START_DATE As Date, _
                                               returnPAY_AGREE_END_DATE As Date, _
                                               returnPAYMENT_AGREEMENT_TYPE As String)


returnPAY_AGREE_TYPE = cNoRecFound
returnPAYMENT_AGREEMENT_TYPE = cNoRecFound
'
Dim wkPaidToDate As Double
'
selectString = "Select [PAY_AGREE_TYPE], [PAY_AGREE_START_DATE], [PAY_AGREE_END_DATE], [PAYMENT_AGREEMENT_TYPE] " & _
                                                                        " From qryInstallPay_Main_ForCOPDW " & _
                                                                        " Where ([PropertyID] = " & passedPropertyID & " ) "
'
'
Set db = getCurrentDbC
'
Dim rs As DAO.Recordset
Set rs = db.OpenRecordset(selectString, dbOpenSnapshot, dbReadOnly)  
'
If rs.EOF Then
Else
    rs.MoveLast
    rs.MoveFirst
    If rs.RecordCount > 0 Then
        '
        returnPAY_AGREE_TYPE = Nz(rs!PAY_AGREE_TYPE, 0)
        '
        '////old logic below, revised 09/27/16
        'returnPAY_AGREE_START_DATE = Nz(rs!PAY_AGREE_START_DATE, 0)
        'returnPAY_AGREE_END_DATE = Nz(rs!PAY_AGREE_END_DATE, 0)
        '
        ' new logic 09/27/16
        returnPAY_AGREE_START_DATE = Nz(rs!PAY_AGREE_START_DATE, cNoDateFound)    ' return cNoDateFound if Null   09/27/16
        returnPAY_AGREE_END_DATE = Nz(rs!PAY_AGREE_END_DATE, cNoDateFound)        ' return cNoDateFound if Null   09/27/16
        '
        returnPAYMENT_AGREEMENT_TYPE = Nz(rs!PAYMENT_AGREEMENT_TYPE, 0)
        '
    End If
End If
'
rs.Close
Set rs = Nothing
'

End Sub
LVL 1
mlcktmguyAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
<<My question is:  In this scenario and type of usage which of these options (dbOpenSnapshot or dbOpenForwardOnly) is the most efficient, or is there any real difference?>>

  Yes, it does make a difference.

 Let's talk snapshot first; what your asking for is a *static* snapshot of the data.   So JET/ACE is going to cache a copy of every record, then hand you the record set.

  On a small recordset, this doesn't matter, but on a large recordset it can make a big difference.  If your reading forward only, then you really don't want a snapshot unless you want to see the data as it was at a specific point in time.

 Forward only gives you a "lighter" cursor.  There's a bunch of things the database engine doesn't have to do if your not going to move forwards/backwards through a recordset.

Jim.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
BTW,

This:

    rs.MoveLast
    rs.MoveFirst
    If rs.RecordCount > 0 Then

 is a real performance drain.   As you already noticed, .RecordCount will be 1 or more if there are records, 0 if none.  So if your just trying to determine if there are records, there is no reason to do a .MoveLast then first.  You can easily use BOF and EOF as well.

 Also note that ADO is dfferent; it will hand you the correct record count right off.

Jim
0
PatHartmanCommented:
Of course, the best solution is to not use a cursor at all.  Or,since you are only ever using the "first" record, change the query to return the Top 1 rows.  Then if the recordset is not at EOF, you have a record.

Running .MoveLast forces Access to bring the ENTIRE recordset down from the server.  What is the point if you are only ever using one record?
0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

mlcktmguyAuthor Commented:
Thanks you confirmed what I was thinking
0
mlcktmguyAuthor Commented:
Pat: Great idea and sorry, I had already awarded points when your post showed up.  

Could you explain how to do this without a cursor, even though I unintentionally stiffed you on points?
0
PatHartmanCommented:
If this is an attempt to update a record, just run an update query.  Otherwise modify the query as I suggested so it only ever returns a single row.
0
mlcktmguyAuthor Commented:
Thanks Pat, no updating in these just data retrieval and usually only the first record is needed.  In most of these queries only one matching record or zero records if there is no match will be returned

I'll make the revision to Top 1, dbOpenForwardOnly.
0
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
<<Otherwise modify the query as I suggested so it only ever returns a single row.>>

   He had a WHERE on that SQL, so I would assume it was calling a single row already, but had the flexibility to get more if need be, which is the way I would have left it.  Not sure what all the logic needs to be though.

jim.
0
PatHartmanCommented:
By the description, he was only using the first record and didn't seem to be giving any error messages if there were more than one so I was attempting to remove the ambiguity.  The code was probably copied from some other place and that is what caused the confusion.
0
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
<< The code was probably copied from some other place and that is what caused the confusion.>>

 Yes, it looks like it has morphed over time.

Jim.
0
mlcktmguyAuthor Commented:
You are both right, there have been many fingers in this pie, each with their own techniques.

I noticed something else but I'll post another question for it.  As always ,thanks for your help in this one.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.