Linked Server Query Timeout Not Working and Optimization of Snap Shot Query

Discussion created by ericL on Apr 15, 2013
Latest reply on Apr 16, 2013 by ericL

Hi All,


I have 2 questions:


1) I configured linked server in our SQL Server 2008 machine to access PI. Everything is working well except that while running one SQL query it is timing out after 5 minutes. I tried to modify the Query Timeout parameter in the linked server configuration but it doesn`t seem to have no effect, still times out after 5 minutes. Not sure if it is worth mentioning but this Sql query that times out runs inside a stored procedure on the sql server 2008 box.








2) Based on the question above the offended query is below, this query takes about 8 minutes to run in Sql Commander (the tool that comes with PI, so is probably not really a linked server performance issue) and as mentioned above it times out after 5 minutes in sql server 2008 using linked server).


The question is: Is there a better way to achieve this? basically I need to get all the values (with the tag names) of the pi points from the archive within a certain period of time, first time the report runs it needs to get everything (lets say after 2000-01-01). I'm already using the pisnapshot table for that.


Worth to mention that the client will have hundreds of thousands of pi points so I cannot get a list of pi points and iterate through each one at a time to get their values as it would obviously be very slow, this code will run incrementally, I mean, next time I run the date will not be 2010-01-01, it will only get the last values after the last time the report ran, so it may run faster. Bigger challenge at this point might be to get out of this 1st calculation for the report:


DECLARE @query1 varchar(1000)
SET @query1 = 'SELECT * INTO ##temp1 FROM OPENQUERY(PI_Mav,''
    SELECT p1.Tag, p1.Value
    FROM [piarchive]..[pisnapshot] p1
    WHERE p1.Tag like ''''00:%''''
    And p1.[time] >= ''''2000-01-01''''
    And p1.Value Is not null






Any ideas?