Anyway to speed this procedure up?

[josekreif]

I assume this has something to do with the DAOs... It takes 5-10 Seconds to build a year report summary for each customer. I have 119 Customers. I tried to wait for the whole thing and finally quit after about 6-7 minutes. I usually don't have any issues programming with VBA, aside from limitation here and there.

 Dim db As DAO.Database
  Dim tbl As DAO.Recordset, Cus As DAO.Recordset, Col As DAO.Recordset, Del As DAO.Recordset
    Set db = CurrentDb()
    db.Execute ("DELETE * from BuyVsPay") ' clear the table. We will rebuild it each time
    Set tbl = db.OpenRecordset("SELECT * FROM BuyVsPay")
    Set cu = db.OpenRecordset("SELECT * FROM Customer")
  
  Dim custLookup As String ' holds the customer name so we can search for it. Might need to change to use ID in future
  Dim monthNum As Integer ' Counter used to pull in the months in order.
  Dim x As Integer
 
  
  If Not cu.EOF Then
    cu.MoveFirst
    
    Do While Not cu.EOF
      monthNum = 1
      custLookup = cu("Customer Name")
      
      Do While monthNum <= 12 ' Get 12 months of data, whether they exist or not.
        Set Col = db.OpenRecordset("SELECT * FROM MonthTotalsCollection where [Custom] ='" _
                                    & custLookup & "' and [Month] = " & monthNum) ' select total collection records
        
        Set Del = db.OpenRecordset("SELECT * FROM MonthTotalsDelivery where [Customer] ='" _
                                    & custLookup & "' and [Month] = " & monthNum) ' select total delivery records
      
        tbl.AddNew ' create the record in the report
        
          If monthNum < 10 Then ' this will add a zero onto month if < 10
            tbl("index") = custLookup & CStr(Year(Date)) & "0" & CStr(monthNum) 'JohnDoe201508
          Else
            tbl("index") = custLookup & CStr(Year(Date)) & CStr(monthNum)  'JohnDoe201512
          End If ' month < 10
          
          tbl("Customer") = custLookup ' Give it the customer
          tbl("CustID") = cu("Customer Number")
          tbl("theMonth") = monthNum   ' give it the month.
          
          ' EOF codes for queries
          If Col.EOF Then
            tbl("USD_Collect") = 0
            tbl("RMB_Collect") = 0
          Else
            Col.MoveFirst
            tbl("USD_Collect") = Nz(Col("SumOfCollection in USD"), 0)
            tbl("RMB_Collect") = Nz(Col("SumOfCollection in RMB"), 0)
          End If
          
          If Del.EOF Then
            tbl("USD_Bill") = 0
            tbl("RMB_Bill") = 0
          Else
            Del.MoveFirst
            tbl("USD_Bill") = Nz(Del("SumOfBilled USD"), 0)
            tbl("RMB_Bill") = Nz(Del("SumOfinclude VA Extended Value"), 0)
          End If
          '''''''''''''''''''''''''''''''''''
        
        
        tbl.Update ' SAVE THE NEW RECORD WE JUST CREATED
      
        ' THAT'S IT. MOVE TO NEXT MONTH! :)
        monthNum = monthNum + 1 ' go to the next month
        x = x + 1
        '=====================
        'RESET FOR NEXT ROUND
        Set Col = Nothing
        Set Del = Nothing
        '====================
      Loop ' month <> 12
      
      cu.MoveNext ' move onto the next customer
      
    Loop ' cu EOF
  
  End If ' cu EOF
  
  MsgBox x & " Records Added"


  
  
End Sub

EDIT: upon posting this, I may have thought of a solution

I can get rid of the multiple DAO calls and call it once per customer then use
if Col/Del("Month") = monthnum then x else y

However, I look forward to opinions

[ranman256]

yes, stop using code, and use update queries only.

[josekreif]

yes, stop using code, and use update queries only.

I generally stick to just query builder whenever possible, because it is sometimes easier to maintain. In this case, the extensiveness of what I'm doing will require some side coding. I have no problem programming, it's what I went to college for. (It's also more fun than SQL -IMMHO)

I will put some more effort into my work to see if I can make it more efficient using different logic strategies.

Thank you for the reply

[June7]

I don't know if would be faster than adding record to the tbl recordset object, but CurrentDb.Execute will write record directly to table

[ItsMe]

Instead of using SELECT * you should define which columns you need, ie Select [Customer Name] FROM Customer

Since you only need to get a list of customers, you do not need a dynaset, so you could use the following type and option ...
Set cu = db.OpenRecordset("SELECT [Customer Name] FROM Customer", dbOpenSnapshot, dbForwardOnly)

Since it seems you are using a local temp table here
db.Execute ("DELETE * from BuyVsPay")

You might try using the following to do your appends ...
Set tbl = db.OpenRecordset("SELECT * FROM BuyVsPay", dbOpenTable)

Instead of
Do While Not cu.EOF

You could just
While Not cu.EOF

and then replace Loop with Wend
While something
...
Wend

Instead of
Do While monthNum <= 12

You could
Dim monthNum As Integer
For monthNum = 1 To 12
...
Next monthNum

Also, instead of using an SQL string in your recordsets, you can assign the string to a DAO querydef's SQL property and then use the query object name defined by the QueryDef in your db.Execute and your db.OpenRecordset("NameOfQueryObject" ...

[josekreif]

Good suggestions Here. I made some changes prior and seem to be getting somewhere. I changed the flow of the program and got it down to 1-2 minutes, where it used to be somewhere over 7 minutes. I will try a few more things now. Thanks.

[ItsMe]

Another thing, for your local temp table, if you can avoid defining a Primary Key and indexing any of the columns, do that. Not sure what the purpose of the temp table is, but if you are not running SELECT queries with WHERE criteria on it, I don't see why you would need to index any of the columns or even define a PK.

[josekreif]

Doesn't help I'm testing this on the deployment computer, it's kind of a slow PC. I need to try and further reduce the calls to "Set Col/Del".

I reduced from calling it 1400+ times, to now calling once per customer. So about 120 times now.

reducing it further would mean calling it 1 time. Before, I called it 12 times per customer. I was able to rework the logic to call it once per customer. So I guess now I'll shoot for just once for all customers together.

Using an extra variable to keep track of what customer we're on and something like "Group by [Customer Name], [month] asc


If that doesn't get me something reasonable, like 5 seconds or less, I'll look for alternatives.

[ssanfu]

"Index" and "Month" are reserved words in Access and shouldn't be used for object names.

Don't set the recordsets "Col" and Del" to nothing. You don't want to destroy the declaration, just close the recordset.

    Dim db As DAO.Database
    Dim tbl As DAO.Recordset, Cus As DAO.Recordset, Col As DAO.Recordset, Del As DAO.Recordset
    Dim custLookup As String    ' holds the customer name so we can search for it. Might need to change to use ID in future
    Dim monthNum As Integer    ' Counter used to pull in the months in order.
    Dim x As Integer


    Set db = CurrentDb()
    db.Execute ("DELETE * from BuyVsPay")    ' clear the table. We will rebuild it each time
    Set tbl = db.OpenRecordset("SELECT * FROM BuyVsPay")
    Set cu = db.OpenRecordset("SELECT * FROM Customer")



    If Not cu.EOF Then
        cu.MoveFirst

        Do While Not cu.EOF
            monthNum = 1
            custLookup = cu("Customer Name")

            Do While monthNum <= 12    ' Get 12 months of data, whether they exist or not.
                Set Col = db.OpenRecordset("SELECT * FROM MonthTotalsCollection where [Custom] ='" _  '<<--Shouldn't this be "Customer"??
                                           & custLookup & "' and [Month] = " & monthNum)    ' select total collection records

                Set Del = db.OpenRecordset("SELECT * FROM MonthTotalsDelivery where [Customer] ='" _
                                           & custLookup & "' and [Month] = " & monthNum)    ' select total delivery records

                tbl.AddNew    ' create the record in the report

                If monthNum < 10 Then    ' this will add a zero onto month if < 10
                    tbl("index") = custLookup & CStr(Year(Date)) & "0" & CStr(monthNum)    'JohnDoe201508
                Else
                    tbl("index") = custLookup & CStr(Year(Date)) & CStr(monthNum)  'JohnDoe201512
                End If    ' month < 10

                tbl("Customer") = custLookup    ' Give it the customer
                tbl("CustID") = cu("Customer Number")
                tbl("theMonth") = monthNum   ' give it the month.

                ' EOF codes for queries
                If Col.EOF Then
                    tbl("USD_Collect") = 0
                    tbl("RMB_Collect") = 0
                Else
                    Col.MoveFirst
                    tbl("USD_Collect") = Nz(Col("SumOfCollection in USD"), 0)
                    tbl("RMB_Collect") = Nz(Col("SumOfCollection in RMB"), 0)
                End If

                If Del.EOF Then
                    tbl("USD_Bill") = 0
                    tbl("RMB_Bill") = 0
                Else
                    Del.MoveFirst
                    tbl("USD_Bill") = Nz(Del("SumOfBilled USD"), 0)
                    tbl("RMB_Bill") = Nz(Del("SumOfinclude VA Extended Value"), 0)
                End If
                '''''''''''''''''''''''''''''''''''

                tbl.Update    ' SAVE THE NEW RECORD WE JUST CREATED

                ' THAT'S IT. MOVE TO NEXT MONTH! :)
                monthNum = monthNum + 1    ' go to the next month
                x = x + 1
                '=====================
                'RESET FOR NEXT ROUND
                Col.Close
                Del.Close
                '                Set Col = Nothing
                '                Set Del = Nothing
                '====================
            Loop    ' month <> 12

            cu.MoveNext    ' move onto the next customer

        Loop    ' cu EOF
    End If    ' cu EOF

    MsgBox x & " Records Added"

    'clean up
    On Error Resume Next ' in case a records set is already closed
    Col.Close
    Del.Close
    cu.Close
    tbl.Close

    Set Col = Nothing
    Set Del = Nothing
    Set Cus = Nothing
    Set tbl = Nothing
    Set db = Nothing


