Seek or Find (1 Viewer)

[Luciano]

Hi, I used to work in an Access DAO-environment with indexed tables. I was very happy to work with the '.seek Criterium' in my Recordsets to look after the 'good' records.
However, I want to switch to an ADO-environment. Whitch method is the best (and the quickest) in my indexed tables: '.seek Criterium' or '.find Criterium'?

 

[gemma-the-husky]

i would stay with DAO personally

seek uses a pre-set index, but cannot be used on linked tables, so generally you end up using find, which is a bit slower.

 

[Luciano]

I heart tha DAO is 'old-fashion' and will be no more supported by Microsft in the future. Besides, I want to use my database as a multi-user db with splitted tables.

 

[pbaldy]

I personally use DAO most of the time, but whichever method you use, most efficient would be opening the recordset with an SQL statement that restricted it to the desired records, rather than opening it on the entire table and then trying to find the desired records.

 

[Luciano]

Is there a preference between opening with an SQL statement or opening as a table and search with a Filter (something like tblTable.Filter = strFilter )?

 

[pbaldy]

I've never used that method, but it appears to have the same flaw (presuming a typical network environment). You are pulling the entire table across the network then filtering rather than pulling only the desired records across the network. Test both methods yourself with a large table.

 

[Luciano]

OK, thanks, this was very helpful .

 

[Pat Hartman]

I heart tha DAO is 'old-fashion' and will be no more supported by Microsft in the future

That rumor started a decade ago when the Access team changed the library order to list ADO first. They never actually changed Access forms/reports though so there was never a real reason to switch and switching caused certain types of problems if you were using recordset clones. However, with the advent of A2007 when the Access team got control over Jet and reinvented it as ACE, we went back the other way. Now DAO is back in the driver's seat. Some use ADO for non-Jet/ACE tables but I haven't found any issues with DAO so I never switched.

In any event, unless your tables are Jet/ACE and small enough to fit into memory, I would not use Seek or Find. Nor would I use Filter for the same reason. Plus, if you are linked to something like SQL Server, I would tell you to change your code immediately to use queries with criteria. You NEVER want to bring down entire tables from a real RDBMS. Jet/ACE work differently and so you don't get the massive advantage of restricted queries that you get with SQL Server.

It takes a maximum of 8 physical reads to retrieve any row from a million row table using an index (a binary search) but if you use Seek/Find/Filter, you need to bring all one million rows across the wire so the larger your tables become, the more important it is that you use efficient retrieval methods and in most cases that will be a query with criteria.

 

[Luciano]

I have three 'large' (bound and split) back-end tables that works 'together'. Large means in my application between 500 and 10 000 records. When I launch a criterion (max. on 3 fields), the number of retrieved records is rather small (between 0 and 20 records). You think that even in this case it would be better to open with an SOL-statement.
I repeat asking because I have a hug of code opening tables with Seek, Filter or/and Find.

 

[gemma-the-husky]

I think the thing is that if you are getting acceptable performance stick with it. There is no point changing for changes sake.

However, in general stored queries are easier to use and develop, and less prone to errors than writing and debugging complex code.

so as you add more functions, consider using queries.

The only occasions I use "find", I think is in some text searches. where a user needs to find a customer, say, often customer names do not lend themselves to direct use of combo boxes.

eg A.B. Johnson Supplies Ltd is likely to be identified by users as "Johnson" rather than as AB. You could add a "search name" to the customer table, and sort a combo box on the search name rather than the company name. But the alternative I sometimes use is to emulate the binoculars in code, by letting the users enter a search string, and then using findfirst and findnext to search for matches in the control, until they find the one they want. As long as the domain is small, it works OK.