0

I'm issuing a fairly simple update of a single varchar column against a remote linked server - like this:

UPDATE Hydrogen.CRM.dbo.Customers
SET EyeColor = 'Blue'
WHERE CustomerID = 619

And that works fine when is written as an ad-hoc query:

enter image description here

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: (not strictly true, but close enough)

EXEC sp_executesql N'UPDATE [Hydrogen].[CRM].[dbo].[Customers] 
SET [EyeColor] = @P1 
WHERE [CustomerID] = @P5', 
N'@P1 varchar(4),@P5 bigint',
'Blue',619

This parameterized form of the query ends up performing a remote scan against the linked server:

enter image description here

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 - each pulling down a few thousand rows.

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? And even if it was going to decide to pull back rows to the local server, why in the world would it do it using a cursor?

It only fails on varchar columns. If I try updating an INT column, it works fine. But this column is varchar - and it fails.

I tried other parametrizing 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.

Of course I can't reproduce it anywhere except the customer's live environment. So it is going to require knowledge of SQL Server to come up with an explanation of what's happening.

Bonus Reading

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Ian Boyd
  • 246,734
  • 253
  • 869
  • 1,219
  • 2
    Beware, these are questions: What is the actual definition of the columns in Hydrogen.CRM.dbo.Customers? If it's not `varchar(4)` (I really doubt it) and `bigint`, can you force your code to hard-code the true definitions of these columns instead of basing it on the length of the string and a better-safe-than-sorry bigint? Sorry but there isn't going to be a "oh yeah just change this setting" answer. My guess is the ad-hoc version is letting the remote SQL Server infer the types, and the parameterized version is taking that choice away and forcing conversions that lead to scans. – Aaron Bertrand Oct 13 '21 at 01:06
  • 3
    Also, another question, what about instead of `sp_executesql N'do some remote server thing here';`, why not `EXEC [Hydrogen].[CRM].sys.sp_executesql N'do some simpler thing here without mentioning server names';`? When you say "fails" does that mean it is slow, or is there a third error condition you haven't specified? (Also, I think you've misplaced your generalization that parameterized queries are bad; I think it's more that, generally, linked servers are bad. But sometimes they work okay enough to see past that.) – Aaron Bertrand Oct 13 '21 at 01:08
  • Are you specifying the `SqlParameter` length, it should be the same as the column? Can you share the query plan via https://brentozar.com/pasthetheplan so we can view it properly – Charlieface Oct 13 '21 at 05:26
  • Beware another question. Have you considerd using `[Hydrogen].[master].[dbo].[sp_executesql]` From my experience avoid four part naming in SQL server and use OPENQUERY, so the query can be optimized on the server it's executing. Four part naming doesn't have any optimization in the engine resulting in your remote table scan. – Preben Huybrechts Oct 13 '21 at 12:16
  • @PrebenHuybrechts that's what [I suggested yesterday](https://stackoverflow.com/questions/69548333/updating-varchar-column-over-linked-server-with-parameterized-query-causes-remot#comment122930157_69548333). :-) – Aaron Bertrand Oct 13 '21 at 16:41

0 Answers0