Connecting to oracle via ODBC & ADO - Why so slow?

Please help

I'm connecting to an oracle database via ODBC and ADO. I'm
populating a DB Grid using a query based on the boundtext
of a datalist. The datalist's recordsource is an Adodc data
control.

This works fine but the problem is it is
so slow. I'm only using a test database with a couple of
records in each table so the lack of speed must be due to
the way i'm connecting.


Any ideas?


____________________________________________________

Dim conn As New ADODB.Connection
Dim Cmd As New ADODB.Command
Dim rs As New ADODB.Recordset

dgdContract.Visible = True

conn.Open "dsn=dsn;pwd=pwd"
Set Cmd.ActiveConnection = conn
Cmd.CommandType = adCmdText
Cmd.CommandText = "SELECT a.name as AccountManager" _
& ",c.startdate AS StartDate,c.paymentschedule as Frequency" _
& ",c.contractid as hidethis " _
& " FROM accmanager a, contract c" _
& " WHERE a.accmanagerid = c.accmanagerid AND " _
& "c.clientid=" & dblClient.BoundText

MyClientID = dblClient.BoundText


rs.CursorLocation = adUseClient
rs.Open Cmd, , adOpenDynamic, adLockOptimistic
Set dgdContract.DataSource = rs
dgdContract.ReBind

________________________________________________________

Cheers

SELECT a.name as AccountManager,
c.startdate AS StartDate,
c.paymentschedule as Frequency,
c.contractid as hidethis
FROM accmanager a, contract c
WHERE a.accmanagerid = c.accmanagerid
AND c.clientid= dblClient.BoundText

I would change your where clause to be
WHERE c.clientid= dblClient.BoundText
AND a.accmanagerid = c.accmanagerid

But this is probably not the problem. It is just faster to do the table linking last.

You should step through and determine which line is taking too long. If it is rs.open, then you will want to change the way you call the db. If it is
Set dgdContract.DataSource = rs
or
dgdContract.ReBind
you're hosed because the datacontrol is doing the work.

Here is the code I use

Dim strConnection As String
Dim mConnection As ADODB.Connection

Set mConnection = New ADODB.Connection

With mConnection
.ConnectionTimeout = 3
.CursorLocation = adUseClient
End With

strConnection = "SERVER=PAS;" & _
"DRIVER={Microsoft ODBC for Oracle};" & _
"UID=yourid;PWD=yourpwd;"


mConnection.Open strConnection

Dim strSQL As String
Dim cmd As New ADODB.Command

'You'll have to format the sql statement
strSQL = "SELECT a.name as AccountManager,
c.startdate AS StartDate,
c.paymentschedule as Frequency,
c.contractid as hidethis
FROM accmanager a, contract c
WHERE a.accmanagerid = c.accmanagerid
AND c.clientid = '" & dblClient.BoundText & "'"

strSQL = UCase$(strSQL)

With cmd
.ActiveConnection = mConnection
.CommandType = adCmdText
.CommandText = strSQL
End With

'Check for contract
Set rsMerchandise = cmd.Execute

Set dgdContract.DataSource = rs
dgdContract.ReBind

Again, step through and see where the holdup is.




Brian T. Wiehoff

Just out of interest is there any reason why you guys are using the ODBC provider to connect to Oracle instead of the Oracle provider?

Q...

In days gone by, the Microsoft ODBC provider was much, much more stable. That has changed, but if the code is written like my example, we don't see a real performance change, so we haven't switched.



Brian T. Wiehoff

Q, what is the Oracle provider? When I use adodc properties to help me build my connection string it gives me this as the provider..."Provider=MSDASQL.1". It works when I "Test Connection", but when I delete the adodc and try and code it through global declarations, it doesn't work.