Large Recorset calculation is too slow
Posted on 2003-11-23
I have a module, which is used to analyze testing data and output to a table the analyzed values. I am trying to speed up the processing time by trying different ways of analyzing data. I have one portion of the code one calculation, which is extremely slow.
There are 8 channels so it loops eight times.
Originally raw data imported was only 1000 records but now it is 16000. Before this calculation would move through 20000 records now it has not move through 5504000 records for each channel. I am thinking of filtering the data back down to 1000 records for this calculation.
Dose anyone have any input on what processes the fastest and whether I should use a DAO.Recordset or use a sql ‘s.
Any input and experience with analyzing large recordset is appreciated.
Additional notes about how it functions
Data is copied to a VB generated file structure, then imported, then imported to TEMP table, Analyzed, Hyperlinks are stored and then the TEMP table data is deleted. I have found this approach keeps the overall Database size manageable and all the raw data can be reloaded our calculated again since the hyperlink is stored.
Code as Follows
'''' GENERATES NUMBER FOR ROLLING WDL
DoCmd.RunSQL " DELETE tblRollingTest.[WL (nm)] " _
& "FROM tblRollingTest " _
& "WHERE (((tblRollingTest.[WL (nm)]) Is Null)) " _
& "OR (((tblRollingTest.[WL (nm)]) Is Not Null));"
Dim rst As DAO.Recordset
Dim MinVal As Double
Dim MaxVal As Double
Dim i As Long
Dim CurrentVal As Double
Dim NumberRecords, NumIntervalThousandRecords As Long
Dim FirstNum, LastNum
Dim intervalone As Long, intervaltwo As Long
DoCmd.RunSQL "INSERT INTO tblRollingTest ( [WL (nm)], MaxIL )SELECT SWSRaw.WL, Abs([SWSRaw]![MIN" & ChNo & "]) AS MaxIL From SWSRaw WHERE (((SWSRaw.CurrentUser)=CurrentUser()));"
Set rst = CurrentDb.OpenRecordset("tblRollingTest")
NumberRecords = rst.RecordCount
FirstNum = rst![WL (nm)]
LastNum = rst![WL (nm)]
CurrentVal = 0
intervalone = ((NumberRecords) / (LastNum - FirstNum))
intervaltwo = (0 - (intervalone - 1))
'End of new stuffto calculate thenumber of recordsfor 1 nm
While Not rst.EOF
MinVal = 9999 'Replace this value with a number higher than the maximum in the table
MaxVal = 0
For i = 1 To intervalone 'Was 21
If rst!MaxIL > MaxVal Then MaxVal = rst!MaxIL
If rst!MaxIL < MinVal Then MinVal = rst!MaxIL
If (MaxVal - MinVal) > CurrentVal Then CurrentVal = (MaxVal - MinVal)
If Not rst.EOF Then rst.Move intervaltwo 'was -20
Set rst = Nothing
SQL_STR = "UPDATE tblOpticalTestResultsPassive SET tblOpticalTestResultsPassive.RollingWDL = " & CurrentVal & "" _
& " WHERE (((tblOpticalTestResultsPassive.TestInfoID)=[Forms]![frmMultiChannelSelectImport]![tmpTestInfoID]));"
'''' END OF ROLLING WDL GENERATION