Compare ADO.NET and OLE DB

转载 2015年07月09日 17:24:21

1.     support across SSIS components:

a.     SSIS is not welcoming ADO.NET and OLE DB connectors equally across the board. Although this is more so in SQL Server 2005, we are making steps to close the gap in Katmai in between the two. For instance, there is no ADO.NET destination component in Data Flow, and the Lookup component only supports OLE DB. OLE DB is by far the most supported connectivity option across the board. For a full list of supported connectivity options and SSIS components, see my article on the subject:

2.     performance

a.     Performance is very much dependent on the computing environment and the data and network load levels in your scenario. We highly recommend slicing your end-end scenario into isolated sub scenarios to see where the performance bottleneck is. RowCount component comes particularly handy where you can replace source and destination components with. By replacing the connectors with the RowCount component, which does not incur any I/O and frees memory as an inline component, you are indeed collecting benchmark performance numbers with and without the connector. This will give you the time the connector spends reading/writing the data.

b.    That being said, OLE DB, for most cases will have better performance than ADO.NET. This is due to ADO.NET being a managed façade, and providing more abstraction with a little more performance overhead.

3.     64bit considerations

a.     Since ADO.NET is managed, the deployment of packages and moving them around 32bit and 64bit boxes will be easier compared to the experience with OLE DB providers. SSIS design time is a 32bit application because of the dependency on Visual Studio, and has to work with 32bit connectors. However, during runtime, you have the option to choose a native 64bit or a 32bit connector. Please read my article on 64bit and why things are the way they are here:

b.    Some OLE DB providers are 32bit only (i.e. Office connectivity, JET and ACE providers), whereas others support both architectures (i.e. Oracle’s own OLE DB provider for Oracle). To work in a seamless 64bit environment, make sure your provider has both binaries.

c.     For an OLE DB provider to be available only in 32bit (i.e. JET), does not necessarily mean that you cannot run it on 64bit machines. You can, but it will be in WOW64 emulation mode. The only downside to that is if you need the 64bit address space of huge memory (i.e. greater than 4GB). Otherwise, it’s not a problem.

4.     target data source & data type supportability

a.     Connectors, whether they are ADO.NET or OLE DB providers, don’t support a specific target database/application system equally. For instance, the OLE DB providers for DB2 are all different in the way they support different flavors of DB2. Some support DB2 on Linux, others support DB2 on AS/400, etc…For a good list of connectors and what actually they support, please see the table we are compiling in our connectivity wiki :

b.    Another key support question is whether these connectors support a specific version of the target database and how good they are in dealing with a recently introduced data type. For instance, Microsoft’s Oracle OLE DB provider was not recently updated to fully support new data types in Oracle 10g +…However, Microsoft’s ADO.NET provider for Oracle does a better job with Oracle data types. Again, our connectivity wiki is a good place to start and contribute to this very organic structure of the connectivity realm.


一、ODBC ODBC的由来 1992年Microsoft和Sybase、Digital共同制定了ODBC标准接口,以单一的ODBC API来存取各种不同的数据库。随后ODBC便获得了许多数据库厂...
  • xcymorningsun
  • xcymorningsun
  • 2016年11月08日 16:32
  • 1349

SSIS数据流性能比较(ADO.NET vs. OLE DB vs. ODBC)

选择哪一种数据库连接方式,这个问题在开发者社区当中一直争论不休。 性能通常被视作首要的鉴别标准。一般来讲ADO.NET的性能不如ODBC与OLE DB,因为他是一个托管提供程序。但是由于大多数应用只...
  • u012988208
  • u012988208
  • 2015年07月19日 23:29
  • 1546

Sybase OLE DB独立驱动下载与安装

201023 六月Sybase OLE DB独立驱动下载与安装by GlobeTourPosted in 编程开发, Sybase, 驱动Add comments (3) | Permanent li...
  • yanchao1023
  • yanchao1023
  • 2011年02月16日 17:48
  • 1867

通过OLE DB连接SQL数据库的两种方法

通过OLE DB连接SQL数据库的两种方法重新了SQL Server2000后,用ASP连接数据库轻松多了。我查了一下几本资料,发现连接SQL Server的代码写法有两种,就拿我建的数据库,名称为“...
  • ruhender_vulkan
  • ruhender_vulkan
  • 2008年05月19日 09:49
  • 1136

数据库连接技术之OLE DB

之前的博客介绍了ODBC和JDBC,这次简单的介绍一下OLE DB。ODBC的总结不知道是没贴到博客上还是不在这个博客上,我再找找,没有的话我再补充到时候。好了,开始吧。 回顾     之前呢...
  • zhuojiajin
  • zhuojiajin
  • 2014年07月25日 21:38
  • 1987


PB9.0(含)前的版本都由提供MSSQL server 的专用接口但PB10之后,这个接口就被取消了,改而使用ole db或者odbc接口来连接database举个例子//MSSQL Server的...
  • SinBen
  • SinBen
  • 2007年12月16日 17:27
  • 3340

用于 Oracle 的 Microsoft OLE DB 提供程序

访问和更改关系数据用于 Oracle 的 Microsoft OLE DB 提供程序用于 Oracle 的 Microsoft OLE DB 提供程序使分布式查询得以查询 Oracle 数据库中的数据...
  • nomad2
  • nomad2
  • 2007年04月06日 20:34
  • 2028

使用OLE DB连接各种类型的数据库的连接字符串

SQL Server 使用 OLE DB 所设置的连接字符串:标准连接方式Provider=sqloledb;Data Source=datasource;Initial Catalog=DbName...
  • promaster
  • promaster
  • 2007年10月20日 23:57
  • 3170

一、使用 Microsoft OLE DB Provider For ODBC 链接MySQL

 一、使用 Microsoft OLE DB Provider For ODBC 链接MySQL安装MySQL的ODBC驱动MyODBC1、为MySQL建立一个ODBC系统数据源,例如:选择数据库为t...
  • cxzhq2002
  • cxzhq2002
  • 2008年01月07日 12:04
  • 3839

SSIS高级转换任务—OLE DB命令

OLE DB Commond任务接受输入流中的行数据并执行一段SQL语句或自定义存储过程。这个转换和Execute Sql任务容易混淆,但是它非常类似于在ADO连接中循环接收一个结果集中的数据,创建,...
  • kuui_chiu
  • kuui_chiu
  • 2012年03月05日 16:40
  • 665
您举报文章:Compare ADO.NET and OLE DB