Slow Table Update

dtaylor · Mar 3, 2004

Hello All - to give a little primer on what i am doing. I am building an app that takes a weekly extract and populates a table for analysis. I am tracking my data by week. I have 3 table that are in question here:
1. My main table holding historical data. Has a field for contract id(indexed to accept no dups) and one field for each fiscal week of the year.
2. Temp table that is populated with weekly import. Has contract id, fiscal week and sales.
3. A table that holds one record for the current fiscal week.

My main table holds all the contracts i am tracking for my division (roughly 12m lines). I have a field for each fiscal week (1,2,3,4,ect.).
The temp table holds all contracts for one week being uploaded. Usually will be about 6m contracts that had sales the previous week.
The current fiscal week is extracted from the temp table and placed in the third table. This record is used to tell the routine which field to upload data to.

This only a piece of my overal app but I cannot continue until get this down since my data analysis/retrival is dependent on my main table.

I have written a piece of code that works the way I want it to but it takes about 25mins to run. A buddy of mine suggested SQL Batch Update, but I have barely read about this and never tried.

If someone can take a look at this code and maybe have a better solution I would be much appreciated.

Thanks!

Private Const tblPMSales As String = "tblPriceMethods_Sales"
Private Const tblPMImport As String = "tblPriceMethodsTemp"
Private Const tblWeek As String = "tblWeek"
Private Const fldWk As String = "Week"
Private Const fldContract As String = "Contract ID"
Private Const fldPMImportSalse As String = "Total Sales"

Sub PMUpdate()
Dim rstPM As ADODB.Recordset
Dim rstPMImp As ADODB.Recordset
Dim rstWK As ADODB.Recordset
Dim i As String

Set rstPM = New ADODB.Recordset
Set rstPMImp = New ADODB.Recordset
Set rstWK = New ADODB.Recordset

rstPM.CursorLocation = adUseServer

rstPM.Open tblPMSales, CurrentProject.Connection, adOpenKeyset, adLockOptimistic, adCmdTableDirect 'Main Table
rstWK.Open tblWeek, CurrentProject.Connection, adOpenForwardOnly ' Table holding current fiscal week
rstPMImp.Open tblPMImport, CurrentProject.Connection, adOpenForwardOnly ' Table holding uploaded data

Do While Not rstPMImp.EOF
rstPM.MoveFirst
Do While Not rstPM.EOF
i = rstWK.Fields(fldWk)
If rstPM.Fields(fldContract) = rstPMImp.Fields(fldContract) Then
rstPM.Fields(i) = rstPMImp.Fields(fldPMImportSalse)
End If

rstPM.MoveNext
Loop
rstPMImp.MoveNext
Loop
ExitHere:
On Error Resume Next
rstWK.Close
rstPMImp.Close
rstPM.Update
rstPM.Close

Set rstWK = Nothing
Set rstPM = Nothing
Set rstPMImp = Nothing

Exit Sub
End Sub

dk · Mar 4, 2004

Hi,

This isn't going to make a huge difference but it seems as though you're retrieving the week in every iteration of both loops. You might be better off getting it once and then closing the recordset. I can think of a few other things which might have a more significant impact. I'll post back later.

Code:

Sub PMUpdate()
    Dim rstPM As ADODB.Recordset
    Dim rstPMImp As ADODB.Recordset
    Dim rstWK As ADODB.Recordset
    Dim i As String

    Set rstPM = New ADODB.Recordset
    Set rstPMImp = New ADODB.Recordset
    Set rstWK = New ADODB.Recordset

    rstPM.CursorLocation = adUseServer

    rstPM.Open tblPMSales, CurrentProject.Connection, adOpenKeyset, adLockOptimistic, adCmdTableDirect    'Main Table
    rstWK.Open tblWeek, CurrentProject.Connection, adOpenForwardOnly    ' Table holding current fiscal week
    rstPMImp.Open tblPMImport, CurrentProject.Connection, adOpenForwardOnly    ' Table holding uploaded data

    i = rstWK.Fields(fldWk)
    rstWK.Close


    Do While Not rstPMImp.EOF
        rstPM.MoveFirst
        Do While Not rstPM.EOF
            If rstPM.Fields(fldContract) = rstPMImp.Fields(fldContract) Then
                rstPM.Fields(i) = rstPMImp.Fields(fldPMImportSalse)
            End If

            rstPM.MoveNext
        Loop
        rstPMImp.MoveNext
    Loop
ExitHere:
    On Error Resume Next
    rstWK.Close
    rstPMImp.Close
    rstPM.Update
    rstPM.Close

    Set rstWK = Nothing
    Set rstPM = Nothing
    Set rstPMImp = Nothing

    Exit Sub
End Sub

dtaylor · Mar 4, 2004

Dan - Thank You for posting a reply. I have seen many of your posts and respect your opion greatly.

Yea your right putting the field variable in the loop is the wrong thing to do - my (i) varaible is a variable of one recordset and no need to be looped. this record will change with each upload, this is why i made it a variable so there would be no user intervention.
And then I left tblWeek open while the process on the main table took place.

