I'm issuing a fairly simple update of single varchar column against a remote linked server. E.g.:
UPDATE Hydrogen.CRM.dbo.Customers
SET EyeColor = 'Blue'
WHERE CustomerID = 619
And that works fine when is written as an ad-hoc query:
Parameterized Queries Bad
When we do what we're supposed to do, and have our SqlCommand
issue it as a parameterized-query, the SQL ends up being:
exec sp_executesql N'UPDATE [Hydrogen].[CRM].[dbo].[Customers]
SET [EyeColor] = @P1
WHERE [CustomerID] = @P5',
N'@P1 varchar(4),@P5 bigint',
'Blue',619
The query ends up performing a Remote Scan against the linked server:
It creates a cursor on the linked server, and takes about 35 seconds to pull back 1.2M rows to the local server through a series of hundreds of sp_cursorfetch
Why, in the world, would the local SQL Server optimizer ever decide to pull back all 1.2M rows to the local server in order to update anything?
It only fails on varchar columns
If i update int fields, it works fine.
But this column is varchar
- and it fails.
I tried other parameterizing the column as nvarchar
, and it's still bad.
Every answer i've seen actually are questions:
- "is the collation the same?"
- "What if you change the column type?"
- "Have you tried OPENQUERY?"
- "Does the login have
sysadmin
role on the linked server?"
I already have my workaround: parameterized queries bad - use ad-hoc queries.
I was hoping for an explanation of the thing that makes no sense. And hopefully if we have an explanation we can fix it - rather than workaround it.
Bonus Reading
- Stackoverflow: Remote Query is slow when using variables vs literal
- Stackoverflow: Slow query when connecting to linked server
- SQL Server update query on linked server causing remote scan
- Stackoverflow: Parameter in linked-server query is converted from varchar to nvarchar, causing index scan and bad performance
- Performance Issues when Updating Data with a SQL Server Linked Server
- Update statements causing lots of calls to sp_cursorfetch?
- Remote Scan on Linked Server - Fast SELECT/Slow UPDATE
"Too localized, unlikely to help others." Gives 7 links of other people having the issue. I hate people.
HYDROGEN
is a server name,, which is running an instance of SQL Server.