?
Solved

Speed up VB code to copy

Posted on 2008-10-16
20
Medium Priority
?
252 Views
Last Modified: 2013-11-25
I have a VB code that is working to copy recordset not null to null fields, but the code is running very slow any ideas on how to make it faster, this code has to go though about 6000 records and takes about 30 mins to do so... slow?? If you could help I have no ideas on how to speed this up..
Public Function LA()
 
Dim rstThis As Recordset
Dim varField2 As Variant
 
Set rstThis = CurrentDb.OpenRecordset("Weekly Comments") ' or whatever it is called
 
With rstThis
.MoveLast
.MoveFirst
If .RecordCount Then
Do Until .EOF
If Len(Nz(!Bill, vbNullString)) Then
varField2 = !Bill
Else
.Edit
!Bill = varField2
.Update
End If
.MoveNext
Loop
End If
.Close
End With
Set rstThis = Nothing
 
End Function

Open in new window

0
Comment
Question by:nelsonje
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 7
  • 4
  • +2
20 Comments
 
LVL 1

Expert Comment

by:patricka_0377
ID: 22733620
could try via a SQL UPDATE statement

from memory i think the command is CurrentDB.RunSQL (or something like that dont quote me)

then the SQL you want is.

UPDATE table_name
SET column1=value, column2=value2,...
WHERE some_column=some_value
0
 

Expert Comment

by:ScottGutman
ID: 22733622
Could we have some more information?

Does this sum what you are trying to do:
1. If there is data in "!bill" then leave the field alone"
2. If the field is empty, then fill it with a NULL??

Why not create a recordset with only fields that have len(!bill) = 0 and then Update the entire recordset with !bill = vbnullstring?

Also, we might help more if you can give us background on what you need
0
 

Author Comment

by:nelsonje
ID: 22733672
The code looks for Records that are not Null and copies that field to the next Null field, and resets when it comes to a Not null field again and loops.
Example:  BEFORE
K80524A  05-06-2005
                 05-06-2006
                 06-05-2008
K90564A 05-06-2008
                06-05-2007
                07-05-2006

Example: AFTER
K80524A  05-06-2005
K80524A  05-06-2006
K80524A 06-05-2008
K90564A 05-06-2008
K90564A 06-05-2007
K90564A 07-05-2006
0
10 Questions to Ask when Buying Backup Software

Choosing the right backup solution for your organization can be a daunting task. To make the selection process easier, ask solution providers these 10 key questions.

 

Expert Comment

by:ScottGutman
ID: 22733747
Is the recordset the WHOLE table?

Try creating the recordset with just the Primary key, !bill.  This way you don't load too much into memory.
0
 

Author Comment

by:nelsonje
ID: 22733957
I dont have a primary key set up on this table and I dont suggest using one either..I find that it just complicates matters
0
 

Expert Comment

by:ScottGutman
ID: 22734013
then your data may have issues, how can you be certain that the records will always come up in the same order?

Anyway, just create the recordset with the on column, then run the script, and see if you get a performance boost.
0
 

Expert Comment

by:ScottGutman
ID: 22734032
oops typo on should be on

Anyway, just create the recordset with the --->>>one <<<--- column, then run the script, and see if you get a performance boost.
0
 

Author Comment

by:nelsonje
ID: 22734060
Run the script where in SQL? Is that right, and if so the VB script or the SQL script listed from PAT??
0
 
LVL 27

Expert Comment

by:BigRat
ID: 22734137
What is the underlying database? And does it support batch update?
0
 

Expert Comment

by:ScottGutman
ID: 22734223
ok, I am not sure of your environment, but use this for your recordset.  you may get some errors, filelocking that we will have to tackle.

Set rstThis = CurrentDb.OpenRecordset ("Select Bill from <TableName>")
0
 

Author Comment

by:nelsonje
ID: 22740964
What is this code going to do??
0
 

Expert Comment

by:ScottGutman
ID: 22741035
instead of having a recordset with lots of fields and data, there will only be 1 column of data.
0
 
LVL 46

Expert Comment

by:aikimark
ID: 22748255
1. Explicitly set the type of recordset.  Note that table access is faster than dynaset.

Example:
Set rstThis = CurrentDb.OpenRecordset("Weekly Comments", dbOpenTable)
Set rstThis = CurrentDb.OpenRecordset("Weekly Comments", dbOpenDynaset)

