3

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.

2

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;
    

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.