HUGE performance problem using Find Method

[sethindeed]

Greetings,
I am using ADO objects to retrieve some SQL Server tables...However, when I am operating a Find Method, it takes awfully long time to retrieve the specific record. My tables are well indexed and only contain 6000 records. Maybe it is how I open my table. Here is an example on how I am calling the content of a table in a recordset :

Public AdoConn As ADODB.Connection
Public rsConn As ADODB.Recordset
Public Cmd1 As ADODB.Command
Global strConnect As String

Set AdoConn = New ADODB.Connection
Set rsConn = New ADODB.Recordset
Set Cmd1 = New ADODB.Command
strConnect = "Driver={SQL Server};Server=MyServer;Database=MyDatabase;UID=sa;PWD=*******;"


With AdoConn
.ConnectionString = strConnect
.Open
End With
Cmd1.ActiveConnection = AdoConn
Cmd1.CommandText = "tblProject"
rsConn.Open Cmd1, , adOpenDynamic, adLockOptimistic
rsConn.Find "FieldID =" & FieldIdTemp

Is it the way I am opening the recordset ?
Thanx for helping

 

[shmiller]

Have you tried using adOpenStatic. I'm not sure if you can use the Find method with a static recordset, but you might try it.

 

[fluteplr]

No its because you are moving the whole table. Use sql to select the record you want. Find should only be used when you have retrieved a relatively small record set (say less than 100 records.) Since you are not doing a sql select with a where clause your server side indexes are useless. Find simply searches through the ADO record set AFTER it has been retrieved.