Loading

slow select distinct jet access and vb6 classic



my code for sql:
Public Function ExecuteSQL(SQL)

CLEAN_UP

Set RST0 = New ADODB.Recordset
With RST0
.CursorLocation = adUseServer
DoEvents
.Open SQL, CONN3, adOpenForwardOnly, adLockReadOnly, adCmdText

'.Source = SQL
'.CursorLocation = adUseServer
'.ActiveConnection = CONN3
'.CursorType = adOpenForwardOnly
'.LockType = adLockReadOnly
'DoEvents
'.Open Options:=adCmdText

End With

End Function

my conn:
Public Sub APRI_CONNESSIONE3()

On Error GoTo Err_SomeName

If Not CONN3 Is Nothing Then
If CONN3.State = 1 Then
CONN3.Close
End If
Set CONN3 = Nothing
End If

Set CONN3 = New ADODB.Connection
With CONN3
.CursorLocation = adUseClient
.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\REPORT_L0\DATABASE\" & MIO_DB & "-L0_TEST.mdb;Persist Security Info=False"
End With

Exit_SomeName:
Exit Sub

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

End Sub

my sql:

SQL = "SELECT DT FROM L0_SI WHERE (INTROITATI>0 OR EROGATI>0) GROUP BY DT"
ExecuteSQL (SQL)

note:
- all field in where clausole are indexed
- numbers of records in table are approx 1.500.xxx
- DT is number
- INTROITATI and EROGATI are number double

is this the best way base connection and select records?
See with attention the cursor type for select query and connection string.

tks for reply.



Add new comment