ODBC vs OLE DB (ADO)

ODBC vs OLE DB (ADO)

Post by Seth Anders » Sun, 18 Jul 1999 04:00:00



Here's an interesting programming question; there are three dimensions
here, so bear with me.

I have one particular view in SQL Server that takes two tables (one
consisting of about 15 records and the other a few hundred thousand
records), inner join them, count the records, and group by one field.
It seems, under Enterprise Manager, that this query is quite
intensive, since it usually times out.

As a test, I went into MS Access 2000, created a new database,
connected to the SQL server via ODBC, linked the two tables, and
created the same query.  The results are generally returned in under a
few seconds.

I've been writing an interface with Visual Basic 6, using the SQL
Server OLE DB provider (through ADO).  (I'm under the impression that
Enterprise Manager also uses OLE DB because the same results in Visual
Basic occur.  I finally had to resort to setting the CommandTimeout
property on the ADODB Connection to 0, just so it would wait until
finished.)

I've never throught (from what I've heard) that ODBC was fast nor
efficient.  Can someone explain to me why ODBC is faster than OLE DB
or is this case an exception to the rule?

Thank you kindly,

Seth

 
 
 

1. ODBC vs OLE DB (ADO)

Here's an interesting programming question; there are three dimensions
here, so bear with me.

I have one particular view in SQL Server that takes two tables (one
consisting of about 15 records and the other a few hundred thousand
records), inner join them, count the records, and group by one field.
It seems, under Enterprise Manager, that this query is quite
intensive, since it usually times out.

As a test, I went into MS Access 2000, created a new database,
connected to the SQL server via ODBC, linked the two tables, and
created the same query.  The results are generally returned in under a
few seconds.

I've been writing an interface with Visual Basic 6, using the SQL
Server OLE DB provider (through ADO).  (I'm under the impression that
Enterprise Manager also uses OLE DB because the same results in Visual
Basic occur.  I finally had to resort to setting the CommandTimeout
property on the ADODB Connection to 0, just so it would wait until
finished.)

I've never throught (from what I've heard) that ODBC was fast nor
efficient.  Can someone explain to me why ODBC is faster than OLE DB
or is this case an exception to the rule?

Thank you kindly,

Seth

2. OLAP Connection pooling

3. ODBC Vs ODBCDirect Vs ADO Vs OLE/DB advise

4. corruption causes

5. ODBC vs ADO/OLE DB

6. ReFox and passwords

7. ADO: SQL OLE DB vs. ODBC

8. SQL2k & SP2 & 'network packet size'

9. OLE DB for SQL vs. OLE for ODBC

10. ADO/OLE DB vs Native vs RDO - Oracle

11. OLE DB vs ODBC for Oracle DB access

12. OLE DB vs. ADO

13. ADO vs OLE DB.