Fog Creek Software
Discussion Board




Fast, OLEDB database connection instead of ODBC.

I noticed this sentence while reading about what is new in FogBugz 3.0.

Anyone want to expand on this sentence?

Thanks!

I'm Tired and can't get work done.
Monday, November 04, 2002

Supposedly there's less of an abstraction layer between your code & the db when using OLEDB as opposed to ODBC.  In my tests there wasn't much of a difference, and I do some fairly high performance server applications (billing).  I doubt you would notice much difference when dealing with a bug database. 

Of course, there's the 'coolness' factor of getting to use the newest technology. 

...
Monday, November 04, 2002

It also saved us some headache because we don't need to create and maintain a DSN.

Joel Spolsky
Monday, November 04, 2002

You don't need a DSN if you use SQLDriverConnect().

...
Monday, November 04, 2002

> Of course, there's the 'coolness' factor of getting to use the newest technology. 

Ummm, nice troll. OLEDB is hardly new.

Damian
Tuesday, November 05, 2002

Heck, I thought it was new.  Of course, I still consider Java to be a new technology, so our perspectives may differ. 

That said, I don't think there's anything wrong with learning a new way of doing things.  Indeed, I'm learning C# even though C++/MFC can do all the things I already need to do.

Or maybe you were trying to troll me.

...
Tuesday, November 05, 2002

Have a look at

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnbda/html/bdadotnetarch031.asp?frame=true

Just me (Sir to you)
Tuesday, November 05, 2002

Will OLEDB still allow us to connect to Postgres on a Linux box?

anon
Tuesday, November 05, 2002

If there is no OLEDB provider for Postgres, you could use the "OLEDB provider for ODBC" (a wrapper of sorts).

There may be a Postgres OLEDB provider. I know there is one for MySQL: http://sourceforge.net/projects/mysqldrivercs.

ns
Tuesday, November 05, 2002

Sorry that is not an OLEDB provider but a MySQL interface for .NET. Still, you could use "OLEDB provider for ODBC".

ns
Tuesday, November 05, 2002

I also have yet to be sold on the real performance increases of OLEDB vs a simple ODBC connection. As long as the queries are passthrough, then the difference below 100 users does not really seem to be such a big deal.

Perhaps computer performance is so good these days, or I never work with enough users on a system to ever really see the performance benefits.

This debate of using ADO (which is a oleDB provider) vs say using JET via ODBC to sql-server has never seemed to really show a lot of difference.

Certainly a ADO (oleDB) connection is a better way to go since you then ensure that no local data engine causes extra overhead, or makes a bad decision on how data should be retrieved.

However, most of the performance problems when working with a database is going to be design, and good coding practices. Perhaps oleDB connections encourage better designs (like 3 teir desings vs 2).

Albert D. Kallal
Edmonton, Alberta Canada
Kallal@msn.com

Albert D. Kallal
Thursday, November 07, 2002

The big advantage to OLEDB over ODBC is that it's a lot easier to write an OLEDB provider than it is to write and ODBC driver, and OLEDB will let you access non-relational data without distorting things too much.

If you're using ADO to access the database, you should be using an OLEDB provider rather than ODBC. That's because ADO is just a wrapper around OLEDB.

So, if you have and OLEDB provider the layers are:

App -> ADO -> OLEDB -> Database

If you're using ODBC, you've got:

App -> ADO -> OLEDB -> ODBC -> Database

So the ODBC just adds another layer to the mix.

Chris Tavares
Thursday, November 07, 2002

Anyone know of an OLEDB provider for MySQL that supports VB6.0 (not VB.Net)?

Kevin
Tuesday, January 20, 2004

One advantage ODBC still has is connection pooling that OLEDB can't offer in certain situations.

Basically, using ADO you go through that extra layer via OLEDB Provider for ODBC.  You first turn off OLEDB connection pooling for the ODBC driver you want pooling on (registry setting).

Then use the ODBC Data Source Administrator control panel applet to set up connection pooling for that driver (say MS Access).

You don't need this for certain classes of applications like ASP pages (IIS takes care of some details - you just need to follow the rules of the road).  You don't need it for MTS applications.  You don't need it as long as a connection is always kept open, etc...

What you'd want it for is something like, oh, say CGI scripts or EXEs.  This keeps the database connections open and improves performance radically.  Look as something like a small site based on Simpleserver:WWW and some VB CGI EXEs and an Access database.  You don't REALLY want to be opening and closing that database every CGI "page hit" do you?

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnmdac/html/pooling2.asp

Or have I misinterpreted things (including my own experiences)?

OLEDB is generally better, ODBC is a "dead horse" in most ways, but in my experience this is one important exception for a narrow class of situations.

Bob Riemersma
Friday, March 12, 2004

BTW:  If you want to do as I suggest above, take a look here first:

http://support.microsoft.com/default.aspx?scid=KB;EN-US;Q295297&ID=KB;EN-US;Q295297

This points out one common ADO->OLEDB->ODBC headache, and its resolution.

Bob Riemersma
Friday, March 12, 2004

*  Recent Topics

*  Fog Creek Home