Action queries vs looping through recordsets?

[spenzer]

I have these two sample snippets that practically accomplishes the same thing; I haven't noticed any difference in speed whatsoever. Both are fast enough and does the job really well.

What does it do:
Both snippets will perform a reset values to fields: TotalDays, Debit and PayDate on an active transaction.


A. Looping through recordset

Dim db As DAO.Database
Dim rs As DAO.Recordset
                 
            Set db = CurrentDb
            Set rs = db.OpenRecordset _
                ("SELECT * FROM tblReceivables WHERE TransactionID = " & Me.TransactionID & _
                " And Active = -1")
                
                Do Until rs.EOF
                    With rs
                    .Edit
                    !TotalDays = 0
                    !Debit = 0
                    !PayDate = Null
                    .Update
                    .MoveNext
                    End With
                Loop
                    rs.Close
                    Set rs = Nothing
                    db.Close
                    Set db = Nothing

B.Using Update action query

strSQL = ""
        strSQL = strSQL & " UPDATE tblReceivables "
        strSQL = strSQL & " SET TotalDays = 0, Debit = 0, PayDate = Null "
        strSQL = strSQL & " WHERE TransactionID = " & Me.TransactionID & " And Active = -1  "

        CurrentDb.Execute strSQL, dbFailOnError

Question:

If both are equally efficient enough in doing the job, which one in best practice should be prioritized to be used when available?

 

[pbaldy]

The more records that will be affected by the update, the greater the advantage of the SQL method. It would always be my first choice. As a test, take out the criteria and let both update the whole table. Presuming it has a reasonable number of records, I'd expect the SQL to be measurably faster.

 

[sparks80]

Hi,

One of the things I have learnt on this forum is that it is often preferable to create a saved query and pass any necessary parameters, rather than constructing the SQL statement in code. Apparently the latter method is one of the factors that causes significant bloat in databases.

So Method C, would be to create a saved query like say qryResetTransaction

UPDATE tblReceivables SET TotalDays = 0, Debit = 0, PayDate = Null WHERE TransactionID = [GetTransactionID] & " AND Active = -1"

Now you can call the query from your code and pass the transaction ID as a parameter:

Dim qdf AS DAO.QueryDef                 
    Set qdf = CurrentDb.QueryDefs("qryResetTransaction")
    qdf.Parameters("GetTransactionID") = Me!TransactionID
    qdf.Execute

As well as reducing database bloat it often helps to simplify code.

 

[VilaRestal]

Yeah, the SQL update method is almost always faster if it can be done. In small tables they're both so quick you don't notice the difference but on 100,000 records you certainly would.

There are cases where looping through recordsets is better though:

If the formula for deciding which records to edit and/or what to set their fields to depends on aggregate formulas based on large tables. Sometimes by looping through a recordset you can help the processor by doing calculations less often and retaining their values for multiple iterations.

And sometimes the SQL needed would be just so complex it's easier to write and debug by doing it step-by-step in a VBA loop.

 

[spenzer]

The more records that will be affected by the update, the greater the advantage of the SQL method. It would always be my first choice. As a test, take out the criteria and let both update the whole table. Presuming it has a reasonable number of records, I'd expect the SQL to be measurably faster.

Hi!

To test it as you suggested, I took out the criteria and tried to update a table with about 40k records. Viola! You were right looping through recordset took about 3-4seconds while doing it in action query gave the result almost instantly.

This was a terrific finding as I am now thinking of changing all my looping recordset snippets into action queries. I realize that It's not only speedier but it is actually easier to write as well.

Warm thanks to you!

 

[spenzer]

Hi,

One of the things I have learnt on this forum is that it is often preferable to create a saved query and pass any necessary parameters, rather than constructing the SQL statement in code. Apparently the latter method is one of the factors that causes significant bloat in databases.

So Method C, would be to create a saved query like say qryResetTransaction

UPDATE tblReceivables SET TotalDays = 0, Debit = 0, PayDate = Null WHERE TransactionID = [GetTransactionID] & " AND Active = -1"

Now you can call the query from your code and pass the transaction ID as a parameter:

Dim qdf AS DAO.QueryDef                 
    Set qdf = CurrentDb.QueryDefs("qryResetTransaction")
    qdf.Parameters("GetTransactionID") = Me!TransactionID
    qdf.Execute

As well as reducing database bloat it often helps to simplify code.

Thank YOu and well done!

Wow I did not know I still have a method c until you came.
You know what? I will try this for sure if it's as good as action query or better, then who knows i might stick to it.

What I don't understand is the bloating part. You said that doing the other methods will cause my database to bloat faster than usual, but then if i use this saved querydef method; and since it will be saved, then, my object in the navigation pane will increase; because it will add another query object there right? If that's the case, wouldn't this bloat my database more than a temporary recordset or just running an action query which will not be saved in the access navigation pane after the execution?

 

[spenzer]

Yeah, the SQL update method is almost always faster if it can be done. In small tables they're both so quick you don't notice the difference but on 100,000 records you certainly would.

There are cases where looping through recordsets is better though:

If the formula for deciding which records to edit and/or what to set their fields to depends on aggregate formulas based on large tables. Sometimes by looping through a recordset you can help the processor by doing calculations less often and retaining their values for multiple iterations.

And sometimes the SQL needed would be just so complex it's easier to write and debug by doing it step-by-step in a VBA loop.

Hello!

Thank you for your input, and with that, I now realize that there is also a place great for using a loop through recordset method though it is not as efficient as the other method.

And so I think the rule of thumb is, use action queries as much as possible; as long as it can, and loop through a recordset only when condition is too complex for other methods to handle it.

 

[sparks80]

Hi Spenzer,

Quoting the legendary Pat Hartman on this very forum:

The most common causes of db bloat are over-use of temporary tables and over-use of non-querydef SQL. Whenever you code an SQL string outside of a querydef, Access must "bind" that statement EVERY time it is run. This process takes a small amount of time and uses a large amount of workspace that is not recovered until you compact the db. Querydef's are "bound" when they are saved and when the db is compacted. Access saves its execution plan at that time and uses the saved plan at run time.

Also it is worth looking at this URL from the Microsoft Knowledgebase:
http://support.microsoft.com/kb/165823

You can compensate for the increase in database size by regularly compacting, but this is one way to avoid it in the first place.

 

[spenzer]

@sparks80

Thank you for the explanation and very informative link you pasted. I think that I haven't personally noticed the bloating because we perform compact and repair on a daily basis.

But looking at it; I think that ideally, it is better to avoid bloating than getting it and then fixing it again and again. And then there is this; if database is assured to be designed not to bloat easily, I could then maybe schedule compact and repair for once a week only instead of daily basis.