SQL Server Performance Forum – Threads Archive
Bug? Synonyms / update / linked server
I had a nasty experience yesterday – I was planning to use synonyms for linked server references in some procedures, but when I exchanged the synonyms for the names of the real objects, it seemed to cause some update statements to update more rows than they should (creating a huge mess). Has anyone else encountered this? Here’s the detail: This type of statement has been working correctly:update linkedserver.database.dbo.table
set someColumn= ‘Foo’, anotherColumn = ‘Bar’
from linkedserver.database.dbo.table rt
inner join localTable lt on lt.col1 = ‘value’ and lt.key = rt.key
where lt.col2 = ‘A Constant’
This should update a subset of rows in the remote table matching those in the local table. BUT, when I created a synonym for the remote table, and substituted it in the statement, it updated ALL the rows, rather than limiting the update to the matching rows: create synonym remoteTable for linkedserver.database.dbo.table
GO update remoteTable
set someColumn= ‘Foo’,
anotherColumn = ‘Bar’
from remoteTable rt
inner join localTable lt on lt.col1 = ‘value’ and lt.key = rt.key
where lt.col2 = ‘A Constant’
updates ALL rows in the linked server table. The performance was also really awful, though I am not sure if that is because of the synonym or because it was doing the wrong things to the data. Do I have the syntax wrong? Or is this a bug? If it matters, this is executed on SQL Server 2005 (9.0.2153) against a linked SQL Server 2000 instance (8.0.760)
Merrill, When using an alias for the table-to-be-updated in the FROM clause, you have to put the alias after UPDATE, not the actual name: UPDATE MyAlias
SET ……..
FROM MyTable AS MyAlias
Are you sure? Here’s the BOL example that has similar logic:
USE AdventureWorks;
GO
UPDATE Sales.SalesPerson
SET SalesYTD = SalesYTD + SubTotal
FROM Sales.SalesPerson AS sp
JOIN Sales.SalesOrderHeader AS so
ON sp.SalesPersonID = so.SalesPersonID
AND s
rderDate = (SELECT MAX(OrderDate)FROM Sales.SalesOrderHeader
WHERE SalesPersonID =
sp.SalesPersonID); and "If the object being updated is the same as the object in the FROM clause and there is only one reference to the object in the FROM clause, an object alias may or may not be specified. If the object being updated appears more than one time in the FROM clause, one, and only one, reference to the object must not specify a table alias. All other references to the object in the FROM clause must include an object alias."
Change your syntax as Andrian mentioned… And I read somewhere synonym copies the table over before it reads the the data from the table…I will try to find out the article or info about this… Check the query plan when you are using the synonym… To improve the performance… copy the localTable table to remove server and run the update… MohammedU.
Moderator
SQL-Server-Performance.com
Respectfully guys – and I do appreciate your time – those responses don’t directly address my question: in my original post, the first update statement works (maybe not as fast as possible, yes, but I am not performance-tuning this) while the one with the synonym does not work, and updates many wrong rows. The first statement is syntactically correct, as far as I can tell from BOL, and from my experience that statements like that work. The fact that it is possible to use the alias to specify the updated table is interesting (that seems to be at odds with the BOL documentation, but it does work). I really am curious to know in more detail why the second statement doesn’t work, so that perhaps I can employ the synonym that I need. I will try variations with Adriaan’s suggestion and the synonym together and see if that prevents the problem.
]]>