2. Do your updates within one or more transactions.
Example:

    DBEngine.Workspaces(0).BeginTrans
    With rstThis

...

        End If
        DBEngine.Workspaces(0).CommitTrans
        .Close
    End With

3. RecordCount is not reliable and is unnecessary overhead in your code.

4. You do not need to .MoveLast and .MoveFirst

5. Your Null test is very inefficient.

See snippet for faster example.

6. Is there a primary key (hopefully and autonumber column) in [Weekly Comments]?  If so, you can solve this problem with a single SQL statement, although it won't be a simple statement as suggested in an earlier comment.
Such a SQL-only solution should be much quicker than a VBA solution, which is interpreted.

Public Function LA()
    Dim rstThis As Recordset
    Dim varField2 As Variant
    
    Set rstThis = db.OpenRecordset("Weekly Comments", dbOpenTable)  'or dbOpenDynaset if a query
 
    DBEngine.Workspaces(0).BeginTrans
    With rstThis
        Do Until .EOF
            If IsNull(!Bill) Then
                .Edit
                    !Bill = varField2
                .Update
            Else
                varField2 = !Bill
            End If
            .MoveNext
        Loop
        DBEngine.Workspaces(0).CommitTrans
        .Close
    End With
    Set rstThis = Nothing
End Function

Open in new window

0
 
LVL 46

Expert Comment

by:aikimark
ID: 22748264
oops.  Should be:

    Set rstThis = Currentdb.OpenRecordset("Weekly Comments", dbOpenTable)  'or dbOpenDynaset if a query
 
0
 
LVL 46

Expert Comment

by:aikimark
ID: 22752935
6. *update* I've attempted to get a good performing query without success.  I expanded the configuration to use several queries and a temporary table.  It appears that the Jet engine will not join two tables (or sets of data) efficiently on a BETWEEN condition. :-(
0
 
LVL 46

Expert Comment

by:aikimark
ID: 22766607
Here's a pertinent question...
7. Is the [Weekly Comments] table bound to any object(s) when you are trying to do this update?
0
 
LVL 46

Accepted Solution

by:
aikimark earned 2000 total points
ID: 22802909
6. *UPDATE 2* Although not performing as fast as a single recordset-updating pass, I have been able to solve the problem reasonably fast using UPDATE query, avoiding the Between operator in the Where clause.  

Testbed Profile:
  Number of groups: 6002 (the two supplied in this question and 6000 randomly generated)
  Number of Null Bill rows/group: 3-10 (randomly generated)
  Number of Null Bill rows (total): 43k-45k (approx observed)
  Performance difference:
    Recordset iteration: 2.4 seconds
    Query iteration within transaction: 9.1 seconds
    Query iteration without transaction: 20-25 seconds
  PC speed: 800 MHz
  PC memory: 512 MB

NOTES:
* This assumes that there is an autonumber ID column on the [Weekly Comments] table.

* The overview of this approach to the updating is that one Null Bill row within each 'group' will be updated in each iteration.  Since the randomly generated groups had between 3 and 10 items, you can see the decreasing number of rows updated with each iteration.

* There were two sets of queries that gave similar results.  One using a query that returned those rows with Null Bill values, and the other just using the [Weekly Comments] table (self joined).

* I tried a version of the LA_Update_Query_Loop function that used dynamic SQL instead of the query definition.  It performed only slightly slower than the querydef sets.

* The big difference = TRANSACTIONS!!! (duh)

[NullBills] Query
_________________
SELECT [Weekly Comments].ID, [Weekly Comments].Bill
FROM [Weekly Comments]
WHERE ((([Weekly Comments].Bill) Is Null));
 
[UpdateBill_NullBills] Query - requires the [NullBills] query
_____________________________________________________________
UPDATE [Weekly Comments], NullBills SET NullBills.Bill = [Weekly Comments].[bill]
WHERE ((([Weekly Comments].Bill) Is Not Null) AND ((NullBills.ID)=[Weekly Comments].[id]+1));
 
[UpdateBill_WC] Query
_____________________
UPDATE [Weekly Comments], [Weekly Comments] AS WC_Null SET WC_Null.Bill = [Weekly Comments].[bill]
WHERE (((WC_Null.Bill) Is Null) AND (([Weekly Comments].Bill) Is Not Null) AND ((WC_Null.ID)=[Weekly Comments].[id]+1));
 
