4

I'm currently designing a SSIS package, which will do following:

  • Clear unwanted records in destination
  • Do ETL using stored procedures and put everything into a table prepared for export in source
  • Inserts data from source to destination

So the final step is pretty much to transfer whole table data from Source into Destination and I've got two options now:

  • SSIS Data Flow
  • Linked Server

Ideally I'd like to use Data Flow task in SSIS, but looking at performance comparison, it looks like Linked Server wins. I'm doing it that way:

In SSIS I do the following

  • Run a select statement using OLE DB Source
  • Insert into destination using OLE DB Destination
  • Takes about 4 seconds when deployed, about 10 seconds on my machine

enter image description here

Using Linked Server I do the following

  • Insert records into #Table querying remote server
  • Insert into actual table
  • Takes about 2-4 seconds

Here's query I'd use:

SELECT *
INTO #Table
FROM RemoteServer.MyDB.dbo.MyTable;

INSERT INTO dbo.MyTable
SELECT *
FROM #Table AS T;

I'm testing with relatively small data set, just about 14k rows (75 columns in total, most of them are FLOATs).

Both servers are under the same network.

I've configured connection managers in SSIS to use Microsoft OLE DB Provider for SQL Server with Packet Size of 32767 as suggested here:

https://gqbi.wordpress.com/2013/06/19/ssis-fastest-data-flow-task-item-for-transferring-data-over-the-network/

Am I doing something wrong, because I'd expect SSIS to run faster than Linked Server?

Is there another way to speed performance up in SSIS package and win against Linked Server?

6
  • You already noted part of the reason - small data set in your testing. Moving to larger data sets you will see SSIS greatly outshine the linked server. In some cases the linked server operations can move from set to row based which is we we avoid them for DML almost entirely. Nov 21, 2016 at 18:02
  • @Steve Thanks. I'll try to compare large data sets. Thanks for spotting that Nov 21, 2016 at 18:15
  • 1
    Use SSIS. Linked server links have bugs, granted they are rare, but Microsoft has said they won't fix them. For example, if you use a SQL synonym across a linked server and then rebuild the indexes on the remote server, you will get a schema bind error. Nov 21, 2016 at 19:58
  • 1
    If you are looking for another approach to test for transferring data between instances, you can try using SqlBulkCopy, which is a .NET function that can be used in a console app, Windows app, or even in SQLCLR. And regarding the SQLCLR option, I wrote such a stored procedure, DB_BulkCopy, which is available in the Free version of the SQL# SQLCLR library. Nov 25, 2016 at 20:30
  • @srutzky I'll take a look at it Nov 25, 2016 at 20:46

1 Answer 1

-5

hi Linked servers in general not best practice. I generally recommend against them. To many reasons to explain here....this would be come a white paper. hahahah I have been doing ETL and data security for 25 years. please consult an architect and don't ask people on internet claiming to be architect. there are very few people out there with my level of experience. there is never one answer either...it depends on many factors how you approach this. AND testing small amount of records is not valid test. do more intense testing. best of luck!

1
  • 1
    Sorry, but even long-established participants in this site, with high reputations, are expected to actually provide some rationale with a statement such as this. "I've got a lot of experience, so you should believe me" doesn't really cut it from a new user, without some reasons. I'm not even saying you're wrong - just that you need to give more than a bald statement.
    – RDFozz
    Jul 25, 2018 at 19:02

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.