Sql-server – DELETE from Linked Server table using OPENQUERY and dynamic criteria

deletelinked-serversql server

I'm attempting to run a DELETE statement from my local SQL Server to delete appropriate records from a table in my remote linked server using OPENQUERY(). I'm using the following code, but it seems slow and I don't want to be pounding my server with bad SQL. Should I be using OPENQUERY() for this? Should I be deleting from it a different way that's more direct?

Here's my code:

DELETE RemoteTable
FROM OPENQUERY(MyLinkedServer,'SELECT * from RemoteTable_T1') AS RemoteTable
INNER JOIN MyDB.dbo.LocalTable_T1 AS LocalTable
ON LocalTable.LocalTable_T1_EntryID = RemoteTable.RemoteTable_T1_EntryID;

Any improvements I can make on this code since it is going to be set up as a recurring task on my server? I have about 5 different variations of this in order to delete from 5 different tables on my linked server.

Best Answer

In my experience, DML statements over Linked Servers is sllooowww (I believe that is the proper, technical spelling of this particular type of slowness ;-).

I found that the following setup was mucho faster:

  1. Create a stored procedure on the remote server:

    • Call it something like RemoteTable_DeleteByEntryIDList
    • It should accept an NVARCHAR(MAX) parameter called @EntryIDs
    • The format of the data for that parameter will be a list of EntryIDs in XML
    • The parameter cannot be of type XML as that is not valid for passing over a Linked Server

    • The proc will do something along the lines of:

      CREATE TABLE #TempIDs ([EntryID] INT NOT NULL);

;WITH cte AS
(
    SELECT CONVERT(XML, @EntryIDs) AS [Data]
)
INSERT INTO #TempIDs ([EntryID])
  SELECT tmp.[EntryID]
  FROM   cte
  CROSS APPLY (SELECT tab.col.value('./EntryID[1]', 'INT') AS [EntryID]
               FROM   cte.[Data].nodes('/row') tab(col)
              ) tmp;

-- optional: test to see if it helps or hurts
-- ALTER TABLE #TempIDs
--   ADD CONSTRAINT [PK_#TempIDs]
--   PRIMARY KEY CLUSTERED (EntryID ASC)
--   WITH FILLFACTOR = 100;

DELETE rt
FROM   RemoteTable rt
INNER JOIN #TempIDs tmp
        ON tmp.[EntryID] = rt.[EntryID];

  2. Update your local stored proc to do something like:

    DECLARE @IDsToDelete NVARCHAR(MAX);

SET @IDsToDelete = (
  SELECT EntryID
  FROM   dbo.LocalTable
  FOR XML RAW
);

EXEC [MyLinkedServer].[DatbaseName].[SchemaName].[RemoteTable_DeleteByEntryIDList]
     @EntryIDs = @IDsToDelete;
Related Question