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便获得了许多数据库厂...


面对各式各样、越来越多的数据来源和访问需求。软件开发框架中一般都提供了统一的访问接口和方法,来屏蔽数据库底层差异。 各式各样的Provider提供者。 ODBC(Open Database Con...




OLE OLE是Object Linking and Embedding,对象链接与嵌入技术,比如我们在Word中插入一个Excel图表,用的都是OLE技术; ActiveX其实说起来是OLE2....

ADO.Net Linq to SQL and Linq to Entities Note

**Linq to SQL Database Context                 -Log    &...

ADO.Net Connectivity using Stored Procedure and Output parameters

原文: Create ...