Slow DELETE or UPDATE against non-SQL linked server

View products that this article applies to.

This article was previously published under Q309182

Expand all | Collapse all

SYMPTOMS

UPDATE or DELETE statements that act against a remote table that resides on a non-SQL Server data source, may run much slower than an equivalent SELECT statement.

Back to the top

For linked server DELETEs or UPDATEs, SQL Server retrieves data from the table, performs any filtering that is necessary, and then performs the deletes or updates through the OLEDB rowset. This processing can result in a round-trip to the remote server for each row that is to be deleted or updated. The SET SHOWPLAN ON output may look something similar to this plan:

  |--Remote Delete(SOURCE:(remserver), OBJECT:("".""."titles"))
     |--Table Spool
        |--Filter(WHERE:([remserver]...[titles].[royalty]=10))
           |--Remote Scan(SOURCE:(remserver), OBJECT:("".""."titles"))

SQL Server 2000 adds the ability to send a DELETE or UPDATE to a linked server as a single SQL statement; however, this feature only covers linked servers to another SQL Server 2000 or SQL Server 7.0 instance. A plan that is taking advantage of this feature shows a Remote Query operator that sends a single DELETE SQL statement to the remote server instead of the Remote Delete operator seen previously. Following is a plan that is functionally equivalent to the preceding plan (but is only seen if the local server is a SQL Server 2000 server and [remserver] is a SQL Server 7.0 or SQL Server 2000 server):

   |--Remote Query(SOURCE:(remserver), QUERY:(DELETE "".""."titles" FROM 
"".""."titles" Tbl1001 WHERE Tbl1001."royalty"=(10)))

To take advantage of the new SQL Server 2000 behavior described above, the query must satisfy the following:

The query must be a simple UPDATE/DELETE against a single remote table.
All selection criteria (in other words, predicates in the WHERE clause) must be remotable. For example, if the query includes the filter "WHERE col1 = 'abc'" and the remote server is not collation-compatible, SQL Server 2000 can't remote the UPDATE as a Remote Query because it can't trust the remote server to use the correct collation when deciding which rows to update.
The local server must be SQL Server 2000, and the remote server must be either SQL Server 7.0 or 2000.

Back to the top

MORE INFORMATION

Before you decide that this behavior is the cause of a performance issue, confirm that the performance problem is specific to a data modification statement by converting the UPDATE or DELETE query into a comparable SELECT against the remote table. For example, if the query is:

 DELETE FROM remsrv...remtbl WHERE c1 = 10,

Compare the execution time of the preceding query to this query:

 SELECT * FROM remsrv..remtbl WHERE c1 = 10

Use the DBCC FREEPROCCACHE statement before each execution to eliminate the effects of plan caching on these tests. If the speed of the SELECT statement is comparable to the speed of the UPDATE or DELETE query, the information in this article does not apply.

You cannot use the OPENQUERY function for DELETEs or UPDATEs; OPENQUERY is designed to send a read-only command that returns a result set.

As mentioned earlier, the ability to remote certain multi-row UPDATE or DELETE statements as a single SQL query to a linked server only exists in SQL Server 2000. If the local (originating) instance is a SQL Server 7.0 server and if the destination instance is also a SQL Server 7.0 or SQL Server 2000 server, you can execute a multi-row UPDATE or DELETE statement entirely on the remote server with one of the following alternatives:

Create a stored procedure on the remote instance that contains the UPDATE or DELETE statement. Pass in additional data that the query needs as stored procedure parameters. Execute this stored procedure from the local instance via a server-to-server Remote Procedure Call (RPC).
Execute the query in a more dynamic fashion by invoking the sp_executesql stored procedure on the remote server from the local server; for example:
EXEC remserver.master.dbo.sp_executesql @stmt = N'DELETE sometable WHERE col1 = 10 OR col1 = 20'

Back to the top