End Sub

There are a couple of things that might speed up record creation, but I don't see much improvement.
119 customers X 12 months = 1,428 records to create. Shouldn't take over 7 minutes.

I have about 1500 emps and create an ave of 6 records each (with lots of function calls) = about 9,000 records. Only takes 3-4 minutes.
What else is going on?

----------------------------------
Shouldn't use spaces, punctuation or special characters (underscore is the exception) in object names


Not really a good idea to use "customer name". What happens when there are 2 customer records with "John Smith"??

[josekreif]

I think I am good. I have further reduced my "set Col/Del" calls from

1440+ times (Over 10 Minutes) - 12 times per customer
to
120 times (1-2 Minutes) - 1 time per customer
to
1 time (1 Second) - 1 time per run.

I think I am good now. Just got to be sure your code is always efficient as possible. I have noticed over the last few months that using "Set x = y.openrecordset()" does take some time away from the program.

I'm calling it a day. Tomorrow I will get the report checked out for validity.

Thanks for the suggestions.

-----------------
UPDATE: for those who want to see the new code to compare it and learn

 
' NOTE FROM: Developer
' Code has been reworked 3 times to bring down the run time
' from 10+ minutes, to 2 minutes, to now 1 second).
' Logic shouldnt be hard to follow


  Dim db As DAO.Database  Dim tbl As DAO.Recordset, Cus As DAO.Recordset, Col As DAO.Recordset, Del As DAO.Recordset
    Set db = CurrentDb()
    db.Execute ("DELETE * from BuyVsPay") ' clear the table. We will rebuild it each time
    Set tbl = db.OpenRecordset("SELECT * FROM BuyVsPay")
    Set cu = db.OpenRecordset("SELECT [Customer Name],[Customer Number] FROM Customer order by [customer name] asc")
  
  Dim custLookup As String ' holds the customer name so we can search for it. Might need to change to use ID in future
  Dim monthNum As Integer ' Counter used to pull in the months in order.
  Dim x As Integer        ' Counter | How many total reocrds?
  Dim cansearch As Boolean ' Used to turn off are SQL lookup.
  
  
  
  If Not cu.EOF Then ' MASTER CONDITIONAL -
    cu.MoveFirst
    cansearch = True ' Turn the switch on just this once
    
      
'   MASTER LOOP:
    Do While Not cu.EOF
      
      If IsNull(cu("Customer Name")) Then ' This incase the blank record shows up again in the future
        custLookup = "XX"
      Else
        custLookup = cu("Customer Name")
        custLookup = Trim(custLookup) ' Sometimes they will out spaces at the end of the names. -.-
      End If
      
      If cansearch Then ' only search the first time. This allows the report to be made in just 1 second rather than minutes.
        Set Col = db.OpenRecordset( _
          "SELECT [Custom],[Month], [sumofcollection in usd],[sumofcollection in rmb] FROM MonthTotalsCollection " & _
          "ORDER BY [Custom],[Month] ASC") ' select total collection records
      
        Set Del = db.OpenRecordset( _
          "SELECT [Customer],[Month],[SumOfinclude VA Extended Value],[sumofBilled USD] FROM MonthTotalsDelivery " & _
          "ORDER BY [Customer],[Month] ASC") ' select total delivery records
        
        cansearch = False ' switch it off for the rest of run-time
        If Not Col.EOF Then Col.MoveFirst
        If Not Del.EOF Then Del.MoveFirst
      End If
      
      
      For monthNum = 1 To 12 ' Get 12 months of data, whether they exist or not.
      
        tbl.AddNew ' create the record in the report
        
          If monthNum < 10 Then ' this will add a zero onto month in index if < 10
            tbl("index") = custLookup & CStr(Year(Date)) & "0" & CStr(monthNum) 'JohnDoe201508
          Else
            tbl("index") = custLookup & CStr(Year(Date)) & CStr(monthNum)  'JohnDoe201512
          End If
          
          tbl("Customer") = custLookup ' Give it the customer
          tbl("CustID") = cu("Customer Number") ' ID for future reference.
          tbl("theMonth") = monthNum   ' give it the month.
          
          ' Main logic for collection data
          If Col.EOF Then ' This must come before the others incase we have reached the EOF for this query
            tbl("USD_Collect") = 0
            tbl("RMB_Collect") = 0
          Else ' in this we check is month matches, or if customer matchs.
            If Not Col("Month") = monthNum Or Not Col("Custom") = custLookup Or Col.EOF Then
              tbl("USD_Collect") = 0
              tbl("RMB_Collect") = 0
            Else
              tbl("USD_Collect") = Nz(Col("SumOfCollection in USD"), 0)
              tbl("RMB_Collect") = Nz(Col("SumOfCollection in RMB"), 0)
              If monthNum < 13 And Not Col.EOF Then Col.MoveNext
            End If
          End If
          
          ' main logic for delivery data.
          If Del.EOF Then
            tbl("USD_Bill") = 0
            tbl("RMB_Bill") = 0
          Else
            If Not Del("Month") = monthNum Or Not Del("Customer") = custLookup Or Del.EOF Then
              tbl("USD_Bill") = 0
              tbl("RMB_Bill") = 0
            Else
              tbl("USD_Bill") = Nz(Del("SumOfBilled USD"), 0)
              tbl("RMB_Bill") = Nz(Del("SumOfinclude VA Extended Value"), 0)
              If monthNum < 13 And Not Del.EOF Then Del.MoveNext
            End If
          End If
          '''''''''''''''''''''''''''''''''''
        
        
        tbl.Update ' SAVE THE NEW RECORD WE JUST CREATED
      
        ' THAT'S IT. MOVE TO NEXT MONTH! :)
      '  monthNum = monthNum + 1 ' go to the next month
        x = x + 1


      Next monthNum ' month <> 12


      cu.MoveNext ' move onto the next customer
      
    Loop ' cu EOF
  End If ' cu EOF