lightcross
asked on
Large Recorset calculation is too slow
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.
Thanks
Zack
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
Dim FirstNum, LastNum
Dim intervalone As Long, intervaltwo As Long
DoCmd.SetWarnings False
DoCmd.RunSQL "INSERT INTO tblRollingTest ( [WL (nm)], MaxIL )SELECT SWSRaw.WL, Abs([SWSRaw]![MIN" & ChNo & "]) AS MaxIL From SWSRaw WHERE (((SWSRaw.CurrentUser)=Cur
Set rst = CurrentDb.OpenRecordset("t
NumberRecords = rst.RecordCount
rst.MoveFirst
FirstNum = rst![WL (nm)]
rst.MoveLast
LastNum = rst![WL (nm)]
rst.MoveFirst
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
rst.MoveNext
Next
If (MaxVal - MinVal) > CurrentVal Then CurrentVal = (MaxVal - MinVal)
If Not rst.EOF Then rst.Move intervaltwo 'was -20
Wend
rst.Close
Set rst = Nothing
SQL_STR = "UPDATE tblOpticalTestResultsPassi
& " WHERE (((tblOpticalTestResultsPa
DoCmd.RunSQL SQL_STR
'''' END OF ROLLING WDL GENERATION
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.
Thanks
Zack
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
Dim FirstNum, LastNum
Dim intervalone As Long, intervaltwo As Long
DoCmd.SetWarnings False
DoCmd.RunSQL "INSERT INTO tblRollingTest ( [WL (nm)], MaxIL )SELECT SWSRaw.WL, Abs([SWSRaw]![MIN" & ChNo & "]) AS MaxIL From SWSRaw WHERE (((SWSRaw.CurrentUser)=Cur
Set rst = CurrentDb.OpenRecordset("t
NumberRecords = rst.RecordCount
rst.MoveFirst
FirstNum = rst![WL (nm)]
rst.MoveLast
LastNum = rst![WL (nm)]
rst.MoveFirst
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
rst.MoveNext
Next
If (MaxVal - MinVal) > CurrentVal Then CurrentVal = (MaxVal - MinVal)
If Not rst.EOF Then rst.Move intervaltwo 'was -20
Wend
rst.Close
Set rst = Nothing
SQL_STR = "UPDATE tblOpticalTestResultsPassi
& " WHERE (((tblOpticalTestResultsPa
DoCmd.RunSQL SQL_STR
'''' END OF ROLLING WDL GENERATION
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Lightcross,
as far as I can see, you have the ChNo as separate fields in the source data, - i.e. 8 fields called MIN1-MIN8 ?:
> DoCmd.RunSQL "INSERT INTO tblRollingTest ( [WL (nm)], MaxIL )SELECT SWSRaw.WL,
> Abs([SWSRaw]![MIN" & ChNo & "]) AS MaxIL From SWSRaw WHERE (((SWSRaw.CurrentUser)=Cur
------^------
Anyhow, - the job should work even if you cut it down to 1 channel as shown below (only 4 & 5 is new, - you must do 1-3 anyhow).
4. Do one query to get all 8 channel results by interval
(basically it groups on each interval and finds the max(MaxIL)-min(MaxIL)
- the result is output to the table [tblRollingTest_IntervalRe
SELECT tblRollingTest.Interval, Max([MaxIL])-Min([MaxIL]) AS Ch1CurrentVal INTO tblRollingTest_IntervalRes
FROM tblRollingTest
GROUP BY tblRollingTest.Interval;
5. From the table [tblRollingTest_IntervalRe
do one query to get absolute result for each channel:
SELECT Max(tblRollingTest_Interva
sven
as far as I can see, you have the ChNo as separate fields in the source data, - i.e. 8 fields called MIN1-MIN8 ?:
> DoCmd.RunSQL "INSERT INTO tblRollingTest ( [WL (nm)], MaxIL )SELECT SWSRaw.WL,
> Abs([SWSRaw]![MIN" & ChNo & "]) AS MaxIL From SWSRaw WHERE (((SWSRaw.CurrentUser)=Cur
------^------
Anyhow, - the job should work even if you cut it down to 1 channel as shown below (only 4 & 5 is new, - you must do 1-3 anyhow).
4. Do one query to get all 8 channel results by interval
(basically it groups on each interval and finds the max(MaxIL)-min(MaxIL)
- the result is output to the table [tblRollingTest_IntervalRe
SELECT tblRollingTest.Interval, Max([MaxIL])-Min([MaxIL]) AS Ch1CurrentVal INTO tblRollingTest_IntervalRes
FROM tblRollingTest
GROUP BY tblRollingTest.Interval;
5. From the table [tblRollingTest_IntervalRe
do one query to get absolute result for each channel:
SELECT Max(tblRollingTest_Interva
sven
ASKER
ChNo is a variable, which the function grabs when it is run.
All data is negative which makes it very confusing to calculate unless it changes it to ABS first ie. Min becomes max and max becomes min.