• Home
  • Modeling for BI
  • DBA
  • Azure
  • SSIS
  • SSAS
  • SSRS - Reporting
  • PBI - Analytics
  • Consulting
  • About
Microsoft Data & AI

SQL Server Integration Services

Performance Testing OLE DB vs ADO.NET in SSIS

7/12/2017

4 Comments

 
I always "knew" that ADO.NET was slower than OLE DB destination SSIS components, but I had never tested it.  This month I was testing a 3rd party SSIS destination component that had been built on ADO.NET and, oh my (!!).  The performance was substantially slower than what I could get done with a traditional OLE DB upsert process.  However, before I told the client, "Abandon ship!  Every SSIS package for itself!", I decided to try a few simple tests.  Here are my results of my second test, an upsert (insert new rows and update existing rows):
​
Environment
  1. (local) SQL Server 2016 v.13.0.1782.2
  2. (local) Visual Studio 2015 v.14.0.25431.01
  3. Source table contains 12,243,275 rows and was 31.1 MB on disc
  4. Source and destination tables had identical column structures
    • Tables contained 33 columns with total max row length of 181 bytes (information taken from sys.columns)
    • There was a PK in both the dbo.SourceTable and dbo.DestinationTable.  dbo.DestinationTableForUpdate contained no PK
    • There was no FK, additional constraints or indexes on any table
  5. dbo.DestinationTable was manually truncated (test #1) or half the rows were deleted (test #2) before each test and was not part of the test time results
  6. All tests were executed through Visual Studio 2015 unless noted otherwise

Test: Insert and Bulk Update, or Merge()
Setup: 6,121,637 rows were removed from dbo.DestinationTable prior to each test.  Consequently, these tests inserted 6,121,637 rows and updated 6,121,638 existing rows.

Results:
Picture
1.  This was a typical t-sql MERGE() statement with no DELETE and no output into a result table.
2.  The same MERGE() statement executed through SSMS in #1, was executed through a SSIS Execute Sql task
3. An old-fashioned OLE DB upsert process:  If key found, insert to DestinationTableForUpdate, if new, insert into DestinationTable.  An execute Sql task followed the data flow to UPDATE DestinationTable with values found in DestinationTableForUpdate.
4.  Rinse and repeat #3 using ADO.NET components
5. Third party upsert component which handles the insert and update in one beautiful simple ... easy ... low maintenance ... any-entry-level-SSIS-developer-can-do-it task.  (I really wanted to use this SSIS add-in!)


Conclusion:
OLE DB has better performance, and because the nifty 3rd upsert task is built on ADO.NET, I won't be using it nor recommending it for use in anything other than a small to mid-size company with small MB incremental loads.  Bummer!  The good news is that I now have a benchmark and if Microsoft improves ADO.NET in future releases on SQL Server, I'll be pulling out my POC database and hoping for better results.  (p.s. If you are wondering why I will not recommend sacrificing the permanent daily ETL load on the alter of an immediate faster ETL development, please see my BI Wheel.  ETL is not the center of the wheel.)
4 Comments
Ann Weber
8/28/2019 04:56:16 pm

Thank you for sharing these results. Did you happen to test the difference between ADO.NET and OLE DB in the Execute SQL task? I've long found OLE DB to be the overall better option when given the choice, but it keeps going in and out of favor. I love that it was just "Undeprecated".

Reply
Emery Duncan link
2/28/2021 02:01:20 am

This was great to read, thank you

Reply
James
4/19/2022 03:51:07 pm

Is there a reason you ran these tests from within Visual Studio?

Reply
GFE Escorts Tamworth link
3/16/2025 03:48:00 am

I find the comparison of different methods for upsert operations in SQL Server to be very informative.

Reply



Leave a Reply.

    Categories

    All
    Best Practices
    Design
    Error Handling
    SCD2
    SSIS

    RSS Feed

    Tweets by @SqlServerSME
Powered by Create your own unique website with customizable templates.
  • Home
  • Modeling for BI
  • DBA
  • Azure
  • SSIS
  • SSAS
  • SSRS - Reporting
  • PBI - Analytics
  • Consulting
  • About