gseim

Performance issue with query when using linked server

Discussion created by gseim on Jun 18, 2014
Latest reply on Jul 4, 2014 by gseim

I have a sql server set up (Server 2012 standard), with a linked server towards PI. It works perfectly, at least until I change something in the query. Anyone with several issues?

 

I am creating a report showing the latest buttons pressed on a operator interface.

 

Query which works perfectly:

 

 This query has a wildcard in the tagname.. 

 
declare @BackHours nvarchar(10)
set @BackHours = '20'

SELECT arch.tag, arch.value, b.name, point.descriptor, arch.time
FROM 
.[piarchive]..[picomp2] arch, 
.[pipoint]..[pipoint2] point, 
.[PIDS]..[BOOL] b
WHERE arch.tag like 'IOC1.Button*.bPressed'
     AND arch.value = b.code
     AND arch.time > dateadd(HOUR, isnull(cast(@BackHours as int)*-1, -24), GetDate())
     AND arch.tag = point.tag
     and b.name = 'TRUE'
     order by arch.time desc

Execution plan from sql server:

 

8311.explan1.png

 

 Since there are several operator interfaces, I would like to "generalize" the first part of the tag, creating a variable or concatenated string dependent on parameters.

 

 

 
declare @BackHours nvarchar(10)
set @BackHours = '2'
declare @chairno int
set @chairno = 1

SELECT arch.tag, arch.value, b.name, point.descriptor, arch.time
FROM 
.[piarchive]..[picomp2] arch, 
.[pipoint]..[pipoint2] point, 
.[PIDS]..[BOOL] b
WHERE arch.tag like 'IOC'+cast(@Chair as nvarchar)+'.Button*.bPressed'
     AND arch.value = b.code
     AND arch.time > dateadd(HOUR, isnull(cast(@BackHours as int)*-1, -24), GetDate())
     AND arch.tag = point.tag
     and b.name = 'TRUE'
     order by arch.time desc

 

 

The execution plans for these two queries are quite differenct

 

8105.explan2.png

 

I tried some other contellations like the one below:

 

 Replacing:

 
WHERE arch.tag like 'IOC'+cast(@Chair as nvarchar)+'.Button*.bPressed'

 With

 

 

 
declare @Tag nvarchar(150)
set @Tag = 'IOC'+cast(@Chair as nvarchar)+'.Button*.bPressed'

WHERE arch.tag like @Tag

 or by (subquery)

 

 WHERE arch.tag in (select tag from .[pipoint]..[pipoint2] where tag like @Tag)

 

 

 

Any ideas? 

Outcomes