Post reply

ADO find method too slow, how should I do this

  • December 20, 2006 at 9:54 am

    #92057

    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

  • December 21, 2006 at 6:16 am

    #679387

    You don't specify what language you are using (.net or not? vb? asp?). 

    • Assuming that you are using ADO (and not ADO.Net), you can use Seek method in combination of setting Index to your key column.

    • If you are doing a Windows app, and your "list control" is a listbox, you can use SendMessage API to find a specific value.  I don't remember the exact syntax, but doing a search on MSDN for SendMessage and FINDEXACTSTRING should get it for you.

    • If you are using ADO.Net, using the Find method on the DataRowCollection.  That uses the Primarykey index on the DataTable to find a specific row.  But I'm not sure how you will relate that to your list control, as it will not have "row number", per se.

    Hope this helps



    Mark

  • December 21, 2006 at 6:54 am

    #679397

    Thanks Mark

    It is a C++ app using ADO (not .net)

    Seek is not supported on the SQL Server, I need to support both Jet and SQL Server.

    I can't search the list control, it is "owner data" and thus does not have all the data.

    I have found that by doing a query of all records "less than" my target and then doing a "SELECT COUNT (*) ..." is much faster than the find.

    Z

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply