Efficiency Question: Large Recordset Looping 2

[MCuthill]

Greetings Fellow Tek-Tippers!

I have a theoretical question to ask fo the group, hoping someone may know if I can speed up a function I have developed.

I have a two-phase macro set up, the first step generates a very large table (1.1M+ records), the second step analyses the first to generate a second table around the 1M record mark. Step #1 takes approximately 6 hours to process (I am okay with this timeline), but the second step has now been running for just over 4 days (96+ hours now) and is just now nearing completion.

In step two, I thought selecting the current account being processed and dumping the results to a temporary table (so the inner loop only has to look at the results for the current account) would be more effective, then emptying the temp table and repeating on each account to be processed would be the best approach. I am now wondering if the append and deletion steps are actually taking more processing time than if I just directly queried the first table (with all 1.1M records), just adding the current account being processed to the query selection criteria.

The Outer Loop in Step #2 will run aprroximately 22,000 iterations, with the Inner Loop on each running between 1 and 65 interations depending on the instance (averaging around 45).

I can provide the code if needed (process currently running), though wondering if anyone in the group has come across something similar and has any insight (as a 5-day turnaround time is not overly acceptable)?

I also have asked our IT staff about running this process on a virtual machine to see if that speeds up the processing time should there not be a more effective approach from the VBA-side.

Thanks in advance,

Mike
---------------------------------------------------------------
EvE Online: Claim the Destiny that is Rightfully Yours...

 

[dhookom]

I think we would need to know more about your code, tables, indexes, etc to provide a good guess.

Have you considered using SQL Server?

Duane
Hook'D on Access
MS Access MVP

 

[MCuthill]

Thanks Duane,

I have considered SQL Server, but I am not as versed in the coding required (I have always used VBA on projects like this) and presently do not have access to the SQL server - though may be able to get access if needed. As always with reporting projects, time was a factor - I didn't have time to learn the code needed in SQL and didn't foresee this second step being so intensive.

Once the file is unlocked and I can access the VBA module, I will upload the code.

Thanks for your assistance,

Mike
---------------------------------------------------------------
EvE Online: Claim the Destiny that is Rightfully Yours...

 

[MCuthill]

I decided to take a chance and copy the DB while the macro was processing... please see below for the code I am looking to optimize.

Please advise should anyone have any questions. Thanks!

[b]Public Function Generate_MEBalanceSchedule()[/b]

Dim cSQL As String
Dim recsetAllActiveLoans As Recordset [COLOR=green]’Used to store listing of Active Loans to process[/color]
Dim recsetCurrentLoan As Recordset [COLOR=green]’Used to store Balance found for Month End for Current Loan being processed[/color]
Dim flagEndDate As Date [COLOR=green]’Flag as last date process will generate Amortization until if Balance still > 0[/color]
Dim currAcctNum As Double [COLOR=green]’Current account being processed[/color]
Dim currBalance As Double [COLOR=green]’Balance of Loan being processed (most recent balance prior to current month end processed) [/color]
Dim currMonthEndDate As Date [COLOR=green]’Date Value of Month End being processed[/color]

[COLOR=green]’Set Last Date Amortization Schedule Generated until if Balance still greater than zero[/color]
[COLOR=green]’Testing: December 31st, 2010[/color]
[COLOR=green]’Live: December 31st, 2015[/color]
flagEndDate = DateSerial(2015, 12, 31)


[COLOR=green]’Identify the first active loan on system to be processed[/color]
cSQL = "SELECT LOAN.[Account Number], LOAN.[Balance], LOAN.[Effective Date] " & _
    "FROM LOAN " & _
    "WHERE (((LOAN.Status)=[COLOR=green]’ACT[COLOR=green]’) AND ((LOAN.BALANCE)>0)) " & _
    "ORDER BY LOAN.[Account Number]; "
Set recsetAllActiveLoans = CurrentDb.OpenRecordset(cSQL)
recsetAllActiveLoans.MoveFirst