Once again Thanks for your info and pointing out these out for me.

Dan

mdmilner · Mar 4, 2004

I think what he's suggesting is to use SQL to do the update with a single parameter to determine what to update. Yes, this would run far far faster.

Try:
Where i equals the value of the contents of tblWeek.Week

Code:

strSQL = "UPDATE tblPMSales SET tblPMSales.i = tblPMInput.fldPMImportSalse "
strSQL = strSQL & "WHERE tblPMSales.fldContract = tblPMInput.fldContract"

DoCmd.RunSQL strSQL

And because I love DAO - a DAO rewrite (optional)

Code:

Sub PMUpdate()
Dim dbs As DAO.Database
Dim rsWk as DAO.Recordset
Dim strSQL,i   As String

Set dbs = CurrentDB()
strSQL = "SELECT * from tblWk"
Set rsWk = dbs.OpenRecordset(strSQL, dbOpenSnapshot)
i = rsWK(0)  ' Explict Reference to the only field in the table (first)

strSQL = "UPDATE tblPMSales SET tblPMSales.i = tblPMInput.fldPMImportSalse "
strSQL = strSQL & "WHERE tblPMSales.fldContract = tblPMInput.fldContract"

DoCmd.RunSQL strSQL

Set rsWK = Nothing
Set dbs = Nothing
End Sub

With luck I didn't typo any of the field names.
You should look at QBE = Query By Example Wizard interface that you get when you click on 'New' under the queries tab in Access. It will basically build the above for you.

CT Witter · Mar 4, 2004

Your Friend may have meant to use transactions in access.

If you have a query, other than a simple select query, such as an update or append query that takes too long to run then the following technique will increase the speed of your query from minutes to a few seconds. The reason this works is that the record set operations are written to memory (transaction buffer) rather than directly to the hard disk.

I've built the transaction process onto the previous post. The key parts of the Transaction processing are bolded.

Sub PMUpdate()
Dim dbs As DAO.Database
Dim rsWk as DAO.Recordset
Dim strSQL,i As String
Dim wksp As DAO.Workspace

Set dbs = CurrentDB()
Set wksp = DBEngine.Workspaces(0) ' set up a transaction buffer
wksp.BeginTrans ' all record set changes are buffered after this
On Error GoTo roll0

strSQL = "SELECT * from tblWk"
Set rsWk = dbs.OpenRecordset(strSQL, dbOpenSnapshot)
i = rsWK(0) ' Explict Reference to the only field in the table (first)

strSQL = "UPDATE tblPMSales SET tblPMSales.i = tblPMInput.fldPMImportSalse "
strSQL = strSQL & "WHERE tblPMSales.fldContract = tblPMInput.fldContract"

DoCmd.RunSQL strSQL

wksp.CommitTrans
GoTo finish_it
roll0:
If Err.Number = 3022 Then

On Error GoTo roll0
Resume check_next
End If
MsgBox Err.Number & " " & Err.Description
wksp.Rollback ' cancel everything if unexpected error
finish_it:
Set rsWK = Nothing
Set dbs = Nothing
End Sub

HTH,
CT

mdmilner · Mar 4, 2004

Thanks CT - Yours is better, particularly given the size (12 million? 6 million? records). I've avoided any serious attempt to learn how to use Transactions properly under the misperception that it was ADO only and by telling myself that I didn't need it for most of my current projects.

I'm guessing the answer is "always" but do you follow any specific rule-of-thumb over whether to use transactions or not (to use them)?

Mike

dtaylor · Mar 4, 2004

Thanks All for the replys! I am in meetings all morning but this afternoon I will review and implement the changes.... All are great suggestions! My code is working and the changes Dan suggested will help. But I am very intrigued by the transactions sql statments suggested - Thanks CT!

mdmilner - thanks for the suggestion on the Update SQL statement. Before I wrote the code I posted I was going in circles trying to get the correct syntax for an UPDATE query, which was my first train of thought!

How will having a ref to both Mircosoft ActiveX Data Objects and Microsoft DAO loaded affect my app? I do need the ADO since the rest of my app is coded in ADO. Although I do perfer to use DAO especially when creating tables/queries. So much easier than working with views in ADO

.But I am trying to learn more about ADO so I do use when possible.

And my apologies if I was not clear - 6m and 12m are not a million records (i would be dead in the water). I was using m as the roman numeral for 1000.

Dan - you said you may have some other suggestions. I am very interested in reviewing them.

God I love this site!

Dan

CT Witter · Mar 4, 2004

Mike-

I don't really have any "rules" regarding it. Most of the time it comes down to how deeply "nested" the transactions are. You can have multiple transactions nested together, but many times they take a lot of work to get right. With a relatively straight logic path and high number of records involved, this example is perfect for transactions.

