Slow ADO/ODBC to Oracle

[xinhua]

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

[ahara]

just for giggles, try the following connection string:

    DBConnection.Open ("Provider=MSDAORA.1;Password=[pwd];User ID=[schema name];Data Source=[db name];Persist Security Info=True")

This way, you bypass ODBC and go direct - and this is know to be significantly quicker

[xinhua]

Do you mean I can use the similar string you provided and assign it to my CONNECTION_STRING constant? Like this one:

Private Const CONNECTION_STRING = "Provider=MSDAORA.1;Password=readonly;User ID=reader;Data Source=OracleDBName;Persist Security Info=True"

[ahara]

yes, and you pass the name of the schema to User ID parameter, db name to the Data Source parameter, and the password to the Password parameter.....you use an ADO connection object:

dim DBConnection as ADODB.Connection
set DBConnection = New ADODB.Connection

DBConnection.ConnectionString = CONNECTION_STRING
DBConnection.Open

I really am curious how much your query will speed up - according to documentation ODBC can be up to 5 times slower. But if it is still painfully slow, you will have to look at tweaking your query (although if the same query runs fast in SQL*Plus, you should see a difference)....another thing you can try is rather than using the MSDAORA driver (Microsoft), you can try OraOLEDB.Oracle driver (check that website) as this is a native Oracle driver that is reputed to be better than Microsoft's driver. Hope that helps.