ADO vs. ODBC Performance
Vaughn Haybittle
I have recommended to a colleague that using the native provider to connect
to a data source is faster than accessing the datasource using the OLE BD
provider for ODBC.
My colleague has always used ODBC connections and is asking what performance
improvement could be realised. I have no idea (although I am tempted to say
33% as there is one less layer in the process.). and am wondering if there
is anyone out there that could give me some sort of idea, maybe based on
experience. I am sure this is one of those "how long is a piece of string"
questions, however any comments would be welcome!
Regards
Vaughn
Maya Roselip
Vaughn,
I believe that if the data you're accessing
does not have an OLE DB provider, you
aren't actually losing a layer...the OLE DB
provider for ODBC will make use of the
ODBC driver available for your data. I've
been using ADO to access Visual FoxPro
data for over a year now. It has to use
the VFP ODBC driver since an OLE DB
provider is not available, yet (VFP 7 :).
I don't see a drop in performance versus
using ODBC directly - but, I don't see any
improved performance, either. I would
assume that you won't see the major benefits
of OLE DB unless you have an OLE DB
provider for your data.
Sincerely,
Maya Roselip
Vaughn Haybittle <vhayb...@konnectsoft.com> wrote in message
news:pNny5.581$He3....@news2-win.server.ntlworld.com...
Michael D. Long
You are also making an assumption that
the native OLE DB provider is optimized
as well as the comparable ODBC driver.
As the OLE DB provider is typically less
mature, this can be a *big* assumption.
In an ideal world, the OLE DB provider
would always work better than an ODBC
driver + the OLE DB provider for ODBC.
However, we live in the real world.
Have you ever considered running a few
simple tests? It really doesn't take that
much time, and then you don't have to
accept second hand opinions.
Just a thought...
--
Michael D. Long
http://extremedna.homestead.com
Eggie
Since ADO uses a COM object model suited for VB, The overhead with COM
pointers going from VB to ADO to OleDB is slightly there...
If you are insane :) and you have to much time left for you project you
could program the ODBC32 api directly using VB. The performance - wil - in
most cases be faster. Think about some 25% for heavy VB loops.
Think about the 25% performance win and the 400% time losses you'll get.
Just compare...
This is surely true when I talk about SQL server or Oracle but when you
speak about non DBMS-es like FOx Pro and Access the performance diff is not
measurable for reasons I want to spare my and your time for now..