================================================
Public Sub LA_Update_Query_Loop(UpdQryname As String, boolTrans As Boolean)
    Dim qd As QueryDef
    Dim db As Database
    Dim sngStart As Single
    Dim sngQStart As Single
    Dim strTransStatus As String
    Set db = CurrentDb
    Set qd = db.QueryDefs(UpdQryname)
    If boolTrans Then DBEngine.Workspaces(0).BeginTrans
    sngStart = Timer
    Do
        sngQStart = Timer
        qd.Execute
        Debug.Print qd.RecordsAffected, Timer - sngQStart
    Loop Until qd.RecordsAffected = 0
    If boolTrans Then DBEngine.Workspaces(0).CommitTrans
    If boolTrans Then
        strTransStatus = "within"
    Else
        strTransStatus = "without"
    End If
    Debug.Print "_________________________"; vbCrLf; , Timer - sngStart & "  seconds -- Finished query loop updating (" & UpdQryname & ") " & strTransStatus & " trans "
End Sub
 
=======================================
Testbed Results:
LA_Update_Query_Loop "UpdateBill_NullBills",True
 6002          0.8203125 
 6002          0.6796875 
 6000          0.7109375 
 5238          0.7910156 
 4508          0.8125 
 3794          0.8515625 
 3054          0.8613281 
 2297          0.890625 
 1526          0.8925781 
 754           0.9003906 
 0             0.4804688 
_________________________
               9.083984  seconds -- Finished query loop updating (UpdateBill_NullBills) within trans 
 
LA_Update_Query_Loop "UpdateBill_NullBills",false
 6002          0.8222656 
 6002          3.134766 
 6000          3.244141 
 5266          3.103516 
 4519          2.84375 
 3796          2.625 
 3057          2.423828 
 2293          2.171875 
 1538          1.914063 
 767           1.611328 
 0             0.7109375 
_________________________
              24.61523  seconds -- Finished query loop updating (UpdateBill_NullBills) without trans 
 
LA_Update_Query_Loop "UpdateBill_WC",True
 6002          0.8105469 
 6002          0.6621094 
 6000          0.7304688 
 5216          0.7714844 
 4483          0.8300781 
 3739          0.8398438 
 2979          0.8925781 
 2210          0.9003906 
 1455          0.9003906 
 689           0.8828125 
 0             0.5 
_________________________
               9.052734  seconds -- Finished query loop updating (UpdateBill_WC) within trans 
 
LA_Update_Query_Loop "UpdateBill_WC",false
 6002          0.8222656 
 6002          3.365234 
 6000          3.34375 
 5195          3.105469 
 4443          2.923828 
 3741          2.693359 
 2994          2.455078 
 2224          2.181641 
 1436          1.851563 
 736           1.613281 
 0             0.4785156 
_________________________
              24.875  seconds -- Finished query loop updating (UpdateBill_WC) without trans

Open in new window

0
 
LVL 46

Expert Comment

by:aikimark
ID: 22802978
@nelsonje

We haven't heard from you in a week.  Are you still participating in this question?  Have we already solved this question for you and just don't know it yet?

We've shown you tweaks to your posted code that run in 2.4 seconds on my PC.  This should be enough improvement from the 30 minutes of your code to warrant some response from you, such as "OMG" or "OMG!" or "Wow! You guys rock." or "Awesome" or "Thanks" or "I'll try that and get back to you" or "It still performs poorly on my PC"

If you are working on this, please let us know.  If you are still having performance problems, some of our questions will give us enough information to help you beyond the tweak.
0
 

Expert Comment

by:ScottGutman
ID: 22810189
@ aikimark

I definately think you rock!!! OMG, WOW, Awesome, And Thanks...  I learned a few things.  Too bad i can't give you points.
0
 
LVL 46

Expert Comment

by:aikimark
ID: 22833346
@nelsonje

Thanks for the points.  Glad I could help.  It was an interesting problem that revealed a slow aspect of the Jet database engine.

Out of curiosity, did you use the tweaked recordset updating code or the iterative updating query?  I assume both are several orders faster than your (current) 30 minutes.
0

Featured Post

Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
Suggested Courses
Course of the Month13 days, 17 hours left to enroll

801 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question