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;


StackExchange Database Administrators Q#89495, answer score: 2