We are currently retrieving event frames through a custom application making use of PI AF SDK. It is a bit slow for our needs and thus looking for alternatives. It takes about one minute to retrieve 3000 events.
I've read about RTQP but have not tried it yet. How does the performance of SDK, traditional ODBC and RTQP compare when retrieving event frames with custom attributes?
UPDATE
It seems that the SDK is supposed to give similar performance to RTQP. Thus I need to figure out why my code takes long to execute.
See below for test code that I'm using. The code returns about 6000 events. With the code as below it takes between 50 and 70 seconds to run. Commenting out the attribute lookups, the code takes less than two seconds to run. Thus the attribute lookups are the problem. I have changed the 'isIndex' of attribute templates to true, as well as used the CaptureValues() function on the event frame. It did not increase the perormance.
What is also interesting is only calling ef.Attributes gives the same performance as calling all the individual attributes. Thus I assume ef.Attributes loads all the attributes in memory then is easily references by the individual attributes. Thus the problems lies there.
Something else that might be relevant: the Reason attribute references an enumeration set with 1100 items. Might cause issues if not indexed properly ?
AF SDK Version: 2.10.1.8731
PI AF Server Version: 2.10.0.8628
Stopwatch sw = new Stopwatch();
sw.Start();
var eventTemplate = afDatabase.ElementTemplates.Where(x => x.Name == "Chimiwungo Down").First();
string query = string.Format("Template:\"{0}\"", eventTemplate.GetPath(afDatabase));
var s = new AFEventFrameSearch(afDatabase, "*", AFSearchMode.Overlapped, new AFTime("2018-01-01"), new AFTime("2019-03-13"), query);
var eventFrames = s.FindEventFrames().OrderBy(x => x.StartTime).ToList();
List<GridEvent> geList= new List<GridEvent>();
foreach (var ef in eventFrames)
{
GridEvent ge = new GridEvent();
ge.Name = ef.Name;
ge.StartDate = ef.StartTime;
ge.EndDate = ef.EndTime;
ge.Duration = ef.Duration.ToTimeSpan().TotalHours;
ge.Reason = ef.Attributes["Reason"].GetValue().ToString();
ge.ChangeHistory = (string[])ef.Attributes["Change History"].GetValue().Value;
ge.Comment = ef.Attributes["Comment"].GetValue().ToString();
ge.Locked = (bool)ef.Attributes["Locked"].GetValue().Value;
geList.Add(ge);
}
sw.Stop();
Console.WriteLine(sw.ElapsedMilliseconds / 1000.0);
Console.WriteLine(geList.Count);
Console.ReadLine();
How many total event frames are in your AFDatabase? How many total event frames are in your PISystem?
I gave a presentation in Barcelona explaining some of the mysteries of AFSearch.
https://www.osisoft.com/Presentations/LiveCoding--Getting-the-Most-Out-of-the-New-AFSearch/
I am as frustrated as our customers that PI Server 2018 SP2 (AF SDK 2.10.5) has not been released into production yet. However, it does have dramatic performance boosts with Overlapped event frames. Around the 59 minute mark of the presentation, I talk about how this new speed boost was implemented merely 2 weeks before Barcelona PI World. For me to search for 320K event frames out 3 million previously took 1 minute. With the improvement to Overlapped searches, it dropped to 15 seconds.
Overlapped searches aside, there are some inefficiencies in your code, or other things not said that can help. The talk above can shed light on some of this in better detail, but let's touch on some now.
Do you use Captured Values on the event frames? This may be the biggest factor to speed improvement. As Dan Fishman notes, use a full load. With captured values, this will be fast.
Have you considered using a lightweight search? See this blog for more: Aggregating Event Frame Data Part 5 of 9 - Lightweight FindObjectFields
How important is it for the results to be sorted by StartTime? This does introduce a performance drag because you must wait to gather the whole list rather than streaming and processing as values become available. How about you implement a sort mechanism on your List<GridEvent> instead? The IO bottleneck from AFSearch is bad enough waiting for data to be streamed. Don't impose a sort on those streamed items per se. Rather sort later on your own local list.
These 2 lines alone can be improved:
var s = new AFEventFrameSearch(afDatabase, "*", AFSearchMode.Overlapped, new AFTime("2018-01-01"), new AFTime("2019-03-13"), query); var eventFrames = s.FindEventFrames().OrderBy(x => x.StartTime).ToList();Again, ignoring the performance of Overlapped, the 2nd line, as short-and-sweet as it is, is doing too much and masking performance issues. See the Barcelona presentation for the section on LINQ cautions.
You should be opting into server side caching. And the AFEventFrameSearch is disposable. If you absolutely insist on sorting the full list, try something like this (not a lightweight search):
List<GridEvent> geList = new List<GridEvent>(); using ( var s = new AFEventFrameSearch(afDatabase, "*", AFSearchMode.Overlapped, new AFTime("2018-01-01"), new AFTime("2019-03-13"), query) ) { s.CacheTimeout = TimeSpan.FromMinutes(10); var eventframes = s.FindEventFrames(fullLoad: true); foreach (var ef in eventframes) { GridEvent ge = new GridEvent(); ge.Name = ef.Name; ge.StartDate = ef.StartTime; ge.EndDate = ef.EndTime; ge.Duration = ef.Duration.ToTimeSpan().TotalHours; ge.Reason = ef.Attributes["Reason"].GetValue().ToString(); ge.ChangeHistory = (string[])ef.Attributes["Change History"].GetValue().Value; ge.Comment = ef.Attributes["Comment"].GetValue().ToString(); ge.Locked = (bool)ef.Attributes["Locked"].GetValue().Value; geList.Add(ge); } } geList = geList.OrderBy(x => x.StartDate).ToList();With captured values, employing server-side caching, and a lightweight search, this would be much faster. And when AF 2.10.5 comes out, the Overlapped search will be much faster too.