Tuesday, March 27, 2012

Are all DSN's really ODBC?

I'm still trying to learn the alphabet soup of MS's data access methods, but
one thing I just noticed is that the Data Sources control panel is called
"Data Sources (ODBC)". Is this accurate? If so, should I *not* use DSN's in
my code? I am under the impression that ODBC is considerably slower than
OLEDB or other technologies (although what the differences are I don't really
know).
For instance, I have my code literally sprinkled with little ADO lookups
that use a single connection string...
"ODBC;DATABASE=OurDB;DSN=SQL Server"
Removing the "ODBC" does nothing, which I assume is because it's not a
key/value pair. But it still uses a DSN to look up the connection, so does
this mean every query I run using this connection string uses ODBC?
If so, should I really expect any sort of real-world performance boost if I
use a new string, one like...
"Provider=sqloledb;DATABASE=OurDB;DSN=SQL Server"
Is there an even better provider to use?
It's all so confusing!!
Maury
Why use DSN's at all? Go DSN-less. You just need to tack a few things onto
the connection string.
Head to www.connectionstrings.com for more info.
Keith
"Maury Markowitz" <MauryMarkowitz@.discussions.microsoft.com> wrote in
message news:3486DEAD-166D-43C5-A2C7-7B37F66EC392@.microsoft.com...
> I'm still trying to learn the alphabet soup of MS's data access methods,
> but
> one thing I just noticed is that the Data Sources control panel is called
> "Data Sources (ODBC)". Is this accurate? If so, should I *not* use DSN's
> in
> my code? I am under the impression that ODBC is considerably slower than
> OLEDB or other technologies (although what the differences are I don't
> really
> know).
> For instance, I have my code literally sprinkled with little ADO lookups
> that use a single connection string...
> "ODBC;DATABASE=OurDB;DSN=SQL Server"
> Removing the "ODBC" does nothing, which I assume is because it's not a
> key/value pair. But it still uses a DSN to look up the connection, so does
> this mean every query I run using this connection string uses ODBC?
> If so, should I really expect any sort of real-world performance boost if
> I
> use a new string, one like...
> "Provider=sqloledb;DATABASE=OurDB;DSN=SQL Server"
> Is there an even better provider to use?
> It's all so confusing!!
> Maury
|||"Keith Kratochvil" wrote:

> Why use DSN's at all?
That's what I'm asking.
Maury
|||yes.
Choose an OLEDB Provider and do not use a DSN.
OLEDB is going to be faster particularly when you choose the native provider
for your target platform (Example..."SQL Server")
Greg Jackson
PDX, Oregon
|||In addition tot he other comments:
ODBC is a programming API. A low-level API. There exists higher level abstractions such as the RDO
object model on top of ODBC. ODBC uses a driver to connect to the DBMS.
OLEDB is a programming API. A low-level API. There exists higher level abstractions such as the ADO
object model on top of OLEDB. OLEDB uses a driver to connect to the DBMS. There exists an OLEDB
driver which sits on top of ODBC. This is probably what you are seeing.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Maury Markowitz" <MauryMarkowitz@.discussions.microsoft.com> wrote in message
news:3486DEAD-166D-43C5-A2C7-7B37F66EC392@.microsoft.com...
> I'm still trying to learn the alphabet soup of MS's data access methods, but
> one thing I just noticed is that the Data Sources control panel is called
> "Data Sources (ODBC)". Is this accurate? If so, should I *not* use DSN's in
> my code? I am under the impression that ODBC is considerably slower than
> OLEDB or other technologies (although what the differences are I don't really
> know).
> For instance, I have my code literally sprinkled with little ADO lookups
> that use a single connection string...
> "ODBC;DATABASE=OurDB;DSN=SQL Server"
> Removing the "ODBC" does nothing, which I assume is because it's not a
> key/value pair. But it still uses a DSN to look up the connection, so does
> this mean every query I run using this connection string uses ODBC?
> If so, should I really expect any sort of real-world performance boost if I
> use a new string, one like...
> "Provider=sqloledb;DATABASE=OurDB;DSN=SQL Server"
> Is there an even better provider to use?
> It's all so confusing!!
> Maury

No comments:

Post a Comment