Hi, I have VB6 code here to access Oracle 8i with ADO/ODBC. I found the performance is very bad. If I run the same query from SQLPlus, it only takes a few seconds. But with the program it take a few minutes. The bottle neck is the statement of checking rstSource.EOF. I am using Microsoft ODBC for Oracle driver. Any help is appreciated. Thank you in advance.


Private Const CONNECTION_STRING = "DSN=MyDBDev;UID=reader;PWD=readonly"

Private Sub ImportData(strSQL As String)
Dim cnnConn As ADODB.Connection
Dim rstSource As ADODB.Recordset
Dim cmdCommand As ADODB.Command

lblStatus.Caption = "Connecting to DB ..."

' Open the connection.
Set cnnConn = New ADODB.Connection
With cnnConn
.ConnectionString = CONNECTION_STRING
.Open
End With

' Set the command text.
Set cmdCommand = New ADODB.Command
Set cmdCommand.ActiveConnection = cnnConn
With cmdCommand
.CommandText = strSQL
.CommandType = adCmdText
.Execute
End With

lblStatus.Caption = "Retrieving data ..."

Set rstSource = New ADODB.Recordset
rstSource.Open cmdCommand

If Not rstSource.EOF Then '<-- This statement takes very long time to execute!!
ProcessData rstSource
End If

End Sub