Solved

Speed up VB code to copy

Posted on 2008-10-16
20
245 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
What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 

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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
I was working on a PowerPoint add-in the other day and a client asked me "can you implement a feature which processes a chart when it's pasted into a slide from another deck?". It got me wondering how to hook into built-in ribbon events in Office.
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
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…

705 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

16 Experts available now in Live!

Get 1:1 Help Now