[COLOR=green]’***********************************[/color]
[COLOR=green]’Loop for Each Account To START Here[/color]
[COLOR=green]’***********************************[/color]
Do Until recsetAllActiveLoans.EOF
    
    [COLOR=green]’Set Database Field values to Variables[/color]
    currAcctNum = recsetAllActiveLoans("Account Number")
    currMonthEndDate = recsetAllActiveLoans("Effective Date")
    
    [COLOR=green]’Step to Month End after current data date[/color]
    currMonthEndDate = DateSerial(Year(currMonthEndDate), Month(currMonthEndDate) + 2, 1) - 1
    
    [COLOR=green]’Insert the Amortization Schedule for the current loan being processed into the temporary table[/color]
    cSQL = "INSERT INTO currLoanTEMP ( [Account Number], [Payment Date], [Ending Balance] )" & _
    "SELECT AmortizationSchedule.[Account Number], AmortizationSchedule.[Payment Date], AmortizationSchedule.[Ending Balance]" & _
    "FROM AmortizationSchedule " & _
    "WHERE (((AmortizationSchedule.[Account Number])=" & currAcctNum & ")) " & _
    "ORDER BY AmortizationSchedule.[Payment Date];"
    
    CurrentDb.Execute (cSQL)
        
    [COLOR=green]’*************************************[/color]
    [COLOR=green]’Loop for Each Month End To START Here[/color]
    [COLOR=green]’*************************************[/color]
    Do While currMonthEndDate <= flagEndDate
    
    [COLOR=green]’Query the Minimum Balance of the Loan Prior to the Month End being Processed[/color]
    [COLOR=green]’Logic: as loans are paid down, the minimum balance prior to this date would be the balance after the last payment[/color]
    cSQL = "SELECT Min(currLoanTEMP.[Ending Balance]) AS EndBal " & _
    "FROM currLoanTEMP " & _
    "WHERE (((currLoanTEMP.[Payment Date])<=#" & currMonthEndDate & "#));"
    
    Set recsetCurrentLoan = CurrentDb.OpenRecordset(cSQL)
    
    
    [COLOR=green]’If no payment prior to next month end after data, take Loan Balance as of Current Month End. [/color]
    If IsNull(recsetCurrentLoan("EndBal")) Then
        [COLOR=green]’Use Current Month End Balance[/color]
        currBalance = recsetAllActiveLoans("Balance")
    Else
        [COLOR=green]’Capture the Minimum Balance queried above[/color]
        currBalance = recsetCurrentLoan("EndBal")
    End If
    
    [COLOR=green]’Insert the results into the Month End Balance Schedule (Account Number & Month End being processed & the balance found) [/color]
    cSQL = "INSERT INTO MonthEndBalanceSchedule ( [Account Number], [Balance], [Month End Date] ) " & _
    "SELECT " & currAcctNum & " AS [Account Number], " & currBalance & " AS [Balance], #" & currMonthEndDate & "# AS [Month End Date]"
    
    CurrentDb.Execute (cSQL)
    
    [COLOR=green]’If Loan Paid Off, Do not continue processing for remaining Month Ends.
    If currBalance = 0 Then[/color]
        [COLOR=green]’Set currMonthEnd to day after "trigger date" which stops analysis[/color]
        currMonthEndDate = flagEndDate + 1
    Else
        [COLOR=green]’Step To Next Month End Date to Process[/color]
        currMonthEndDate = DateSerial(Year(currMonthEndDate), Month(currMonthEndDate) + 2, 1) - 1
    End If
    
    [COLOR=green]’***********************************[/color]
    [COLOR=green]’Loop for Each Month End To END Here[/color]
    [COLOR=green]’***********************************[/color]
    Loop
    
    [COLOR=green]’Clear Temporary Table prior to moving to next loan[/color]
    cSQL = "DELETE * FROM currLoanTEMP; "
    CurrentDb.Execute (cSQL)
    
    [COLOR=green]’Move to next Active Account to Process[/color]
    recsetAllActiveLoans.MoveNext
    
[COLOR=green]’*********************************[/color]
[COLOR=green]’Loop for Each Account To END Here[/color]
[COLOR=green]’*********************************[/color]
Loop

[b]End Function[/b]

Mike
---------------------------------------------------------------
EvE Online: Claim the Destiny that is Rightfully Yours...

 

[dhookom]

Is the data on a network share or local machine. If possible, I would copy locally prior to doing this.

Which fields are indexed? Any field used for filtering or sorting should be indexed.

I would not use "ORDER BY" on the INSERT INTO statement.

Change the cSQL insert to:

    cSQL = "INSERT INTO MonthEndBalanceSchedule ( [Account Number], [Balance], [Month End Date] ) " & _
    "Values (" & currAcctNum & ", " & currBalance & ", #" & currMonthEndDate & "# )"
    
    CurrentDb.Execute (cSQL)

Duane
Hook'D on Access
MS Access MVP

 

[MCuthill]

Thanks Duane,

The database is on a network share, with all the data having been copied to said DB. Though it would be running off the network onto my local machine. I assume the network connection could be creating a potential bottle-neck as the read/writes occur from the local PC to the Network, and back? It can most certainly be copied local prior to running this process (and be moved back to the network once complete).

Indexed... don't laugh, I am not sure if any of the fields would be indexed on the tables - is that a setting I can control on the tables? (I seem to recall seeing it on Table > Design View).

I will look at removing the ORDER BY - though you copied the second INSERT INTO statement for where to change. =) I beleive I had the "order by" there to assist in testing, able to visually see how the data flowed from the first table into the second.

Thanks again for all your assistance Duane, it is much appreciated.

Mike
---------------------------------------------------------------
EvE Online: Claim the Destiny that is Rightfully Yours...

 

[dhookom]

The order by and the INSERT INTO are separate recommendations for changes.

Duane
Hook'D on Access
MS Access MVP

 

[MCuthill]

Oops! Sorry Duane!

I read, and reread your INSERT INTO recommendation and yet I still missed the changes from what I had.

I will look to implement both changes and see if performance increases. The process has now finished up, so the next few days will be spend analyzing the results - but I will need to rerun this every month and want it to run as optimal as possible.

Thanks again for all your assistance!