You also must be aware of the amount of ram and resources required as well. I've had good luck with up to about 20 million records on a machine with 1024 MB of ram. Of course with 20 million that is a one-time event, not a regular processing scenario.

I would recommend taking a look at some of your existing code and adding transactions to understand how they work.

Below is some sample code with the execution times. Even though this timer isn't exact should give you good idea. I ran each test 5 times and averaged the results. I deleted the table data and compacted after every run to reset the database.

Pretty dramatic results when using transactions! Look at the 5 Million 2:15 minutes using transactions versus 7:41 minutes!

Here's the code, just uncomment to run with transactions.

Code:

Option Compare Database
Option Explicit
Private Declare Function adh_apiGetTime Lib "winmm.dll" Alias "timeGetTime" () As Long
Dim lngStartTime As Long
Function adhEndTimer()
adhEndTimer = adh_apiGetTime() - lngStartTime
End Function
Function adhStartTimer()
lngStartTime = adh_apiGetTime()
End Function

Private Sub cmdTran_Click()
adhStartTimer
Dim wks As Workspace
Dim dbs As Database
Dim rst As Recordset
Dim I As Long

Set wks = DBEngine.Workspaces(0)
Set dbs = CurrentDb()
Set rst = dbs.OpenRecordset("table1")

'wks.BeginTrans
With rst
For I = 1 To 5000000

.AddNew
!AutoID = I
!Text = "Text No. " & I
.Update
Next I
End With

'If MsgBox("Save all changes?", vbYesNo) = vbYes Then
'wks.CommitTrans
'Else
'wks.Rollback
'End If

rst.Close
dbs.Close

Dim Test1a As Long
Test1a = adhEndTimer()
Test1a = Test1a / 1000
MsgBox "Time was: " & Test1a & " seconds", vbOKCancel, "Time"
End Sub

Below is my data table, I couldn't post the graph, but when graphed it is very impressive

Book1.xls

A

B

C

D

1

NumberofRecords

Time(sec)-WithoutTransactions

Time(sec)-WithTransactions

PercentFaster

2

10,000

1

0%

3

50,000

3

2

50%

4

100,000

6

4

50%

5

500,000

38

14

171%

6

1,000,000

86

37

132%

7

5,000,000

461

136

239%

Sheet1

*Edit: Just for kicks I did 10 million records: 947 secs without; 265 secs with!

mdmilner · Mar 4, 2004

About ADO/DAO.
No Impact on having both loaded, but, just like yours, I explicitly declared my objects as DAO. By default, Access works down the list of references from top to bottom and uses the first match for the applicable object type. Only if you explicitly reference which one will you always avoid errors.

Also, despite what some might suggest, DAO/ADO are about even in terms of how quickly they handle a lot of stuff where they have overlapping capabilities, for example, working with Office Applications.

My own thoughts (pre this discussion) for Transactions were that they should be used when you're executing multiple SQL statements and it's critical that all succeed or you risk your database integrity...also, particularly when you're working with tables outside the immediate application (Access) as you can't always rely on remote/networked databases to be available 100% of the time.

About quantity - I think you'll see a huge performance increase solely because of using SQL. SQL gets fun once you realize such a short and simple statement will iterate through the entire database, whether that's 10 or 10,000 records.

Mike

dk · Mar 4, 2004

Edit : Getting slow in my old age

dtaylor said:
How will having a ref to both Mircosoft ActiveX Data Objects and Microsoft DAO loaded affect my app? I do need the ADO since the rest of my app is coded in ADO. Although I do perfer to use DAO especially when creating tables/queries. So much easier than working with views in ADO .But I am trying to learn more about ADO so I do use when possible.

Dan,

You can use DAO and ADO in the same project no problems. However, you should be aware of this. If you declare a variable as Recordset e.g.

Dim RS As Recordset

the variable might be a DAO recordset or an ADO recordset. It depends on what appears first in the references list (you can change a library's priority). To ensure that these potential problems don't occur you should explicitly declare your variables using the library name and class name e.g.

Dim adoRS As ADODB.Recordset
Dim daoRS As DAO.Recordset

dtaylor said:
Dan - you said you may have some other suggestions. I am very interested in reviewing them.

I was thinking along the lines of what the other two guys wrote. However, it looks like CT Witter and mdmilner have given you some great ideas. I didn't even think of know about the speed difference of transactions so

to CT. I'd be interested to see how much of an improvement you get. I think you'll be very pleased.

Slow Table Update

dtaylor

Active Member

Excel Facts

dk

MrExcel MVP

dtaylor

Active Member

mdmilner

Well-known Member

CT Witter

MrExcel MVP

mdmilner

Well-known Member

dtaylor

Active Member

CT Witter

MrExcel MVP

mdmilner

Well-known Member

dk

MrExcel MVP

Similar threads

Forum statistics

Share this page

Slow Table Update

Active Member

Excel Facts

MrExcel MVP

Active Member

Well-known Member

MrExcel MVP

Well-known Member

Active Member

MrExcel MVP

Well-known Member

MrExcel MVP

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock