14 Replies Latest reply on Mar 14, 2019 2:47 PM by fmocke

    Fastest Way to Retrieve Events from AF

    fmocke

      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();
      
          • Re: Fastest Way to Retrieve Events from AF
            fmocke

            Thanks.  The RTQP event frame search strings looks similar to the ones that I use in the AF SDK.  Why is the AF SDK performance then so slow?  It seems like RTQP is a lot faster.  To be more specific, it is the attribute lookups that decreases performance by 20 times.  Returning vanilla event frames is quick enough for our purposes.

          • Re: Fastest Way to Retrieve Events from AF
            Rick Davin

            Hello Franco,

             

            I find it hard to believe that RTQP will be any faster when you say that AF SDK is slow.  What versions of AF Server and Client are you using?  Can you share the relevant code?  It helps to know which method you are using (e.g. AFEventFrame.FindEventFrames versus AFEventFrameSearch.FindEventFrames), as well as how you are filtering the event frames.  I know that filtering on Overlapped event frame dates is significantly faster with AF SDK 2.10.5.

             

             

            Beyond the code, we had one head scratcher of a case where someone showed us their code and was getting horrific performance for event frames.  We looked at the code and found little wrong with it.  The problem was an unrelated database on their SQL Server.  Chances are AF's PIFD database is not the only database on your SQL Server.  Nor was it for this other customer.  They discovered some other database had a table with 30 million records and that it made the SQL Server transaction logs grow sluggish and unwieldy.  These logs affect every database on the SQL Server instance.  When they performed maintenance to truncate the log file, things became very fast again for AF as well as their other databases.

             

            Since the introduction of myOSIsoft.com, I cannot see the same things that customers do.  And not all KB's have been migrated over.  One KB that I see that is a good place to start is KB01398 FindChanges_AT Table May Cause PI AF Server Performance Issues.  But I think there is another KB that is more directly applicable.  If you call Tech Support, they can help you better.

             

            Other posts semi-related to this:

             

            About « OSIsoft AF Maintenance (PIFD) » SQL Server job

             

            AF SQL Database Transaction Log

             

            Anything to add Keith Fong ?

             

             

            UPDATE ABOUT RTQP VERSUS AF SDK

            Someone in Tech Support did let me know that RTQP should be faster than AF SDK since it goes directly to the SQL Server and not through the time-consuming serialization of AF SDK objects first.  Again, should be.  The question remains "How much faster?"  My point was not a battle between RTQP versus AF SDK.  Rather it was to mention that there may be other things going on the SQL Server that adversely affect the AF Server.  In which case, SQL Server maintenance would be the path to pursue.

            1 of 1 people found this helpful
              • Re: Fastest Way to Retrieve Events from AF
                fmocke

                Thanks for the reply.  I have included test code that reproduces the problem.  As said, the issue is with the attribute lookups.  The database server itself is not an issue as it is solely dedidcated to AF.

                 

                I have also indicated the software versions in the initial post.

                  • Re: Fastest Way to Retrieve Events from AF
                    Dan Fishman

                    You are correct about the Event Frame loading all the attributes into memory when you try to load just one attribute.

                     

                    You are making thousands of calls to the AF Server and I would try to reduce the number of network calls. For your FindEventFrames method, change it to FindEventFrames(fullLoad: True).  This will take some time, put all of the EF attributes values will be in memory.  CaptureValues should definitely be used.

                    3 of 3 people found this helpful
                    • Re: Fastest Way to Retrieve Events from AF
                      Rick Davin

                      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.

                      2 of 2 people found this helpful
                        • Re: Fastest Way to Retrieve Events from AF
                          fmocke

                          Hi Rick.  Thanks for the detailed response.  The big gainer was fullLoad:true and I am happy with the performance improvement.  I will watch your presentation and be on the lookout for the new release!

                           

                          Thanks again

                            • Re: Fastest Way to Retrieve Events from AF
                              Rick Davin

                              Glad it was reduced to 8 seconds.  Just saying, I would like to see what a lightweight search would have reduced it to.  Maybe 3 seconds or less?  Would be cool to know.

                               

                              Not to boast, but others have told me that my Barcelona talk is a must-see for any developer using AFSearch.  Anyone who has seen it can say they walked away with a much better understanding of what is happening during a search, and that they feel more confident to tweak their own code afterwards.

                               

                              Lightweight searches have been around for many releases know.  They require a bit more coding but are extremely fast with captured event frames.  Not so much if not captured.  For the application at hand, this just screams for a lightweight search.

                                • Re: Fastest Way to Retrieve Events from AF
                                  fmocke

                                  Hi Rick.  I just played around with it, however not sure how to retrieve start and end date.  I used the string below to look for attributes however StartTime and EndTime does not work.

                                   

                                  "|StartTime |EndTime |Reason |Comment |Locked"

                                   

                                  The execution time was reduced to 3 seconds using FindObjectFields.

                                    • Re: Fastest Way to Retrieve Events from AF
                                      Rick Davin

                                      I am anxious to see this through.  We are always looking for successful cases, particularly with lightweight searches.  For the fields definition, anything that is an event frame property should be written with the proper name and NO |.  Anything that is an attribute must contain a |.

                                       

                                      I came up with my own version of GridEvent.  This may be thought of a DTO (Data Transfer Object) or a POCO (Plain Old Class Object).  Note I include the Event Frame ID, just in case you ever need to find the source event frame.

                                       

                                        public class GridEvent
                                          {
                                              public string Name { get; set; }
                                              public AFTime StartDate { get; set; }
                                              public AFTime EndDate { get; set; }
                                              public double Duration { get; set; }
                                              public string Reason { get; set; }
                                              public string[] ChangeHistory { get; set; }
                                              public string Comment { get; set; }
                                              public bool Locked { get; set; }
                                              public Guid EventFrameID { get; set; }  // for more direct link back to source Event Frame
                                          }
                                      

                                       

                                       

                                      Then I have this method to drive it all.  Note I have not tested this but the code compiles correctly.

                                       

                                            public void LightWeightSearch(AFDatabase afDatabase)
                                              {
                                                  var eventTemplate = afDatabase.ElementTemplates["Chimiwungo Down"];
                                      
                                                  string query = $"Template:'{eventTemplate.GetPath(afDatabase)}'";
                                      
                                                  List<GridEvent> geList = new List<GridEvent>();
                                      
                                                  // In fairness, to measure the time for the AFEventFrameSearch, you should limit the stopwatch to the search
                                                  // itself.
                                                  Stopwatch sw = Stopwatch.StartNew();
                                      
                                                  using (var search = new AFEventFrameSearch(afDatabase, "*", AFSearchMode.Overlapped, new AFTime("2018-01-01"), new AFTime("2019-03-13"), query))
                                                  {
                                                      // Opt-in to server-side caching
                                                      search.CacheTimeout = TimeSpan.FromMinutes(10);
                                      
                                                      // The order of these fields determines the order of returned values in IList<object> below.
                                                      // Attributes require a preceding | and anything with an embedded blank needs to be wrapped in quotes.
                                                      // Attributes are returned as AFValue.
                                                      var fields = "Name StartTime EndTime Duration |Reason '|Change History' |Comment |Locked ID";
                                      
                                                      //Define your custom factory to map back to your class
                                                      Func<IList<object>, GridEvent> factory = (values) =>
                                                      {
                                                          // The values[index] correspond to order in variable "fields"
                                                          var dto = new GridEvent();
                                                          dto.Name = (string)values[0];
                                                          // if you wanted to use DateTime, change to: = ((AFTime)values[1]).LocalTime;  // Or .UtcTime
                                                          dto.StartDate = (AFTime)values[1];
                                                          dto.EndDate = (AFTime)values[2];
                                                          dto.Duration = ((AFTimeSpan)values[3]).ToTimeSpan().TotalHours;
                                                          dto.Reason = ((AFValue)values[4]).Value.ToString();
                                                          dto.ChangeHistory = ((AFValue)values[5]).Value as string[];
                                                          dto.Comment = ((AFValue)values[6]).Value.ToString();
                                                          dto.Locked = (bool)(((AFValue)values[7]).Value);
                                                          dto.EventFrameID = (Guid)values[8];
                                                          return dto;
                                                      };
                                      
                                                      var geRecords = search.FindObjectFields(fields, factory);
                                      
                                                      foreach (var geRecord in geRecords)
                                                      {
                                                          geList.Add(geRecord);
                                                      }
                                                  }
                                      
                                                  sw.Stop();
                                      
                                                  // Time to sort this is done outside of the AFSearch.
                                                  // With roughly 3000 items, it should be fast with low memory consumption.
                                                  geList = geList.OrderBy(x => x.StartDate).ToList();
                                      
                                                  Console.WriteLine(sw.ElapsedMilliseconds / 1000.0);
                                                  Console.WriteLine(geList.Count);
                                                  Console.ReadLine();
                                              }
                                      

                                       

                                      Give that a try.  Let me know what does or doesn't work.

                                        • Re: Fastest Way to Retrieve Events from AF
                                          fmocke

                                          Hi Rick,

                                           

                                          My code looks similar to yours, however that 'factory' Func was pretty interesting, have not used something like that before.  Very handy!!

                                          My main problem was I included StartTime and EndTime with a pipe character in front.  All I had to do was remove the pipe character.

                                           

                                          1.5 seconds execution time! 

                                           

                                          Thanks for your support, really appreciate.  This is the kind of performance I expected from OsiSoft

                                            • Re: Fastest Way to Retrieve Events from AF
                                              Rick Davin

                                              AWESOME!  From 60 seconds to 8 to 3 and finally to 1.5 seconds.  My job is done here.

                                               

                                              I do encourage you to watch the video.  Reading the blog would help too.  My goal when creating those wasn't just to explain it to customers.  It was to explain it to myself first!  That's right, I was feeling very inadequate on the subject of AFSearch and made it my goal to understand it better.  I was a former customer, now an OSIsoft employee, and thought "If I don't feel comfortable fiddling with this, imagine how a customer feels."