Slow query when connecting to linked server

Question

I've got this query

UPDATE linkeddb...table SET field1 = 'Y' WHERE column1 = '1234'

This takes 23 seconds to select and update one row

But if I use openquery (which I don't want to) then it only takes half a second.

The reason I don't want to use openquery is so I can add parameters to my query securely and be safe from SQL injections.

Does anyone know of any reason for it to be running so slowly?

Answer 1

Here's a thought as an alternative. Create a stored procedure on the remote server to perform the update and then call that procedure from your local instance.

/* On remote server */
create procedure UpdateTable
    @field1 char(1),
    @column1 varchar(50)
as
    update table
        set field1 = @field1
        where column1 = @column1
go

/* On local server */
exec linkeddb...UpdateTable @field1 = 'Y', @column1 = '1234'

Answer 2

If you're looking for the why, here's a possibility from Linchi Shea's Blog:

To create the best query plans when you are using a table on a linked server, the query processor must have data distribution statistics from the linked server. Users that have limited permissions on any columns of the table might not have sufficient permissions to obtain all the useful statistics, and might receive aless efficient query plan and experience poor performance. If the linked serveris an instance of SQL Server, to obtain all available statistics, the user must own the table or be a member of the sysadmin fixed server role, the db_ownerfixed database role, or the db_ddladmin fixed database role on the linkedserver.

(Because of Linchi's post, this clarification has been added to the latest BooksOnline SQL documentation).

In other words, if the linked server is set up with a user that has limited permissions, then SQL can't retrieve accurate statistics for the table and might choose a poor method for executing a query, including retrieving all rows.

Here's a related SO question about linked server query performance. Their conclusion was: use OpenQuery for best performance.

Update: some additional excellent posts about linked server performance from Linchi's blog.

Answer 3

Is column1 primary key? Probably not. Try to select records for update using primary key (where PK_field=xxx), otherwise (sometimes?) all records will be read to find PK for records to update.

Answer 4

Is column1 a varchar field? Is that why are you surrounding the value 1234 with single-quotation marks? Or is that simply a typo in your question?