I have the below stored procedure that is taking close to an hour to complete. The result set is about 200K machines.
All I am doing is extracting a set of ids from a linked server, deleting those ids in a local server and then extracting all details for those ids from a remote server and inserting them. The INSERT takes 90%, DELETE table 9% and SELECT INTO 1%.
id is the Primary Key/Clustered Index.
DECLARE @ProcessDate DATETIME SET @ProcessDate = CONVERT(VARCHAR(10), DATEADD(DAY, -3, GETDATE()), 111) SELECT DISTINCT id INTO #temp_machines FROM remote_server.db.dbo.table1 WHERE dt_modify >= @ProcessDate DELETE FROM local_server.db.dbo.table2 WHERE id IN ( SELECT id FROM #temp_machines ) INSERT INTO local_server.db.dbo.table2 SELECT * FROM remote_server.db.dbo.table1 WHERE id IN ( SELECT id FROM #temp_machines )
Any suggestions on improving performance?