Remote queries and linked servers

I have 2 servers with SQL Server 2008 R2: INT & CRM.

I need to run an SSIS package on INT, which uses data from both an INT database and a CRM database. It's painfully slow.

I tried this to test a simple query (on INT):

EXEC sp_addlinkedserver @server='CRM', 
                        @srvproduct='', 
                        @provider='SQLNCLI', 
                        @datasrc=''

EXEC sp_addlinkedsrvlogin
                        @useself='FALSE',
                        @rmtsrvname='CRM',
                        @rmtuser='',
                        @rmtpassword=''

I also gave ` ddl_admin role on the CRM server, as recommended on this answer:
SQL performance issues with remote query across linked server

I then run this query:

UPDATE [CRM].DB.dbo.table1
SET field = 1
WHERE id = (SELECT id FROM [CRM].DB.dbo.table2 WHERE secondary_id = 9999)

This query takes 40 ms to run when on CRM` itself. It takes 30 seconds to run in the above example. There are no results returned, it can't be network issues, so why does it take so long?

Okay so here's what I think you need to do. I'm assuming since you are already using integration services you have both of your data sources set so I'm not going into detail on that. If you have any questions as to how to properly set those up I can help you to. So the first thing you need to do is create a new package in your Integration services solution. Then you need to create a dataflow task. You will find that in your You toolbox like so .

Once you have done that open the DataFlowTask and Create a new OLEDB Connection. Once again found in the toolbox.

Open that Up and set up your SQL command this will run locally on the machine and will therefore suffer no linked query costs.

Next Set up your OLEDB command the portion that will execute a SQL command with the result Set. First Select OLEDB Command from the toolbox.

Connect your OLE Connection to You OLE Command.

Next you create a stored procedure to handle this update. Its a fairly simple procedure to make it will ultimately look like this.

CREATE PROCEDURE UPD_CRM_TableName(
    @ID int,
    @Field int)

UPDATE TableName SET 
@Field = Field

WHERE @ID = ID

Then open you OLEDB command and select the appropriate connection in the connection tab.

Go to the Component properties tab click on SQL Command and the appropriate values to execute your stored procedure.

Then go to column mappings and make your parameters match you columns and you will be good to execute the package. Note that the update stored procedure will be sent to the server with the appropriate parameters required to update the table eliminating the need for your costly linked query.

Setting this up for any update you command will be incredibly easy to set up and will basically run as fast as your hardware and network can keep up. Make sure your integration services box has plenty of memory as that is where all of it's operations are executed. If you have any questions feel free to let me know.

StackExchange Database Administrators Q#31641, answer score: 5