Prev: See each of the steping through few rows to see how data gets
Next: Get minimum from table
From: Dan on 29 Jan 2010 17:21
We have been trying to implement a multi-server database solution that tracks
millions of invoices. However, updating a bit data type status column across
the link has proved problematic. The following update fails due to a timeout
because of a full table scan of the remote table

SET XACT_ABORT ON
BEGIN TRAN
UPDATE LinkedServer.Account.dbo.Invoice
SET IsPaid = 1
WHERE InvoiceId = 1234
COMMIT TRAN

--Query Plan:
|--Remote Update(SOURCE:(LinkedServer), OBJECT:("Account"."dbo"."Invoice"),
SET:([LinkedServer].[Account].[dbo].[Invoice].[IsPaid] = [Expr1003]))
|--Compute Scalar(DEFINE:([Expr1003]=(1)))
|--Table Spool

|--Filter(WHERE:([LinkedServer].[Account].[dbo].[Invoice].[InvoiceId]=(1234)))
|--Remote Scan(SOURCE:(LinkedServer),
OBJECT:("Account"."dbo"."Invoice"))


However, updating a character field works fine!:
SET XACT_ABORT ON
BEGIN TRAN
UPDATE LinkedServer.Account.dbo.Invoice
SET CustomerName = 'Fred'
WHERE InvoiceId = 1234
COMMIT TRAN

Query Plan:
|--Remote Query(SOURCE:(LinkedServer), QUERY:(UPDATE
"Account"."dbo"."Invoice" set "CustomerName" = 'Fred' WHERE
"InvoiceId"=(1234)))

So other than the appearance of this being a bug, is there any way to stop
SQL Server from doing the full scan? And yes, I tried OPENQUERY. It is not
an option, since it's performance is just as bad as the full scan.

Thanks.
From: Erland Sommarskog on 29 Jan 2010 18:15
Dan (Dan(a)discussions.microsoft.com) writes:
> We have been trying to implement a multi-server database solution that
> tracks millions of invoices. However, updating a bit data type status
> column across the link has proved problematic. The following update
> fails due to a timeout because of a full table scan of the remote table
>...
> --Query Plan:
>|--Remote Update(SOURCE:(LinkedServer), OBJECT:("Account"."dbo"."Invoice"),
> SET:([LinkedServer].[Account].[dbo].[Invoice].[IsPaid] = [Expr1003]))
> |--Compute Scalar(DEFINE:([Expr1003]=(1)))
> |--Table Spool
>
>|--
Filter(WHERE:([LinkedServer].[Account].[dbo].[Invoice].[InvoiceId]=(1234)))
> |--Remote Scan(SOURCE:(LinkedServer),
> OBJECT:("Account"."dbo"."Invoice"))
>
>
> However, updating a character field works fine!:
>...
> Query Plan:
>|--Remote Query(SOURCE:(LinkedServer), QUERY:(UPDATE
> "Account"."dbo"."Invoice" set "CustomerName" = 'Fred' WHERE
> "InvoiceId"=(1234)))

I was able to reproduce this with a table I just made up, and I added a
repro to your Connect bug,
https://connect.microsoft.com/SQLServer/feedback/details/528007/updating-
bit-field-on-linked-server-fails

I can see how this could happen with newer data types like datetime2,
for which there is no matching core type in OLE DB, but it should not
happen on bit, I think.

But the general problems with linked servers is that the query plans
are generated with knowledge of what is on the other end, to the
smallest common denominator is used.

> So other than the appearance of this being a bug, is there any way to
> stop SQL Server from doing the full scan? And yes, I tried OPENQUERY.
> It is not an option, since it's performance is just as bad as the full
> scan.

Did you try EXEC AT:

EXEC('UPDATE Account.dbo.Invoice
SET IsPaid = 1
WHERE InvoiceID = ?', 1234) AT LinkedServer

You don't have to pass 1234 as a parameter, but I wanted to show that
it is possible.


--
Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

From: Gert-Jan Strik on 30 Jan 2010 04:46
Dan wrote:
>
> We have been trying to implement a multi-server database solution that tracks
> millions of invoices. However, updating a bit data type status column across
> the link has proved problematic. The following update fails due to a timeout
> because of a full table scan of the remote table
>
> SET XACT_ABORT ON
> BEGIN TRAN
> UPDATE LinkedServer.Account.dbo.Invoice
> SET IsPaid = 1
> WHERE InvoiceId = 1234
> COMMIT TRAN
>
> --Query Plan:
> |--Remote Update(SOURCE:(LinkedServer), OBJECT:("Account"."dbo"."Invoice"),
> SET:([LinkedServer].[Account].[dbo].[Invoice].[IsPaid] = [Expr1003]))
> |--Compute Scalar(DEFINE:([Expr1003]=(1)))
> |--Table Spool
>
> |--Filter(WHERE:([LinkedServer].[Account].[dbo].[Invoice].[InvoiceId]=(1234)))
> |--Remote Scan(SOURCE:(LinkedServer),
> OBJECT:("Account"."dbo"."Invoice"))
>
> However, updating a character field works fine!:
> SET XACT_ABORT ON
> BEGIN TRAN
> UPDATE LinkedServer.Account.dbo.Invoice
> SET CustomerName = 'Fred'
> WHERE InvoiceId = 1234
> COMMIT TRAN
>
> Query Plan:
> |--Remote Query(SOURCE:(LinkedServer), QUERY:(UPDATE
> "Account"."dbo"."Invoice" set "CustomerName" = 'Fred' WHERE
> "InvoiceId"=(1234)))
>
> So other than the appearance of this being a bug, is there any way to stop
> SQL Server from doing the full scan? And yes, I tried OPENQUERY. It is not
> an option, since it's performance is just as bad as the full scan.
>
> Thanks.

Another thing you could try is this:

UPDATE LinkedServer.Account.dbo.Invoice
SET IsPaid = CAST(1 AS bit)
WHERE InvoiceId = 1234

--
Gert-Jan
From: Erland Sommarskog on 30 Jan 2010 06:00
Gert-Jan Strik (sorrytoomuchspamalready(a)xs4all.nl) writes:
> Another thing you could try is this:
>
> UPDATE LinkedServer.Account.dbo.Invoice
> SET IsPaid = CAST(1 AS bit)
> WHERE InvoiceId = 1234

Nope. That was my initial thought, but I tested it before I posted, and
it doesn't help.


--
Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

 | 
Pages: 1
Prev: See each of the steping through few rows to see how data gets
Next: Get minimum from table