ODBC vs OLE DB (ADO)

ODBC vs OLE DB (ADO)

Post by Seth Anders » Tue, 20 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

 
 
 

ODBC vs OLE DB (ADO)

Post by Lotha » Wed, 21 Jul 1999 04:00:00


Have you examined the execution plan for the query in Query Analyzer? It
might give you some ideas on how to improve the speed of the query. As for
the Access results, I believe they are skewed because Access caches the data
locally and performs some local execution of the query. If you are using SQL
Server 7, execute the query using Query Analyzer and examine the execution
plan created by the server. If it's SQL Server 6.5, you can execute the
query in ISQL/w.


Quote:> 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. SetFilter on Lookup Table

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

4. Space Allocation ( SQL Server 6.5 )

5. ODBC vs ADO/OLE DB

6. update table a from table b

7. ADO: SQL OLE DB vs. ODBC

8. How to copy rows (without Identity column)

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