DAO performance speed up query selection

[sal0003]

Is this correct to speed up DAO query with vb6 cvlassic?:_

my connection:
Public Sub APRI_CONNESSIONE_DAO(MIO_DB)

On Error GoTo Err_SomeName

Dim strDBName As String
strDBName = "C:\REPORT_L\DATABASE\" & MIO_DB & "-L_TEST.mdb"
'strDBName = "E:\REPORT_L\DATABASE\" & MIO_DB & "-_TEST.mdb"
Set DBS = OpenDatabase(strDBName)

Exit_SomeName:
Exit Sub

Err_SomeName:
MsgBox Err.Number & Err.Description
Resume Exit_SomeName

End Sub

my query:

SQL = "SELECT DT FROM tab WHERE CONTAB='" & TEST_CONTAB & "' GROUP BY DT"
Set RSTDAO = DBS.OpenRecordset(SQL, dbOpenDynaset, dbReadOnly)

 

[TheAceMan1]

How are ya sal0003 . . .

Your opening the db [blue]"C:\REPORT_L\DATABASE\" & MIO_DB & "-L_TEST.mdb"[/blue]. If this db is taking too long to open ... you need to tell us more about that db! ...

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see faq219-2884 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[TheAceMan1]

sal0003 . . .

What is it thats too slow?

See Ya! . . . . . .

Be sure to see faq219-2884 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[TheAceMan1]

sal0003 . . .

I'm going to assume your recordset has a large recordcount and you want to speedup your looping. Using sample code I'm going to combine Faster Recordset Iterations with How to speed up database access to speed up the loop.

Lets say in the sample your adding DT from the recordset to a combobox. The code of which looks like:

[blue]   Dim db As DAO.Database, rst As DAO.Recordset
   Dim SQL As String, CBx As ComboBox
   
   Set db = CurrentDb
   Set CBx = Me("MyComboboxName")
   SQL = "SELECT DT " & _
         "FROM tab " & _
         "WHERE ([CONTAB]='" & TEST_CONTAB & "') " & _
         "GROUP BY DT;"
   Set rst = db.OpenRecordset(SQL, dbOpenDynaset)
   rst.MoveFirst
   
   Do Until rst.EOF
      CBx.AddItem rst!DT
      rst.MoveNext
   Loop
   
   Set rst = Nothing
   Set CBx = Nothing
   Set db = Nothing[/blue]

And now the combined speedup version. Note ... items speeding up the loop are in [purple]purple[/purple]:

[blue]   Dim db As DAO.Database, rst As DAO.Recordset, [purple][b]fld[/b][/purple] As DAO.Field
   Dim SQL As String, CBx As ComboBox
   Dim [purple][b]cnt[/b][/purple] As Long, [purple][b]maxCnt[/b][/purple] As Long
   
   Set db = CurrentDb
   Set CBx = Me("MyComboboxName")
   SQL = "SELECT DT " & _
         "FROM tab " & _
         "WHERE ([CONTAB]='" & TEST_CONTAB & "') " & _
         "GROUP BY DT;"
   Set rst = db.OpenRecordset(SQL, dbOpenDynaset)
   Set [purple][b]fld[/b][/purple] = rst("DT")
   
   rst.MoveLast
   [purple][b]maxCnt[/b][/purple] = rst.RecordCount
   rst.MoveFirst
   
   [purple][b]For cnt = 1 To maxCnt[/b][/purple]
      CBx.AddItem [purple][b]fld[/b][/purple]
      rst.MoveNext
   Next
   
   Set [purple][b]fld[/b][/purple] = Nothing
   Set rst = Nothing
   Set CBx = Nothing
   Set db = Nothing[/blue]

I would expect you to find things speedup at least 10x. I have routines clocked greater than 20x. If you have any questions let me know.

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see faq219-2884 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[sal0003]

hI TheAceMan1, tks for suggestion.