2

We are researching the performance difference between SSIS Lookup and T-SQL Join. We want to join two tables; tables are in the same SQL server instance, different databases.

I suspect for small table joins, the difference is minimal or negligible. In this case, our team prefers T-SQL, easier to code/script than writing diagrams. Additionally in the DevOps perspective, we can compile/build scripts in a DB project; unfortunately, SSIS will not compile T-SQL correctly, I can write 'testabcd' in an SSIS Execute SQL statement, and the project will still build/compile.

However, for a large number of rows which will take longer processing, what is quicker? T-SQL which has indexes and statistics, or SSIS which is conducted all in memory?

I read these articles with different viewpoints, the team is trying to gain consensus.

https://derekdb.wordpress.com/2012/03/13/ssis-lookup-or-t-sql-join/ http://www.sqlservercentral.com/blogs/jamesserra/2011/08/29/when-to-use-t_2D00_sql-or-ssis-for-etl/

Let's assume, T-SQL Engine and SSIS are provided same hardware: CPU and Memory. Given the same specs, I would like to know performance speed on an internal algorithm perspective.

|improve this question
  • 1
    I suspect the difference is going to be largely dependent on your situation. Is SSIS running on a dedicated box? What are its specs? What is a "large" number of rows? What are your package settings/configs? I suggest you just test the two to compare. I don't see any reason why SSIS shouldn't be able to perform up to snuff when configured properly. – LowlyDBA Oct 4 '18 at 19:39
  • 1
    One key is, indeed, having enough memory to have all the look-up data in memory. If you cannot pre-load the lookup data, but have to read it in for each time through, SSIS Lookup is basically terrible (in my experience). – RDFozz Oct 5 '18 at 15:40
7

Given the same specs, would like to know performance

If your data is on a single SQL Server instance, TSQL joins should always be faster than an SSIS lookup. This is not really close. A TSQL query will leverage indexes and statistics, and the cost-based query optimizer. The join will be performed inside the SQL Server instance, where it is not only more efficient, but it has greater memory and disk resources than an SSIS pipeline.

SSIS lookups are primarily intended for destination-side lookups, heterogenous data scenarios, and for sources without a query processing engine (like flat files).

Further, when your data source is a single SQL Server instance, and your destination is a single (possibly different) SQL Server instance, personally I would almost never use any SSIS data flow transformations. Instead always using source-system SQL for extract-side transformation, and stage-and-merge for destination-side transformation.

|improve this answer
  • 1
    Totally agree on all those points, but where do things begin to tip when it comes to dealing with an active workload on SQL Server vs an SSIS instance that is, by comparison, much more "single use" oriented? – LowlyDBA Oct 4 '18 at 20:04
  • 4
    It's hard to say, but many times attempts to "offload" work from SQL Server end up being no cheaper, as you need more data at the client, and reading lots of rows and sending them to the client is not free. – David Browne - Microsoft Oct 4 '18 at 20:08
1

I think you misunderstand the purpose of Integration Services entirely. Using the wrong tools may still get the job done, but that is not efficient or very wise.

The following explains some of the core reasoning:

Why use SSIS

  1. prepackaged code for working with disparate sources (Oracle, MySQL)

  2. Great for large ETL between heterogenous sources (and great for large insertions i.e. Bulk)

  3. Is independent of an Instance. Portable.

  4. Cumbersome to change by design.

Why use TSQL (procedures)

  1. Easy to change
  2. Pre-recorded statements that are series of sql statements
  3. live inside the Instance. Hard to transport.
  4. Designed for smaller operations.
|improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy