Which is faster? 3

[sjulian]

I am accessing a SQL2000i database using VB6 code with an ADO control. I can get my recordset using 2 methods, seek on an index, or opening the recordset with a SQL statement. All other things being equal, which is the fastest method?

 

[TomKane]

There are two main access methods Transactional and Relational - Transactional is by far and away the quickest.

 

[Vancouverite]

In 2000i, SEEK (transactional access) is always faster for individual record lookups.

Note that if you are using client/server access to a remote DB, a SQL call through the ADO driver will frequently be **significantly** slower than the same call through the ODBC driver when doing JOINs, if you have any filtering on the recordset (e.g. WHERE Table1.X = 1 AND table2.Y = 'bob'). This is because the 2000i ADO driver performs the SQL level filtering on the client, not the server. Thus, a huge chunk of records may be passed to the client for filtering. This is not an issue with the Version 8 ADO driver.

 

[sjulian]

Thanks a million,
I'm still trying to figure all of this data access out. How do I make the call through ODBC rather than ADO?

 

[sjulian]

A note to clarify the above, I want to avoid using a 3rd party ODBC driver. Is there a native way to make an ODBC call?

 

[mirtheil]

Native and ODBC are not usually mentioned the same sentence. If you're using Pervasive.SQL 2000i or Pervasive.SQL V8, the ODBC driver included with the engine is as close to "Native" as you're going to get.

info@mirtheil.com
Custom VB and Btrieve development.
Certified Pervasive Developer
Certified Pervasive Technician