Solved

Speed up VB code to copy

Posted on 2008-10-16
20
246 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
 

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
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Have you ever wanted to restrict the users input in a textbox to numbers, and while doing that make sure that they can't 'cheat' by pasting in non-numeric text? Of course you can do that with code you write yourself but it's tedious and error-prone …
Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

863 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now