ADO - recordset find performance slow
 

I have a couple of applications that use ADO to update recordsets (SQL) from
an excel interface.

After opening a recordset, I set it to the beginning of the file and then do
a find.

It works, but takes several minutes to find the record.

There are only a few hundred records.
I can use the CopyFromREcordset all records appear in seconds.

I'm using the Find to select the record to edit.

Any ideas how I can speed things up?

ADO - recordset find performance slow
 

have you tried querying only for the record you want?

--
Regards,
Tom Ogilvy


"Ken Valenti" wrote:

I have a couple of applications that use ADO to update recordsets (SQL) from
an excel interface.

After opening a recordset, I set it to the beginning of the file and then do
a find.

It works, but takes several minutes to find the record.

There are only a few hundred records.
I can use the CopyFromREcordset all records appear in seconds.

I'm using the Find to select the record to edit.

Any ideas how I can speed things up?

ADO - recordset find performance slow
 

I guess I don't know how to do that.
Here's a code snippet of what I have.
I don't see query from variable "rs"


Sub TestIt()
Dim rs As ADODB.Recordset
Dim cn As ADODB.Connection
Set cn = New ADODB.Connection
cn.Open TheConnectionString
Set rs = New ADODB.Recordset
rs.Open TheRStable, cn, adOpenKeyset, adLockOptimistic, adCmdTable
rs.Find "criteria here"
'Close connections, etc
End Sub


"Tom Ogilvy" wrote:

have you tried querying only for the record you want?

--
Regards,
Tom Ogilvy


"Ken Valenti" wrote:

I have a couple of applications that use ADO to update recordsets (SQL) from
an excel interface.

After opening a recordset, I set it to the beginning of the file and then do
a find.

It works, but takes several minutes to find the record.

There are only a few hundred records.
I can use the CopyFromREcordset all records appear in seconds.

I'm using the Find to select the record to edit.

Any ideas how I can speed things up?

ADO - recordset find performance slow
 

http://support.microsoft.com/kb/294349/en-us

Look at the SQL string they build - the "Select * from . . . "

--
Regards,
Tom Ogilvy


"Ken Valenti" wrote in message
...
I guess I don't know how to do that.
Here's a code snippet of what I have.
I don't see query from variable "rs"


Sub TestIt()
Dim rs As ADODB.Recordset
Dim cn As ADODB.Connection
Set cn = New ADODB.Connection
cn.Open TheConnectionString
Set rs = New ADODB.Recordset
rs.Open TheRStable, cn, adOpenKeyset, adLockOptimistic, adCmdTable
rs.Find "criteria here"
'Close connections, etc
End Sub


"Tom Ogilvy" wrote:

have you tried querying only for the record you want?

--
Regards,
Tom Ogilvy


"Ken Valenti" wrote:

I have a couple of applications that use ADO to update recordsets (SQL)
from
an excel interface.

After opening a recordset, I set it to the beginning of the file and
then do
a find.

It works, but takes several minutes to find the record.

There are only a few hundred records.
I can use the CopyFromREcordset all records appear in seconds.

I'm using the Find to select the record to edit.

Any ideas how I can speed things up?

ADO - recordset find performance slow
 

Thanks, Worked perfectly.

Performance went from several minutes to a couple seconds!


"Tom Ogilvy" wrote:

http://support.microsoft.com/kb/294349/en-us

Look at the SQL string they build - the "Select * from . . . "

--
Regards,
Tom Ogilvy


"Ken Valenti" wrote in message
...
I guess I don't know how to do that.
Here's a code snippet of what I have.
I don't see query from variable "rs"


Sub TestIt()
Dim rs As ADODB.Recordset
Dim cn As ADODB.Connection
Set cn = New ADODB.Connection
cn.Open TheConnectionString
Set rs = New ADODB.Recordset
rs.Open TheRStable, cn, adOpenKeyset, adLockOptimistic, adCmdTable
rs.Find "criteria here"
'Close connections, etc
End Sub


"Tom Ogilvy" wrote:

have you tried querying only for the record you want?

--
Regards,
Tom Ogilvy


"Ken Valenti" wrote:

I have a couple of applications that use ADO to update recordsets (SQL)
from
an excel interface.

After opening a recordset, I set it to the beginning of the file and
then do
a find.

It works, but takes several minutes to find the record.

There are only a few hundred records.
I can use the CopyFromREcordset all records appear in seconds.

I'm using the Find to select the record to edit.

Any ideas how I can speed things up?

ADO - recordset find performance slow
 

Worked way fast to read the record.

But now I can't edit the data.



"Tom Ogilvy" wrote:

http://support.microsoft.com/kb/294349/en-us

Look at the SQL string they build - the "Select * from . . . "

--
Regards,
Tom Ogilvy


"Ken Valenti" wrote in message
...
I guess I don't know how to do that.
Here's a code snippet of what I have.
I don't see query from variable "rs"


Sub TestIt()
Dim rs As ADODB.Recordset
Dim cn As ADODB.Connection
Set cn = New ADODB.Connection
cn.Open TheConnectionString
Set rs = New ADODB.Recordset
rs.Open TheRStable, cn, adOpenKeyset, adLockOptimistic, adCmdTable
rs.Find "criteria here"
'Close connections, etc
End Sub


"Tom Ogilvy" wrote:

have you tried querying only for the record you want?

--
Regards,
Tom Ogilvy


"Ken Valenti" wrote:

I have a couple of applications that use ADO to update recordsets (SQL)
from
an excel interface.

After opening a recordset, I set it to the beginning of the file and
then do
a find.

It works, but takes several minutes to find the record.

There are only a few hundred records.
I can use the CopyFromREcordset all records appear in seconds.

I'm using the Find to select the record to edit.

Any ideas how I can speed things up?