Find method of ADO Recordsets

[harjeen]

I'm developing a database application which communicates with an SQL Server using the ADO model. The recordsets I'm using have about 4000 records in them at any given time. I've noticed that when I use the FIND method to identify a specific record in a recordset it takes an usually long time to find that record (like 30 seconds).

Has anyone else experienced such slow find times? Is there any way to improve find efficiency?

Thanks in advance for the response,

Nahid Harjee

[JHausmann]

Is the field you're searching indexed? If it isn't, table scans will be used to search the data...

[harjeen]

Thanks for the response.

My tables on the SQL Server are all indexed by Primary Keys. However, does that have an impact in this case? Because I have already populated the recordset from the server, I'm just performing a search within the recordset (which should be all local).

Nahid

[JHausmann]

The location of the recordset depends entirely on which side (client or server) your cursors are running on. The speed will also be affected by the cursor type (forward only, static, keyset and dynamic). You might try playing with these "variables" to see how they affect your response times...

[harjeen]

Thanks a lot for the help JHausmann, I have one more question, if possible.

I tried several configurations of my CursorLocation and CursorType, and as expected, when the cursor is set to adUseClient, my calls of the Find method are almost instant. However, now the time problem occurs when the recordsets are first opened (because all 4000 records have to be pulled over from the server).

I'm not really sure how to solve this problem now. I know it may have something to do with the CacheSize property of a recordset, and also using adAsyncFetch when opening a recordset, but I don't know much about these settings. Do you have ay suggestions?

Thanks again for all your help.

Nahid

[JHausmann]

Not sure but you might try opening the recordset with the CursorType = adOpenKeyset and the LockType = adLockOptimistic for your inital pass...

[Clunietp]

If you don't want to use client side cursors, you can't dynamically index a field in your existing recordset

If you want to use server side cursors and search on the client, at least increase your cachesize to 500 or so
(the default cachesize is 1, so every time you scroll to another record, the client requests SQL server to send the next row. As you can imagine, this can be quite time consuming)

Your best bet would be a search using a stored procedure on an indexed field, so do that if possible

[JasonGS]

I frequently need to handle large amounts of records, I've found that by using the following params when I open the recordset that I get pretty good performance.

CursorType = adOpenForwardOnly + adOpenStatic
LockType = adLockReadOnly

[jscontreras]

Hi,

Depending on what the purpose of your dataset is. If retrieving large amounts of data. I feel your best bet is to use ADO's getrows method. Basically what this does is takes the recordset and converts it into an array. Disconnecting from the server immediately.

Let me know if you need an example,

Thanks