Solved

Large Recorset calculation is too slow

Posted on 2003-11-23
6
366 Views
Last Modified: 2008-03-10
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 As Long
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)=CurrentUser()));"

Set rst = CurrentDb.OpenRecordset("tblRollingTest")

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 tblOpticalTestResultsPassive SET tblOpticalTestResultsPassive.RollingWDL = " & CurrentVal & "" _
& " WHERE (((tblOpticalTestResultsPassive.TestInfoID)=[Forms]![frmMultiChannelSelectImport]![tmpTestInfoID]));"

DoCmd.RunSQL SQL_STR

'''' END OF ROLLING WDL GENERATION
0
Comment
Question by:lightcross
6 Comments
 
LVL 33

Assisted Solution

by:Mike Eghtebas
Mike Eghtebas earned 50 total points
ID: 9809112
Assuming ChNo is a public variable, change:

.
.
Dim intervalone As Long, intervaltwo As Long
Dim CurrUserName As String                               '<--***************NEW
CurrUserName =CurrentUser()                             '<--***************NEW

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='" & CurrUserName    '<--***Revised
.
.
rst.Close      '<--***Revised
Dim TestInfoID As Long                                                                   '<--***************NEW
TestInfoID = Forms!frmMultiChannelSelectImport!tmpTestInfoID         '<--***************NEW

'***Revised:
SQL_STR = "UPDATE tblOpticalTestResultsPassive SET RollingWDL = " & CurrentVal & " WHERE TestInfoID=" & TestInfoID
DoCmd.RunSQL SQL_STR

0
 
LVL 17

Accepted Solution

by:
Natchiket earned 75 total points
ID: 9809570
Use a  database object to refer to the database, rather than currentdb.
e.g.
Dim db as DAO.Databaase
.
.
Set db = DbEngine(0)(0)

Use the execute method of the database object rather than runSQL, which is slower
so
db.Execute SQL_STR

Define your recordset more precisey, e.g.
Set rst = db.Openrecordset("SELECT [WL (nm)],MaxIL FROM tblRollingTest",dbOpenDynaset)


Try and avoid traversing the recordset so much, it takes up a huge amount of time and resources.  For example you could use
strSQL = "SELECT Max([WL (nm)]) as MaxWL from tblRollingTest"
set rstMax = db.OpenRecordset(strSQL,dbOpenSnapShot)
LastNum = rstMax(0)

instead of using movelast and then movefirst

Hope this helps!
0
 
LVL 39

Assisted Solution

by:stevbe
stevbe earned 50 total points
ID: 9810062
Dim NumberRecords, NumIntervalThousandRecords As Long
Dim FirstNum, LastNum

Specifically declare the data types of all variables ... in your current declaration NumberRecords, FirstNum and LastNum are variants.
0
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 
LVL 9

Assisted Solution

by:svenkarlsen
svenkarlsen earned 75 total points
ID: 9810141
Lightcross,

assuming:

[WL (nm)] is common for all 8 channels
MaxIL is the channel parameter

I would suggest the following analysis process:

1. Operate on the full dataset, - i.e. [WL (nm)], [MIN1] - [MIN8] only extracted per CurrentUser

2. Add a field for the [WL (nm)] interval group: [Interval]

3. Do one walk to assign the interval number (re-using your code):
     
       For i = 1 To intervalone 'Was 21
           rst.[Interval]=IntCounter
           rst.MoveNext
       Next
       IntCounter=IntCounter+1

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_IntervalResults]:

SELECT tblRollingTest.Interval, Max([MIN1])-Min([MIN1]) AS Ch1CurrentVal, Max([MIN2])-Min([MIN2]) AS Ch2CurrentVal, Max([MIN3])-Min([MIN3]) AS Ch3CurrentVal, Max([MIN4])-Min([MIN4]) AS Ch4CurrentVal, Max([MIN5])-Min([MIN5]) AS Ch5CurrentVal, Max([MIN6])-Min([MIN6]) AS Ch6CurrentVal, Max([MIN7])-Min([MIN7]) AS Ch7CurrentVal, Max([MIN8])-Min([MIN8]) AS Ch9CurrentVal INTO tblRollingTest_IntervalResults
FROM tblRollingTest
GROUP BY tblRollingTest.Interval;


5. From the table [tblRollingTest_IntervalResults], do one query to get absolute result for each channel:

SELECT Max(tblRollingTest_IntervalResults.Ch1CurrentVal) AS MaksOfCh1CurrentVal, Max(tblRollingTest_IntervalResults.Ch2CurrentVal) AS MaksOfCh2CurrentVal, Max(tblRollingTest_IntervalResults.Ch3CurrentVal) AS MaksOfCh3CurrentVal, Max(tblRollingTest_IntervalResults.Ch4CurrentVal) AS MaksOfCh4CurrentVal, Max(tblRollingTest_IntervalResults.Ch5CurrentVal) AS MaksOfCh5CurrentVal, Max(tblRollingTest_IntervalResults.Ch6CurrentVal) AS MaksOfCh6CurrentVal, Max(tblRollingTest_IntervalResults.Ch7CurrentVal) AS MaksOfCh7CurrentVal, Max(tblRollingTest_IntervalResults.Ch8CurrentVal) AS MaksOfCh8CurrentVal
FROM tblRollingTest_IntervalResults;


Your basic problem is that you don't have a sequence identifier in your source data (or at least appears not to have one). If the measurement system can provide a counter number for each measurement interval, then you will not need to do step 3), and you can set the [Interval] field to indexed/allow-duplicates to help Access a little.

As an "Added Value", this will give you the data for each interval, so you can make a nice distribution analysis or whatever you like ;-)

Regards,
Sven
0
 
LVL 2

Author Comment

by:lightcross
ID: 9812221
Additional notes:
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.
0
 
LVL 9

Expert Comment

by:svenkarlsen
ID: 9812281
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)=CurrentUser()));"
                                    ------^------

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_IntervalResults]:

SELECT tblRollingTest.Interval, Max([MaxIL])-Min([MaxIL]) AS Ch1CurrentVal INTO tblRollingTest_IntervalResults
FROM tblRollingTest
GROUP BY tblRollingTest.Interval;


5. From the table [tblRollingTest_IntervalResults],
   do one query to get absolute result for each channel:

SELECT Max(tblRollingTest_IntervalResults.Ch1CurrentVal) AS MaxOfCh1CurrentVal, FROM tblRollingTest_IntervalResults;


sven
0

Featured Post

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Most if not all databases provide tools to filter data; even simple mail-merge programs might offer basic filtering capabilities. This is so important that, although Access has many built-in features to help the user in this task, developers often n…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now