Solved

Speed up VB code to copy

Posted on 2008-10-16
20
248 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
  • 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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 

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 45

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 45

Expert Comment

by:aikimark
ID: 22748264
oops.  Should be:

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

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 45

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 45

Accepted Solution

by:
aikimark earned 500 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 45

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 45

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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

Enums (shorthand for ‘enumerations’) are not often used by programmers but they can be quite valuable when they are.  What are they? An Enum is just a type of variable like a string or an Integer, but in this case one that you create that contains…
Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

808 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