Sql-server – Delete and Insert performance SQL Server

linked-serverperformancequery-performancesql serversql-server-2008

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?

Best Answer

First step: Make sure there's something to tune and that your query isn't just being blocked. You can do this with free tools like sp_WhoIsActive or sp_BlitzFirst (disclaimer, I'm a contributor to the First Responder Kit).

Second step: Don't use local variables.

Third step: Maybe indexing the temp table will help.

Fourth step: Check your wait stats using sp_BlitzFirst (same disclaimer about me contributing to it, blah blah blah). It could be that the query as written is fine, but you're running into some other issue, like tempdb contention.

Related Question