twoplustwo
Registered User.
- Local time
- Today, 08:08
- Joined
- Oct 31, 2007
- Messages
- 507
Hi guys, hope you're all well.
I am currently working on a database that reads in a number of daily values. I then open the recordset and lookup the relevant daily price in rsMiddayCurve.
Table tblExposureVolumes is reasonably large, and the below snippet of code takes around 45 secs to run even on a recordset that contains 2k records. Is there any approach I can take to speed this up. FWIW I have no primary key assigned to each record and have the following fields - TradeDate, SettlementDate, Volume, PortfolioType, IsClosed, IsTrade.
I am currently working on a database that reads in a number of daily values. I then open the recordset and lookup the relevant daily price in rsMiddayCurve.
Table tblExposureVolumes is reasonably large, and the below snippet of code takes around 45 secs to run even on a recordset that contains 2k records. Is there any approach I can take to speed this up. FWIW I have no primary key assigned to each record and have the following fields - TradeDate, SettlementDate, Volume, PortfolioType, IsClosed, IsTrade.
Code:
sSqlCurrentDb = "SELECT * FROM tblExposureVolumes"
sSqlCurrentDb = sSqlCurrentDb & " WHERE [TradeDate] = #" & Format(dtTradeDate, "dd/mmm/yyyy") & "#"
sSqlCurrentDb = sSqlCurrentDb & " AND [ExposureValue] = 0 "
Set rsExposureTotals = db.OpenRecordset(sSqlCurrentDb)
With rsExposureTotals 'price new exposures using imported curve
If Not .BOF Then
Do While Not .EOF
dtGasDate = !GasDate
With rsMiddayCurve
sSearchCriteria = "[GasDate]=#" & Format(dtGasDate, "DD/MMM/YYYY") & "#"
sSearchCriteria = sSearchCriteria & " AND [TradeDate] = #" & Format(dtTradeDate, "DD/MMM/YYYY") & "#"
.FindFirst sSearchCriteria
If Not .NoMatch Then
dPrice = Nz(!MiddayPrice, 0)
End If
End With
.Edit
!ExposureValue = (!GasVolume * dPrice) / 100
.Update
.MoveNext
Loop
End If
.Close
End With