SQL Server remotely deletes data performance problem resolution through linked servers

Original: SQL Server remotely deletes data performance problem resolution via linked servers

In the previous article, the workaround for SQL Server access to Oracle performance issues through linked servers is described in this article, which addresses the performance issues of remotely deleting SQL Server data under linked servers

1. Problem finding

There is a feature in the system that requires remote deletion of the SQL Server Instance's table data, and a where condition in the DELETE statement, with a subquery in the condition.

The function of the foreground execution is very slow. So prepare for tuning.

The following is the demo code, which is not optimized as follows:

DELETE  from [Linkservername].[AdventureWorks2008].[Sales].[SalesOrderDetail]WHERESalesorderdetailid=5     and EXISTS(SELECT TOP 1 1  from [Linkservername].[AdventureWorks2008].[Sales].[SalesOrderDetail])

The execution plan at this time is as follows:

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

Start profiler tracking on the remote server, some of the following:

You can see that the remote server has a cursor open and then reads the data row by line and returns it to the caller.

Predictable performance can be very poor, how to avoid the remote scanning without the Where condition?

2. Problem solving2.1 OpenQuery

Use OpenQuery to submit a filter for the delete data to the 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 point, the execution plan

2.2 sp_executesql

Commits the entire DELETE statement to the remote execution

 @ 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  . [ ] . dbo.sp_executesql  @sql   

The following statements are tracked by the profiler:

 

If there is no place, welcome to shoot bricks, if there are other ways to share, thank you!

SQL Server remotely deletes data performance problem resolution through linked servers