Solved

Large Recorset calculation is too slow

Posted on 2003-11-23
6
372 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 34

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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
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…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

749 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