AnsweredAssumed Answered

OLEDB Enterprise Timeout when querying Event Frames Attributes of type PI Point

Question asked by ryland Champion on Sep 28, 2015
Latest reply on Nov 3, 2015 by ryland

AF: Server:2.7.0.6937;  Database:2.7.0.6937

PIOLEDBENT: 1.3.1.5

 

We are working on a solution where we query Event Frame data using the PIOLEDB Enterprise provider.  We've been able to pull in all the event frames fine, until I added an Attribute Template of type PI Point to the Event Frame template we are using.  This PI point is a link to a PI Point  attribute of the default element for the event frame: ".\Elements[.]|Current Shift"  It is basically looking up the Production Shift Value in PI for the piece of equipment at the end time that is associated with the Event frame.  In PI System Explorer, the shift  data from PI displays correctly for the event frames, and seems to do so with very little delay when switching between Event frames.  However when I try to query the same data in PI SQL Commander, the query hangs and eventually times out after about 6 minutes or so.

 

I am able to get the query to return data, if I exclude the PI Point attribute from the results, or if I write the query in a way to only return results on a single event frame.  Any ideas why my query is choking?

 

This query chokes (Only looks to return the single PI point attribute "Shift" for the event frames):

 

SELECT

     ID                     = cast(ef.ID as String),

     RecordType            = ef.Description,

     Machine                = h.[Name],

     ef.StartTime,

     ef.EndTime,

     Type                 = cast(efa.[Name] as String),

     ValueStr             = cast(efs.[Value] as String),

     EventFrameName         = cast(ef.[Name] as String),

     ModifiedDT            = ef.Modified

FROM CLWOEE.EventFrame.EventFrame ef

JOIN CLWOEE.EventFrame.EventFrameAttribute efa

     ON efa.EventFrameID = ef.ID

JOIN CLWOEE.Data.EventFrameSnapshot efs

     ON efs.EventFrameAttributeID = efa.ID

JOIN CLWOEE.Asset.Element e

     ON ef.PrimaryReferencedElementID = e.ID

JOIN CLWOEE.Asset.ElementHierarchy h

     on h.[Name] = e.[Name]

     and h.Level = 3

WHERE efa.[name] = 'Shift'

 

This Query runs quickly (returns all attributes except 'Shift')

 

SELECT

     ID                     = cast(ef.ID as String),

     RecordType            = ef.Description,

     Machine                = h.[Name],

     ef.StartTime,

     ef.EndTime,

     Type                 = cast(efa.[Name] as String),

     ValueStr             = cast(efs.[Value] as String),

     EventFrameName         = cast(ef.[Name] as String),

     ModifiedDT            = ef.Modified

FROM CLWOEE.EventFrame.EventFrame ef

JOIN CLWOEE.EventFrame.EventFrameAttribute efa

     ON efa.EventFrameID = ef.ID

JOIN CLWOEE.Data.EventFrameSnapshot efs

     ON efs.EventFrameAttributeID = efa.ID

JOIN CLWOEE.Asset.Element e

     ON ef.PrimaryReferencedElementID = e.ID

JOIN CLWOEE.Asset.ElementHierarchy h

     on h.[Name] = e.[Name]

     and h.Level = 3

WHERE efa.[name] != 'Shift'

 

 

Outcomes