Updating linked server columns

Here’s  a fun one I came across this week, we had what was supposed to be a relatively simple update statement taking forever to execute. The update was to an application database on another server, and the table had about 300k rows in it.  We had two very similar update statements and only one of them was causing us any grief.

As mentioned, we were updating an application database, so used a cursor to lock the rows 1 at a time.

   1: DECLARE @co_num NVARCHAR(10)

   2: DECLARE @OrderOutcome NVARCHAR(50)

   3:  

   4: DECLARE CurOrders CURSOR FORWARD_ONLY READ_ONLY FOR

   5:        SELECT        co_num, LEFT(OO.OrderOutcome,40)

   6:        FROM          [SERVER01].DATABASE01.dbo.co co

   7:        INNER JOIN    OrderOutcome OO ON OO.OrderNo = co.ordno

   8:  

   9: OPEN CurOrders

  10: FETCH NEXT FROM CurOrders INTO @co_num, @OrderOutcome

  11:  

  12: WHILE @@FETCH_STATUS = 0

  13:  

  14: BEGIN

  15:        UPDATE [SERVER01]. DATABASE01.dbo.co

  16:        SET    Outcome = @OrderOutcome

  17:        WHERE  co_num = @co_num   

  18:  

  19:        FETCH NEXT FROM CurOrders INTO @co_num, @OrderOutcome

  20:              

  21: END

  22:  

  23: CLOSE CurOrders

  24: DEALLOCATE CurOrders

The original execution plan looked as follows:

So you can see the query is doing a full table scan of the remote table (the table we want to update).

Why is it doing this?  Well after much head scratching we finally identified the difference between our working query and this troublesome query. Our @OrderOutcome parameter is of length 50, where as the data being entered into it is limited to 40 characters. The fix, update the @OrderOutcome parameter to 40 characters…

The resulting execution plan, now looks like this:

I can’t explain why this happens, but it looks to have been a problem ever since SQL 2005, and when you have 2,000 updates to do, doing a table scan for each of those can be quite tedious, but now you know.  Make your parameters the same length as the longest data going into it.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s