Mike
---------------------------------------------------------------
EvE Online: Claim the Destiny that is Rightfully Yours...

 

[MajP]

Although Access should be able to handle this as long as you are under 2Gb, I would think that something like this should take tens of minutes not hours and days. However, it is hard to predict what Access will do with queries this big. It is kind of pushing the limits. I do believe if you put this on a sql server backend (which you can get for free) you should be able to get this into the sub hour speed.

In theory what you have is pretty inefficient. But in reality this may work better than feeding a big query to Access that it chokes on. At least with the loop you bite off a little at a time. So without testing, it is hard to tell.
When looking at this I think 90% could be done with straight SQL and no looping. I would think at a minimum you could start with a query linking active loans to their amortization schedule. If it was me I think I would try what Duane said and run a make table query to my local machine, from this. I would just think this is way faster than creating a temp table and writing to it. The remaining actions could be done by looping this query/table or even through sql.

A couple of small things, but with this size could make big differences.
If you are instantiating a recordset and do not define the type it defaults to the type with the most functionality and usually the slowest to read. The fastest to read is probably dbOpenForwadOnly

“Forward-only-type Recordset— identical to a snapshot except that no cursor is provided. You can only scroll forward through records. This improves performance in situations where you only need to make a single pass through a result set. This type corresponds to an ODBC forward-only cursor”

Ex: .OpenRecordset ("queryName", dbOpenForwardOnly)

Also stored queries usually run much faster than those generated through code. This is because they are optimized through Rushmore. No reason your first query cannot be stored and saved because there are no variables in it.
Out of curiosity what does this resolve to? I cannot figure out what happens with the single quotes inside the string.
(LOAN.Status)='ACT')

 

[MCuthill]

Thanks for the suggestions MajP,

This line: (LOAN.Status)='ACT')
was an error, I used MS Word to add the tags with Find Replace, and used a single quote as my trigger to add the tags. Missed removing those from the final post.
The line should read: (LOAN.Status)='ACT') My Apologies.

I am well under the 2GB mark on this database, having been at around 350MB before Compact & Repair, and under 200MB once taking this step.

I will investigate the difference RecordSet instantiation, I am intrigued by the efficiency gained by this step. As I mentioned above to Duane, I am unsure if SQL is a viable option at this time due to my limited knowledge of writing in SQL and presently not having access to the SQL Server.

I have since talked to IT some more and we are going to do some testing with running it directly on a Physical Server (more power) and then on a Virtual Machine with even more resources allocated.

Thanks again for your suggestions on improving the above, it is greatly appreciated.

Cheers!

Mike
---------------------------------------------------------------
EvE Online: Claim the Destiny that is Rightfully Yours...

 

[vbajock]

I hope you are compacting your db between runs.

I think if you spent a little time converting this to ADO you'd solve your problem.

 

[MajP]

I think if you spent a little time converting this to ADO you'd solve your problem.

Are you serious???? Please explain this one.

 

[MCuthill]

Hi Everyone,

Update: I have implemented the above suggestions and am starting testing today to see what kind of performance increases have been acheived.

Efficiencies Implemented:
1) "INSERT INTO" SQL Statements have been cleaned up as per dhookum's post on 16 Aug 10 16:44 - in both the above modules as well as the other modules for this program.

"INSERT INTO MonthEndBalanceSchedule ( [Account Number], [Balance], [Month End Date] ) " & _
    "SELECT " & currAcctNum & " AS [Account Number], " & currBalance & " AS [Balance], #" & currMonthEndDate & "# AS [Month End Date]"

changed to:

cSQL = "INSERT INTO MonthEndBalanceSchedule ( [Account Number], [Balance], [Month End Date] ) " & _
    "Values (" & currAcctNum & ", " & currBalance & ", #" & currMonthEndDate & "# )"

2) All RecordSets which are used to scroll through and read information from have been defined as type "dbOpenForwardOnly" as per MajP's post on 17 Aug 10 9:54. As a result of this change, I had to remove the "MoveFirst" command on the same recordsets.

In addition to the coding changes, I have also moved the DB to my local drive prior to executing the modules.

Mike
---------------------------------------------------------------
EvE Online: Claim the Destiny that is Rightfully Yours...

 

[MCuthill]

Update: Results

Module -- Original -- New Local -- New Network
. #001 -- 6 hours --- 0.52 hrs --- 03.5 hrs
. #002 -- ~75 hrs --- 6.58 hrs --- 44.0 hrs
(Originally ran on Network)

Module #001 time is actual for local run, and projected based on sample for network.
Module #002 times are both projected - New local on processed so far, and New Network assuming same gain as on Module #001.

Thanks for all your assistance everyone, looks like running local made the biggest gains, though the coding changes helped as well and any time gained is much needed on a process this large. Projected to take these two steps around 7 hours to complete, a timeline I am quite comfortable with.

Cheers!

Mike
---------------------------------------------------------------
"To be alive is to revel in the moments, in the sunrise and the sunset, in the sudden and brief episodes of love and adventure,
in the hours of companionship. It is, most of all, to never be paralyzed by your fears of a future that no one can foretell."