10 Replies Latest reply on Sep 17, 2012 5:58 PM by williamsd

    Performance tests in getting EventFrames

    MGendron

      Hi VCampus!

       

      I'm new to the AF SDK and am currently reviewing the performance of the EventFrame functionality. We want to know how fast EventFrame's fetching functions are and if they are a viable option for quick reporting. We need to know if it's at least as fast as the next PI OLEDB release, otherwise we'll just use SQL queries through OLEDB.

       

      We wrote a test program to benchmark all the "find" methods of the EventFrame namespace. Each method is called between two Date.Now and we calculate the resulting timespan. We work with 40000 EventFrames spread across 20000 minutes before the 2011-09-01. Each EventFrame come from a template and has 2 attributes, a double and a string, each indexed. Each double attribute was set a random value between 0 to 1000 and each string attribute was set a random string between "Code1" to "Code10".

       

      So here's the results:

      • AFEventFrame.FindEventFrames: 40002 frames in 8 secs, 846 millis
      • AFEventFrame.FindEventFramesByAttribute (String attribute matching "Code2" or "Code3"): 8034 frames in 2 secs, 656 millis
      • AFAttribute.FindEventFrameAttributes: 40002 frames in 2 mins, 51 secs, 611 millis. Filling a listbox with the double attribute values from the resulting collection: 6 secs, 862 millis
      • Getting the EventFrame count grouped by "Code" with a SQL query: about 1 secs

      We have 2 problems with these results:

      1. The difference between the FindEventFrames and FindEventFrameAttributes is that one preload all the values right away. If you want to use an attribute value from every EventFrame returned, you end up with the same time, around 3 minutes, which is way too long for creating a report.
      2. If we only want to count the numbers of EventFrame per "Code" (the values of the string attribute) then the FindEventFramesByAttribute works ok (2.5 secs). But you would have to use the method for each known "Code", multiplying the required time. And you need to know the possible values in advance.

      In both case, a SQL query against AF tables is a much faster option. So what do you think? Is there a method or parameter in the AF SDK I missed? Will the performance be improved? Or am I trying something that EventFrames are not meant to do? Will the performance of the incoming PI OLEDB for EventFrame be comparable to working directly with SQL?

        • Re: Performance tests in getting EventFrames
          cnelson

          Hi Mathieu,

           

          Glad to hear you are working and using PI Event Frames!  However, I do have to say that the times you report above are not consistent with our internaly benchmarks so I'm going to have to ask a series of questions.

           

          1)  What Version of PI AF are you using?

           

          2)  What are the hardware specifications of you PI AF SQL Database?

           

          3)  How many total Event Frames do you have in your system?  You can get this by going to the database properties page of PI System Explorer and selecting the counts tab.

           

          4)  Do your Event Frames have a hierarchy or are they all created at the Database/Root Level?

           

          That should get us started. If you can also expand on what you are trying to accomplish once you pull all these Event Frames back, that may help us figure out which calls are better than others.

           

          Cheers - Chris

          • Re: Performance tests in getting EventFrames
            pcombellick

            Are you using the AFEventFrame.LoadEventFrames method ?

              • Re: Performance tests in getting EventFrames
                MGendron

                Hi guys,

                1. The versions i'm using are: AF Server 2.3.0.4048 and AF SDK 2.3.1.4095
                2. The PI AF database run on a SQL Server express 2008R2 (n° 10.50.1600).
                  1. It run on a windows XP SP3 VMware OS.
                  2. Both the SQL and PI AF servers run on this VM image.
                  3. The VM image has these ressources: 2.337 Ghz and 1024 MB.
                  4. The "Server" VM image and my test application run on the same computer.
                3. There are 40002 frames. I controlled this number for my test.
                4. No hierarchy. Always had the root parameter set to nothing in my code.

                We're trying to do a top 10 on our data. And we dont know in advance the possible atrribute values. So fetching all the frames and then accounting each value by code seems the best way.

                 

                As for the LoadEventFrames method, i'll do some more test on it.

                 

                Thx for your time!

                  • Re: Performance tests in getting EventFrames
                    MGendron

                    Alright,

                     

                    I've added the LoadEventFrames method to my test and indeed it make a good difference. It take about 30 seconds to get all 40000 frames and load them while the FindEventFrameAttributes take almost 3 minutes to do the same. Still, 30 seconds is too long for reporting when a direct SQL query can get the final result in a few seconds...

                     

                    Mathieu G.

                      • Re: Performance tests in getting EventFrames
                        pcombellick

                        In AF 2.3, Eventframes are still in preview status.  The official release of EF is part of AF 2010 R3 (AF 2.4).  The built in queries will never solve 100% of query requirements.  Benchmarking pre-released EF may not yield the same results as benchmarking the released version of EF.  Certainly loading an entire database in memory, will not be possible as a database grows beyond the amount of memory available.  EF support is coming in PIOLEDB Ent.

                         

                        Direct table access is not encouraged and the table schema is not documented as we plan schema changes to improve functionality and performance.  Direct table access also means that you will have to handle datareference evaluation, security, and high availability.

                          • Re: Performance tests in getting EventFrames
                            MGendron

                            Hi Paul,

                             

                            I understand the EF being in preview, my results are not really valid. Yet that's the idea of a CTP, to test the limits of a product. This was part of the question; is there a performance gain to be expected with EF official release ? Sure, one can't expect the framework to solve every problem possible, but i feel that getting a large but reasonable number of EF with minimal filtering would be the first problem solved by the framework, no ? Now, we can argue on the "reasonable" part but 40k EF, in our experience, is something plausible that we need to work with. 

                             

                            EF idea is a great addition to AF and there's tons of things we can do with it. We just happens to try it for reporting purpose and are not sure of the results. Maybe PIOLEDB is more suited for what we are trying to do? Would it be faster?

                             

                            Thx and keep up the good work!

                    • Re: Performance tests in getting EventFrames
                      pcombellick

                      40k EF is a reasonable number.

                       

                      Sounds like this type of query is better suited for PIOLEDB Ent that for EF search methods.

                       

                      For EF performance in general, the released version has improved performance v. the CTPs.

                       

                      EF support is coming in PIOLEDB Ent.  Check the Engineering plan for the schedule.

                        • Re: Performance tests in getting EventFrames

                          This is definitely the kind of use cases that will be better addressed through PI OLEDB Enterprise. As Paul pointed out, we are working on that so you can check the public PI System Roadmap for more information (on the technical support website); it is currently expected to release along with our big PI Server 2012 release.

                           

                          A top 10 of downtime codes is a great use case - are there any others you would like to share with me here (or via email)? Also, out of curiosity, what environment are you planning to build reports in?

                           

                          @All: please feel free to answer the 2 questions above too, not only Mathieu ;)

                            • Re: Performance tests in getting EventFrames
                              williamsd

                              Facing the same issue for reporting purposes, retrieving data from event frames is slow due to the fact that AF retrieves the value from PI at run time for all PI point Data references. I circumvent this by creating my event frames, retrieving the value of my PI point Data references, then storing the values in static attributes in the same event frame as I create them. Retrieving the values from static attributes is much faster. Drawback is if there is ever a need to alter the raw PI point data the static attributes would get out of sync, thus, additional code to keep things in sync is required.