How to Speed Up Retrieving and Searching In ADO

[Shella]

Hi
I wonder if using "Select..." statement is faster Or Find method of a recordset in ADO ? I'm used to visual foxpro programming in the past and found that visual foxpro database handling performance is much more efficient and faster than visual basic like retrieving and searching thru a .dbf file containing a few millions of records would just take me a split of milisecond, but opening a recordset in visual basic as in visual foxpro would deadly take me 10-100 seconds, 2000 times slower than visual foxpro.

Any comment, pls kindly advise, thanx a lot.

[Iouri]

From my experience SQL is always faster then Find. Also speed depends on indexes on the fields.

Iouri Boutchkine
[email protected]

[James Longstreet]

In addition to the previous post; the way you open a recordset has an affect on performance. If you open as readonly it will be faster, if you open as forward only, it will be faster....if your SQL statement selects exactly what you want, it will be faster, In other words if you want customers whose id is 110, a "SELECT * FROM Customer WHERE Id = 110", is going to be much faster than selecting all records and then filtering and sorting.



Jim Hewitt
Software Developer
Liberty Tax Service
www.LibertyTax.com

[aknudsen]

Indexes on the database is an excellent point and easily forgotten.

[Shella]

Hi
I really wonder if ADO did make sure of Indexes as what the seek method in visual foxpro did ?
I'm really impressed on the performance of Visual Foxpro in searching for an indexed record thru the seek method or indexseek function, really amazing and very faster, till now, I didn't realize that ADO also rely on indexed field for searching, how do make sure of it ? thanx a lot.