VBA Access: how to optimize this routine

No it doesn't work that way, because I don't know the IDs in advance. I have to loop through all the IDs existing in the table.

It doesn't look I can do it via a single query. The best what I have found so far is to dump the whole recordset to a variant and next work with the variant as an array. I can save time on opening/closing recordsets (only one opening)

<No it doesn't work that way, because I don't know the IDs in advance>
Your question was:
"I need to loop through all distinct IDs (financial instruments) and do something with each instrument Returns. "

This is what my code does.
It simply loops through all the records in the table, so there is no need to know the ID's at all.

Just to mention a couple of things...

Recordset isn't a good choice for a variable name. It's already (potentially) 2 object types in your referenced libraries.
Date is a potentially problematic field name too (you're having to maintain backets around it).

Vitally, what is the "something" you intend to do with temp?
Pass it to a procedure of some sort? This procedure expects an array as a parameter?

I'm just wondering why, having opened a recordset (with at least 10,000 rows in the source table) you're wanting to then dump all that into an array (the same data, briefly held twice in memory) to work on further.
Sticking with a single recordset throughout could be perfectly reasonably performing.

Are you still wanting to do "something" with temp - even if it's empty?
If not then the original recordset that you open should surely be filtered upon [Date] >= #" & firstdate & "#" also yes?
Making your resultset smaller, your memory footprint smaller, performance better and less to do.
When opening such a recordset, you'll want to explicitly sort the results into ID order - so you can perform that checking when the ID changes. (You can't reply on the table ordering).

SQL = "SELECT [Fund ID], [Date], [Return] FROM Returns WHERE [Date] >= #" & firstdate & "# ORDER BY [FUND ID] "

And iterate through that... depending upon what "something" is.

Thank you!

boag2000, please, have a look at my initial code:

1) The table contains a number of rows with the same Instrument ID (different return and date fields

2) my final task is to get a set of returns (dates) corresponding each unique Instrument ID

the table looks as

ID                                          Date                                        Returns
745                                      05/01/2005                             0.78
745                                      06/01/2005                             1.24
745                                      07/01/2005                             0.66
745                                      08/01/2005                             1.22
978                                      11/01/2003                             0.18
978                                      12/01/2003                             1.62

The reason why i am dumping the whole recordset to an array is because that way I don't open/close 1000s recordsets. it is much faster to manipulate with arrays instead.

I am attaching the code. Will I gain something if I loop through a recordset instead of temp()?

    Set Recordset = New ADODB.Recordset
    SQL = "SELECT [Fund ID],[Date],[Return] FROM Returns WHERE Returns.[Date] >= #" & firstdate & "#"
    Call Recordset.Open(SQL, conHFI, CursorTypeEnum.adOpenForwardOnly, LockTypeEnum.adLockreadOnly, CommandTypeEnum.adCmdText)
    temp = Recordset.GetRows
    totNo = UBound(temp, 2)
    Recordset.Close
    Set Recordset = Nothing
    curr = temp(0, i)
    M = 0
   
    For i = 0 To totNo
        If (curr = temp(0, i)) Then
            ReDim Preserve currRets(0 To 1, 0 To i - M)
            currRets(0, i - M) = temp(1, i) 'dates
            currRets(1, i - M) = temp(2, i) 'returns
            temp1 = currRets(0, 0)
        Else
            'do something with currRets
            If (UBound(currRets, 2) > 10) Then
                ReDim correlArray(0 To UBound(HFRetOnly))
                dDiff = DateDiff("m", HFDates(0), currRets(0, 0))
                For j = dDiff To Application.WorksheetFunction.Min(UBound(HFRetOnly), UBound(currRets, 2))
                    correlArray(j) = Val(currRets(1, j - dDiff))
                Next
                On Error Resume Next
                If Err.Number <> 0 Then GoTo EE
 
                cor = Application.WorksheetFunction.Correl(HFRetOnly, correlArray)
                
                ReDim Preserve allCorrels(0 To 1, 0 To p)
                allCorrels(0, p) = curr  'fund ID
                allCorrels(1, p) = cor   'current correlation for that ID
                p = p + 1
                On Error GoTo 0
            End If
            'end correl build
EE:
            M = i 'resetting the redim counter
            curr = temp(0, i)
            ReDim Preserve currRets(0 To 1, 0 To i - M)
            currRets(0, i - M) = temp(1, i) 'dates
            currRets(1, i - M) = temp(2, i) 'returns
        End If
    Next

                                              

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:
28:
29:
30:
31:
32:
33:
34:
35:
36:
37:
38:
39:
40:
41:
42:
43:
44:

It is the same code looping throughout  the recordset.

The execution time for 10,000 records is 1sec, but for the previous code (dumping a recordset to a variant) is 0sec.

So, cycling through a recordset is much slower

  Set Recordset = New ADODB.Recordset
    SQL = "SELECT [Fund ID],[Date],[Return] FROM Returns WHERE Returns.[Date] >= #" & firstdate & "#"
    Call Recordset.Open(SQL, conHFI, CursorTypeEnum.adOpenForwardOnly, LockTypeEnum.adLockreadOnly, CommandTypeEnum.adCmdText)
    curr = Recordset.Fields("Fund ID").Value
    M = 0
    st = Now
   Do While Not Recordset.EOF
    
        If (curr = Recordset.Fields("Fund ID").Value) Then
            ReDim Preserve currRets(0 To 1, 0 To i - M)
            currRets(0, i - M) = Recordset.Fields("Date").Value 'dates
            currRets(1, i - M) = Recordset.Fields("Return").Value 'returns
            temp1 = currRets(0, 0)
            
        Else
            'do something with currRets
            If (UBound(currRets, 2) > 10) Then
                ReDim correlArray(0 To UBound(HFRetOnly))
                dDiff = DateDiff("m", HFDates(0), currRets(0, 0))
                For j = dDiff To Application.WorksheetFunction.Min(UBound(HFRetOnly), UBound(currRets, 2))
                    correlArray(j) = Val(currRets(1, j - dDiff))
                Next
                On Error Resume Next
                If Err.Number <> 0 Then GoTo EE
 
                cor = Application.WorksheetFunction.Correl(HFRetOnly, correlArray)
                
                ReDim Preserve allCorrels(0 To 1, 0 To p)
                allCorrels(0, p) = curr  'fund ID
                allCorrels(1, p) = cor   'current correlation for that ID
                p = p + 1
                On Error GoTo 0
            End If
            'end correl build
EE:
            M = i 'resetting the redim counter
            curr = Recordset.Fields("Fund ID").Value
            ReDim Preserve currRets(0 To 1, 0 To i - M)
            currRets(0, i - M) = Recordset.Fields("Date").Value  'dates
            currRets(1, i - M) = Recordset.Fields("Return").Value 'returns
        End If
    Recordset.MoveNext
    i = i + 1
    Loop
Debug.Print DateDiff("s", st, Now()) & "s"

                                              

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:
28:
29:
30:
31:
32:
33:
34:
35:
36:
37:
38:
39:
40:
41:
42:
43:
44:
45:

Thank you. a few more details:

1) Yes, conHFI is a string, not an object (see attached)

2) I am using Jet... probably, should try DAO

3) the exact execution times:

a) code I (looping throughout a dataset): 0.765 sec
b) code II (looping throughout an array): 0.312 sec
Total rows: 69070
PC: Dell Core Duo 2.5GHz, 8Gb RAM

In answer to Jeff's question:
<But can you post a sample of what the output would be based on the sample data you posted?>

1) I need to calculate correlations (one-by-one) of the extracted returns for all instruments with a given return string

2) The output should be something like a matrix

745 (Ret)                  745(Dates)                        978(Ret)             978(Dates)                       etc(another ID)
--------------------------------------------------------------------------------------------------------------------------------------
0.78                             12/1/2005                        0.92                       03/4/2006                     etc
0.23                             01/1/2006                        0.54                        04/4/2006

as you can see, all series have different lengths, i.e. a max number of rows should be counted on the longest one

I am thinking even of building a temp table with that structure once to improve performance on subsequent calls later. On the other hand, 0.3 sec is not that bad and I could live with it

Public Const conHFI As String = _
"Provider=Microsoft.Jet.OLEDB.4.0;" + _
"Data Source=C:\HFData\HFI\download.mdb;" + _
"Persist Security Info=False"

                                              

1:
2:
3:
4:

1) Establishing a connection each time meant your recordset opening method was nailing resources.

2) Absolutely worth trying. No harm in that. As mentioned, it should be that bit quicker with Jet data. I'd recommend also using the Field objects suggestion I made earlier to get that extra bit of performance.

3) 0.765 sec Vs 0.312 sec...
Well... That's hardly 1 Vs 0 is it? :-p
Certainly one's twice the time of the other... But that's not insurmountable.

8Gb RAM??
So what OS then? Vista 64?
What version of Office?  (Just curious really).
The large memory would possibly explain the relative GetRows efficiency compared to subsequent processing.

No not Vista, obviously. XP
Office 2007
In fact, this particular machine is a notebook :)

I am closing the question. Thank you for your help!

XP 32