SQLServer solve the problem of remotely deleting data by connecting to the server

In the previous article, the solution to the performance problem of SQL Server access to Oracle through the linked server is introduced. This article introduces the solution to the performance problem of remote deletion of SQL Server data under the linked server. the system has a function. You need to remotely Delete the table data of the SQLServer instance. The delete statement contains the where condition and the condition contains a subquery. The

In the previous article, the solution to the performance problem of SQL Server access to Oracle through the linked server is introduced. This article introduces the solution to the performance problem of remote deletion of SQL Server data under the linked server. the system has a function. You need to remotely Delete the table data of the SQLServer instance. The delete statement contains the where condition and the condition contains a subquery. The

In the previous article, the solution to the performance problem of SQL Server access to Oracle through the linked server is introduced. This article introduces the solution to the performance problem of remote deletion of SQL Server data under the linked server.

1. Problem Discovery

The system has a function. You need to remotely Delete the table data of the SQLServer instance. The delete statement has the where condition and the condition contains a subquery.

The frontend execution speed of this function is very slow. So prepare for optimization.

The Demo code is as follows:

DELETE FROM [LINKSERVERNAME].[AdventureWorks2008].[Sales].[SalesOrderDetail]WHERE SalesOrderDetailID=5    AND EXISTS(SELECT TOP 1 1 FROM [LINKSERVERNAME].[AdventureWorks2008].[Sales].[SalesOrderDetail])

The execution plan is as follows:

You can see that there is a remote scan for the execution plan, and then perform filtering locally.

Enable profiler tracking on the remote server. Some content is as follows:

The Remote Server opens a cursor, reads data row by row, and returns the data to the caller.

It is foreseeable that the performance will be very poor. How can we avoid remote scanning without the where condition?

2. Solution to the Problem 2.1 OpenQuery

Use OpenQuery to filter and submit the delete data to a remote server for execution.

DELETE FROM OPENQUERY([LINKSERVERNAME]    ,'SELECT * FROM [AdventureWorks2008].[Sales].[SalesOrderDetail]WHERE SalesOrderDetailID=5    AND EXISTS(SELECT TOP 1 1 FROM [AdventureWorks2008].[Sales].[SalesOrderDetail])')

At this time, the execution plan

2.2 sp_executesql

Submit the entire delete statement to remote execution.

DECLARE @sql nvarchar(max)SELECT @sql ='DELETE FROM [AdventureWorks2008].[Sales].[SalesOrderDetail]WHERE SalesOrderDetailID=5    AND EXISTS(SELECT TOP 1 1 FROM [AdventureWorks2008].[Sales].[SalesOrderDetail])'exec [LINKSERVERNAME].[AdventureWorks2008].dbo.sp_executesql @sql

The statements tracked by profiler are as follows:

If something is wrong, please make a brick. If you have other methods, please share them. Thank you!