need advise on improving code runtime

[SKV]

Hello - looking for some expert advice.

I have a table with with values by time period (months) for 4 years. I want to calculate "month to previous month delta" , "year over year delta" and "rolling 6 months sum"

I have 3 different approaches to use but not sure which one can do the calculations quicker.
1. Strictly use record set to relatively move up and down for each record to get the right reference value
2. Use a combination of simple record set and query to get reference values
3. Use Dlookup to get reference values

In the past I used to use #1 approach and it was time consuming. This time I used query (#2 approach) within my loop to get the values then update the table but this is taking too much of time too. I was reading online and understand that Dlookup takes too much time too.

Please advise which of the above approach is robust and efficient? or Please advise some other approach which can calculate faster. I have about 200k rcds

 

[SKV]

Hello - looking for some expert advice.

I have a table with with values by time period (months) for 4 years. I want to calculate "month to previous month delta" , "year over year delta" and "rolling 6 months sum"

I have 3 different approaches to use but not sure which one can do the calculations quicker.
1. Strictly use record set to relatively move up and down for each record to get the right reference value
2. Use a combination of simple record set and query to get reference values
3. Use Dlookup to get reference values

In the past I used to use #1 approach and it was time consuming. This time I used query (#2 approach) within my loop to get the values then update the table but this is taking too much of time too. I was reading online and understand that Dlookup takes too much time too.

Please advise which of the above approach is robust and efficient? or Please advise some other approach which can calculate faster. I have about 200k rcds

the code I developed for #2 - this is very slow

Option Compare Database
Option Explicit




Sub TableCalcs()


Dim strRcdKey As String


Dim db As DAO.Database
Dim rs1 As DAO.Recordset
Dim rs2 As DAO.Recordset
Dim dt01 As Date
Dim dt02 As Date
Dim strSQL As String


Dim dblYoYDelta As Double
Dim dblMoMDelta As Double
Dim dblRollingSum As Double


Dim intK As Integer


Dim qdef As DAO.QueryDef




Set db = CurrentDb()
Set rs1 = db.OpenRecordset("TH1_A03_WCR_TRANSFORMED", dbOpenTable)


With rs1
.MoveFirst

Do Until .EOF

'setup rcd key
strRcdKey = rs1![Sv_DataGroup] & rs1![FileID] & rs1![Company] & rs1![RptScenarioGroup] & rs1![Location] & rs1![Business Unit No] & rs1![Account No] & rs1![Product Line]
dt01 = rs1!RptMo3

'Debug.Print strRcdKey

''Calculate Rolling time
intK = 6
dt02 = DateAdd("m", -intK, dt01)

strSQL = "SELECT SUM(MoAmtVal) AS QryVal " & _
"FROM [TH1_A03_WCR_TRANSFORMED] " & _
"WHERE [Sv_DataGroup] & [FileID] & [Company] & [RptScenarioGroup] & [Location] & [Business Unit No] & [Account No] & [Product Line] = '" & strRcdKey & "'" & _
"AND RptMo3 BETWEEN #" & dt01 & "# AND #" & dt02 & "#" & _
"GROUP BY [Sv_DataGroup] & [FileID] & [Company] & [RptScenarioGroup] & [Location] & [Business Unit No] & [Account No] & [Product Line]"
'

' Debug.Print strSQL

Set rs2 = CurrentDb.OpenRecordset(strSQL)

dblRollingSum = rs2!QryVal

rs2.Close
Set rs2 = Nothing

'' Calculate MoM delta
dt02 = DateAdd("m", -1, dt01)

strSQL = "SELECT MoAmtVal " & _
"FROM TH1_A03_WCR_TRANSFORMED " & _
"WHERE [Sv_DataGroup] & [FileID] & [Company] & [RptScenarioGroup] & [Location] & [Business Unit No] & [Account No] & [Product Line] = '" & strRcdKey & "'" & _
"AND RptMo3 = #" & dt02 & "#;"

' Debug.Print strSQL

Set rs2 = CurrentDb.OpenRecordset(strSQL)

If rs2.EOF Then
dblMoMDelta = rs1!MoAmtVal
Else
dblMoMDelta = rs1!MoAmtVal - rs2!MoAmtVal
End If
rs2.Close
Set rs2 = Nothing

'' Calculate YoY delta
dt02 = DateAdd("YYYY", -1, dt01)

strSQL = "SELECT MoAmtVal " & _
"FROM TH1_A03_WCR_TRANSFORMED " & _
"WHERE [Sv_DataGroup] & [FileID] & [Company] & [RptScenarioGroup] & [Location] & [Business Unit No] & [Account No] & [Product Line] = '" & strRcdKey & "'" & _
"AND RptMo3 = #" & dt02 & "#;"

' Debug.Print strSQL

Set rs2 = CurrentDb.OpenRecordset(strSQL)

If rs2.EOF Then
dblYoYDelta = rs1!MoAmtVal
Else
dblYoYDelta = rs1!MoAmtVal - rs2!MoAmtVal
End If
rs2.Close
Set rs2 = Nothing

rs1.Edit
rs1![MoM_Delta] = dblMoMDelta
rs1![RollingPeriodSum] = dblRollingSum
rs1![YoY_Delta] = dblYoYDelta
rs1.Update


rs1.MoveNext
Debug.Print rs1![RcdID_Tbl_TH1A03]
Loop


End With


End Sub

 

[SKV]

Any expert advice???

 

[Micron]

Usually queries will run faster (if not too complicated) than some code loops - especially if those loops are running a query as part of the loop instructions. I'm thinking what you need is something like a table of date values such as is often used in summarizing sales by quarters or something like that.

Consider a date based table (in your case, probably Month and Year values) and using that in a query where this table is joined to a field in one of your other query tables. Of course, the data types and data between this original table and your new one have to be compatible, and without seeing what you have I can't say if your month and year are in the same field or not. The important thing is to be able to join two tables on compatible date values, or by using a calculated date field. Then in query design, enter your criteria in the date field of your new table to restrict the records to whatever month values you use as criteria.

 

[xenou]

I'd also think you could just write an ordinary query without using code to do anything with recordsets. Hard to give specific advice without knowing what's in the tables and what exactly is the information you need to get out of them.