ADO find method too slow, how should I do this

I need an efficient way to get the absolute position of a record in a query matching a specific key value. The Find method is a serial search, too slow for big data sets. I am using both SQL Server Express and Jet 4 via ADO.

One example of why I need this .....

I have a list control with a subset of a table (controlled by where clause in query). I want to save the current state of the list control and later restore it when the app restarts. I want to preserve and restore the current line selection in the list control.

So, I save the key value for the current line, upon restart use the find method to locate the key, and set the list control current record index to the current absolute position.

This is too slow for big data sets since the find does a record by record search.

I cannot just save and restore the list control offset, the table may have changed.

The list control has owner data so the data is not all read into the control, so I can't just search through the controls image.

Any ideas. I did search for this answer and failed. Feel free to flame me as long as an answer is included too :-)

Z

Hi,

Here's a quick idea: instead of doing the Find, why don't you simply run a query like "SELECT COUNT(*) FROM mytable WHERE tablekey <= mycurrentkey", this would give you the desired "absolute position" in the recordset and you could simply manipulate the selection in the listbox.

I understand this may go out of sync with the original table, but you could mitigate that - for instance, enclosing both the listbox population and the query above into a transaction (probably with higher isolation level).

And one more (simpler) idea - many of the list and combo-box controls have their own Seek methods. You can use that and position into the control instead of going through the recordset. The search will be local into the already populated data.

HTH,

Jivko Dobrev - MSFT

--
This posting is provided "AS IS" with no warranties, and confers no rights.

|||

Another idea that might work is manually do a binary search on the keys in the listbox (if the items in the listbox are sorted by key this will work). Should be faster than find.

Likewise if your key is ordered you could do what Jivko mentions but something like this:

select count(*) from table where <conditions that restrict items> and key=<saved pkey from last time>

Count should give you your absolute position or zero if the record is deleted.