Updating Largeish Table Through Recordset - Index? (1 Viewer)

[twoplustwo]

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.

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

 

[Galaxiom]

It is very much faster to reopen a recordset with the specific criteria thousands of times than to repeatedly Filter or FindFirst in the same recordset.

I have not looked closely at your code but it would appear you could do this task in a query which would be much faster than any recordset based technique.

 

[spikepl]

For first try, leave your code as is, but put index on all criteria fields in both tables