[원문]   http://ssis.wik.is/Understanding_Connectivity_Stacks/ADO.NET_vs_OLEDB
OLE DB가 ADO보다 빠르다? ... 딜레마에 빠지는 ...........

An often asked aspect of SSIS is which data source/destination provider to use when connecting to various databases and data sources. In my previous articles, I explained what these connectivity libraries are and how SQL Server Integration Services supports them across the product.

In this article, I will try to provide guidance for when to choose various connectivity options when building SSIS packages. To better structure the pros and cons of using either ADO.NET or OLEDB, I will outline my arguments in terms of product-wide support, performance, 64-bit considerations, and target data source version and type-system supportability.

ADO.NET vs OLEDB : SSIS support across the product

• First thing to note here is that 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 (SQL Server 2008) in between the two. For instance, there is no ADO.NET destination component in our ETL engine 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: Connectivity_Libraries

ADO.NET vs OLEDB : Performance

• Performance is very much dependent on the computing environment along with 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.

• 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.

ADO.NET vs OLEDB : 64bit considerations

• Since ADO.NET is a managed interface, the deployment of packages and moving them around 32bit and 64bit boxes will be easier compared to the experience with OLE DB providers. By now, you must have known that SSIS design time is a 32bit application because of the dependency on Visual Studio, which forces it to work with 32bit connectors only. 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: 64-bit_Story

• Some OLE DB providers are 32bit only (i.e. MS Office connectivity via JET and ACE OLEDB 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 and are available on your deployment environment.

• That being said, an OLE DB provider being available only in 32bit (i.e. JET), does not necessarily mean that you cannot run it on 64bit platforms. This can be done using the WOW64 emulation mode, which is Windows support for running 32bit applications on 64bit architectures. The only downside to using WOW64 is if you need the 64bit address space which comes with massive memory (i.e. greater than 4GB). Otherwise, it’s not a problem.

ADO.NET vs OLEDB : Target data source & data type supportability

• 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: Data_Sources

• 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, this wiki is a good place to start and contribute to this very organic structure of the connectivity realm.

We also have a white paper on connectivity which covers other aspects of this subjst, it’s a good read: Connectivity_White_Paper 

Deniz Erkan - msft