June 24, 2008 at 7:52 am
Would an SSIS package that inserts/updates records in a database table faster than an equivalent T-SQL query that performs the same task using a linked server?
Here are 2 simple scenarios:
1st scenario: server instance Instance1, database db1 has data that we need to insert into Instance2, database db2.
2nd scenario: server instance Instance1, database db1 has data that we need to synchronize (make same) as data in Instance2, database db2. In this case the data will be updated in db2 according to data in db1.
Would an UPDATE/INSERT statement using a Linked Server between the 2 instances be always slower than an SSIS package doing the same thing?
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
June 24, 2008 at 8:39 am
The short and long answer in this case is, it depends.
There are a lot of variables involved in this sort of thing and the right answer in one case isn't the right answer in the next. The absolute fastest way I know of to move the data between servers is still to use BCP, or at least BULK INSERT. But that's not always a viable option, especially when you start talking about differential updates. If you need to manipulate the data as it comes across, SSIS really shines. I've also used straight TSQL with an INSERT/SELECT because it was the quickest. Based on your question, there's not enough information to say that SSIS would work better for your updates, but BCP, will probably work better for the inserts, but again, it really depends on your system.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
June 24, 2008 at 9:35 am
Thank you for the response.
The queries we are using for inserting/updating data (thousands of rows) through a linked server are very slow at the moment, that's why I thought to ask the question.
As always, the best way to know for sure is to test this out...
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply