0

I have a perfomance issue regarding the update of Synonym table.

I have two SQL Servers:

  • Server A (2017 DEV)
  • Server B (2008 R2 DEV)

and they communicate through linked Server (collation compatible = TRUE). I have a log table (let's say T) on Server A and a synonym of this table on Server B.

The following code takes 10 seconds to update a single value (!)

DECLARE @VAR nvarchar(max),@guid uniqueidentifier

SET @VAR = 'TEST VALUE'
SET @guid = 'some value'

UPDATE LogTable 
SET SizeKB=SizeKB+isnull(DATALENGTH(@VAR)/1024.0,0) 
WHERE LogTable_guid=@guid

Execution Plan: enter image description here

But this code takes less than 1 second

DECLARE @guid uniqueidentifier

SET @guid = 'some value'
    
UPDATE LogTable 
SET SizeKB=SizeKB+isnull(DATALENGTH('TEST VALUE')/1024.0,0) 
WHERE LogTable_guid=@guid

Execution Plan:

enter image description here

Event this code takes less than 1

DECLARE @VAR nvarchar(max),@guid uniqueidentifier,@temp float

SET @VAR = 'TEST VALUE'
SET @guid = 'some value'

SELECT @temp = SizeKB
FROM LogTable 
WHERE LogTable_guid=@guid

SET @temp = @temp+isnull(DATALENGTH(@VAR)/1024.0,0)

UPDATE LogTable 
SET SizeKB=@temp 
WHERE LogTable_guid=@guid

Execution Plan:

enter image description here

I am not looking for a work around for this, rather I would like to understand why there's difference in execution plan between the variable in DATALENGTH and when there's just the value of the variable..

This behavour is not limited to DATALENGTH.. I have tried LEN() and SUBSTRING() and they both have the same issue as mentioned.

Emka
  • 340
  • 6
  • 16
  • Collation mismatches can cause this, as SQL Server can then not be sure if the remote server would calculate the same thing as locally when operating on strings. Check if your server is marked "collation compatible", and if it is, if the collations of the servers match. You might see an improvement by adding `COLLATE [collation]` to the `@VAR` expression, or then again you might not. Even with strings that have matching collations it's very easy to cook up expressions that happen to not be remoted, which is why you should be conservative with linked queries. – Jeroen Mostert Nov 04 '20 at 16:32
  • @JeroenMostert Thank you for your reply. The linked server is collation compatible and the databases are the same. They were restored from the same backup and set up with same collation. I did try Collate the variable as you mentioned but still have the same execution plan as well as the delay. – Emka Nov 05 '20 at 08:16

0 Answers0