I need help to find a way to efficiently get the last value of a two AF attributes (PI tags) before a given point in time, i.e. I want to get the PrevVal for a set of AF Attributes
The constraints are
- I am not able to use Event Frames
- I am not able to use PI Web API
- I am not allowed to use interpolated values i.e. I need the last actual value prior to the point in time
The reason I cannot use Event Frames or PI Web API is not technical, it is a business reason.
If it is possible the preferred technology would be PI OLEDB Enterprise.
All the assets in the AF hierarchy are templatised.
On each asset (approx 1500) I am interested in two particular attributes.
The attribute values can be fast changing and even with compression we can get about 1000 values per hour per attribute (this cannot be compressed further) but at different times we may not see a value change for hours.
Given a point in time how do I get the last value for each of the attributes. Does PI OLEDB Enterprise have a PrevVal equivalent?
I have something working but it takes 90 seconds on our system and this is far too slow.
The following statement is the heart of the code and also the bottleneck. The actual bottleneck is the join on to the Data.Archive table. (Please note this is not all of the code, it is not the whole solution but this code takes 90 seconds and the rest takes 1 second)
eh.Name as ElementName, ea.Name as Attribute, r.time, r.Value
ea.ElementID, ea.ID, ea.Name, ea.Path
ea.Name = 'Attribute 01' or ea.Name = 'Attribute 02') ea on eh.ElementID = ea.ElementID
AFDatabase.Data.Archive r ON r.ElementAttributeID = ea.ID
r.time > '*-2h' AND r.time <= '*-1h'
option (force order, ignore errors)
Please feel free to ignore the method I have been using and suggest something completely different.
Thanks in advance.