locked
OLE DB VS ADO.NET RRS feed

  • Question

  • I think this is a question you could find on every .NET related forum.

    But I'm going to throw it: Which connection type should I use? ADO .NET or OLE DB

    I know in the past ADO was just a layer on top of OLE DB so it was better to use OLE DB directly. Nowadays, with ADO .NET, that isn't the case anymore or am I totally wrong?

    The reason why I'm asking this is because I'm considering to switch from OLE DB connection to ADO .NET because I have nothing but troubles with parameter mappings and wrong values inserted in the database because of data type conversions. When I use ADO.NET, everything works fine.

    What about performance? A fable or the truth? In my testing environment I work with small datasets but in production this won't be the case.

    Is there a good article somewhere out there on this subject?

    Thanks in advance,
    Tom

    Tuesday, September 12, 2006 9:49 AM

Answers

  • ADO.NET vs OLEDB:

     

    I'll try to answer this question by slicing it into 4 main areas:

     

    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: http://ssis.wik.is/Connectivity_Libraries

    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: http://ssis.wik.is/64-bit_Story

    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 : http://ssis.wik.is/Data_Sources

    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.

     

    We also have a white paper on connectivity which covers other aspects of this comparison, it’s a good read: http://ssis.wik.is/Connectivity_White_Paper

     

    Hope this helps,

    Deniz

     

    Tuesday, June 26, 2007 7:22 PM

All replies

  • Tom,

     

    I'm currently in a training session for SSIS, and this very question has be asked by one of our Senior Database developers.  I is inclined to use ADO.Net because it is explicit with the named variables.  Our instructor then pointed out that if the variable name changes in the stored procedure then you break your package.  He also suggested that if you abstract your variables to a table, then you could avoid that issue.

     

    My question is the performance between the two.  Is ADO.Net just a wrapper for OLE DB?  Or is it an independent environment?

     

    Brandon Forest

    Database Administrator

    Data & Web Services

    Sutter Connect IT

    foresb@sutterhealth.org

    Wednesday, May 30, 2007 5:39 PM
  • Hi Brandon,

     

    I've read a lot about this and nobody seems to know the answer.

    Performance isn't the issue apparently.

    Allthough Microsoft pushes developers towards ADO.NET (code examples on MSDN usually use ADO.NET), I for myself am tended to use OLE DB, this because of the simple fact they seem to have forgotten about ADO.NET in SSIS designer (OLE DB Source component - but nothing like ADO.NET Source Component)

     

    Greets,

    Tom

    Monday, June 25, 2007 1:25 PM
  • [Microsoft follow-up]
    Monday, June 25, 2007 1:34 PM
  • There isn't a single answer that's correct in all cases.  As others have pointed out, there are differences between ADO.Net and OLE DB that lead to slightly different strengths and weaknesses, but performance is much more strongly determined by the provider than the interface.  I.e., two different OLE DB or two different ADO.Net providers for the same data source can demonstrate significantly different performance in only slightly different scenarios.

     

    So, if you really want a meaningful answer, you have to ask, "Should I be using OLE DB or ADO.Net in this particular scenario?"  The best way to answer that question is with some prototyping.

    Tuesday, June 26, 2007 4:21 PM
  • ADO.NET vs OLEDB:

     

    I'll try to answer this question by slicing it into 4 main areas:

     

    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: http://ssis.wik.is/Connectivity_Libraries

    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: http://ssis.wik.is/64-bit_Story

    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 : http://ssis.wik.is/Data_Sources

    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.

     

    We also have a white paper on connectivity which covers other aspects of this comparison, it’s a good read: http://ssis.wik.is/Connectivity_White_Paper

     

    Hope this helps,

    Deniz

     

    Tuesday, June 26, 2007 7:22 PM
  • Thanks Deniz.

    Phil
    Tuesday, June 26, 2007 7:27 PM
  • Many thanks to all of you

     

    -Tom

    Friday, July 6, 2007 3:18 PM
  • Hi Deniz,

    Your wiki site is decommissioned. Have you migrated it to another site?

    Thanks

    Jason

    Thursday, January 27, 2011 12:14 AM
  • It seems this question was raised well before Microsoft announced that OLEDB for SQL Server will be deprecated... (If they don't change their mind)

    Recently I made some simple tests (more related to data warehouse and SSIS) and it seems .net sqlclient is something I might stick to if I performance is not that critical... time will show if that was the right decision.

    OLEDB vs ado.net vs ODBC simple read performance test

    OLEDB vs ado.net vs ODBC simple write performance test

    Take care

    Emil

    Friday, December 14, 2012 2:32 PM
  • It seems this question was raised well before Microsoft announced that OLEDB for SQL Server will be deprecated... (If they don't change their mind)

    Recently I made some simple tests (more related to data warehouse and SSIS) and it seems .net sqlclient is something I might stick to if I performance is not that critical... time will show if that was the right decision.


    Take care

    Emil

    Nice demostration, I´ll try it now.
    Tuesday, August 19, 2014 1:52 PM
  • Hi,

    as far I have tested this with OLE DB or ADO,  setting the values in the boxes do not do anything (for my case of course)  :-)

    It looks like for big data set it always send batches of 10000 rows at the time when running in developer mode.

    Nice thread !

    Tx

    Jose  

    Friday, October 31, 2014 2:12 AM