DELETE from Linked Server table using OPENQUERY and dynamic criteria 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.
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:


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];


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;