nelsonje
asked on
Speed up VB code to copy
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
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
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
ASKER
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
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
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.
Try creating the recordset with just the Primary key, !bill. This way you don't load too much into memory.
ASKER
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
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.
Anyway, just create the recordset with the on column, then run the script, and see if you get a performance boost.
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.
Anyway, just create the recordset with the --->>>one <<<--- column, then run the script, and see if you get a performance boost.
ASKER
Run the script where in SQL? Is that right, and if so the VB script or the SQL script listed from PAT??
What is the underlying database? And does it support batch update?
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>")
Set rstThis = CurrentDb.OpenRecordset ("Select Bill from <TableName>")
ASKER
What is this code going to do??
instead of having a recordset with lots of fields and data, there will only be 1 column of data.
1. Explicitly set the type of recordset. Note that table access is faster than dynaset.
Example:
Set rstThis = CurrentDb.OpenRecordset("W eekly Comments", dbOpenTable)
Set rstThis = CurrentDb.OpenRecordset("W eekly Comments", dbOpenDynaset)
2. Do your updates within one or more transactions.
Example:
DBEngine.Workspaces(0).Beg inTrans
With rstThis
...
End If
DBEngine.Workspaces(0).Com mitTrans
.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.
Example:
Set rstThis = CurrentDb.OpenRecordset("W
Set rstThis = CurrentDb.OpenRecordset("W
2. Do your updates within one or more transactions.
Example:
DBEngine.Workspaces(0).Beg
With rstThis
...
End If
DBEngine.Workspaces(0).Com
.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
oops. Should be:
Set rstThis = Currentdb.OpenRecordset("W eekly Comments", dbOpenTable) 'or dbOpenDynaset if a query
Set rstThis = Currentdb.OpenRecordset("W
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. :-(
Here's a pertinent question...
7. Is the [Weekly Comments] table bound to any object(s) when you are trying to do this update?
7. Is the [Weekly Comments] table bound to any object(s) when you are trying to do this update?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
@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.
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.
@ aikimark
I definately think you rock!!! OMG, WOW, Awesome, And Thanks... I learned a few things. Too bad i can't give you points.
I definately think you rock!!! OMG, WOW, Awesome, And Thanks... I learned a few things. Too bad i can't give you points.
@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.
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.
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