20 Replies Latest reply on Dec 28, 2018 3:03 PM by Rick Davin

    what is the best way to display large set of data in WPF application.

    mdspath

      Hello,

       

      We have a AF SDK based WPF application which retrieves the tag data for a given interval of time. Below is the code.

       

      var config = new PIPagingConfiguration(PIPageType.TagCount, 10000);
      IEnumerable<IDictionary<AFSummaryTypes, AFValues>> listAvgResults = 
              pointList.Summaries(myTimeRange, span, type, AFCalculationBasis.TimeWeighted, AFTimestampCalculation.Auto, config);
      IEnumerable<AFValues> afValues = listAvgResults.Select(pointResults => pointResults[type]);
      
      foreach (AFValues pointValues in afValues)
      {
          // constructing the data table here
      }
      

       

      for less number of tags and time interval for 1 hour this code works fine. But when the time interval is for 10 sec for 3 weeks and number of tag is 20, this foreach runs take lot of time to execute (min 1 min) .

       

      while searching i came across OLE DB where there is access to PI archive for getting data..Since the output from the query is directly in DataTable, should I use this ?

       

      And please provide the download link for OLE DB enterprise 2017,

      Download links in the below URL are broken

       

      PI OLEDB Enterprise - Announcements

       

      Thanks

      Mike

        • Re: what is the best way to display large set of data in WPF application.
          gregor

          Hello Mike,

           

          The general recommendation is to break larger requests for data into smaller chunks e.g. by creating multiple smaller PIPointList objects or by breaking a larger AFTimeRange into smaller ones. Because you like to display data in a table and I expect you do this with a single Timestamp column and one column for each PI Point, the second option is likely the preferred choice.

           

          When asking for recorded values, the challenge is that you usually do not know the sampling rate of single PI Points which makes it a little difficult to estimate what the TimeRange for a single query should be. The PIPointList.Summaries overload you are using expects an AFTimeSpan which is used as the sampling interval for summaries. With this information and the amount of PIPoint objects in your PIPointList you could calculate the amount of values returned by the summaries call upfront and use this information to break a larger TimeRange into smaller ones. It may be necessary to find a good value for the amount of values to ask for a single call by testing what works. I would start using something between 10,000 and 100,000 values in total. 

           

          For PI Points that receive updates at a high frequency, there is still the chance a summary could fail e.g. because the amount of archived events for the TimeRange used exceeds ArcMaxCollect which is a Tuning Parameter intended to protect the PI Data Archive against expensive queries. ArcMaxCollect limits the amount of archived events that can be retrieved with a single call to PI Data Archive. My suggestion is to use TimeRanges of 3~7 days to be on the safe side.

           

          I have checked the download links you mentioned to be broken and found them to work. When checking why they may not work for you, I found that your SSO account is not linked to any site which means you don't have access to any install kit. Please note there was recently a change and these days Developer Technologies are no longer freely available. Please refer to AL00342 - Developer Technologies returning to being distributed with regular licenses.

          If you feel your SSO account should allow you to download, please shoot me a private message.

           

          Your application is working with PIPoints. PI OLEDB Enterprise is the Asset Centric OLEDB provider. It does not offer direct access to PI Points. Queries for time series data like recorded values and summaries will have to include AF Attribute references. There is another OLEDB provider also referred to as PI OLEDB (Classic) Provider which offers access to the PI Data Archive. PI OLEDB (Classic) Provider is built against PI SDK which means it does not offer bulk calls. Bulk calls allow asking data for multiple data items (PI Points or AFAttributes) where introduced with AF SDK and are only existent in the library also referred  to as RDA (Rich Data Access). Examples for bulk calls are the PIPointList data calls like Summaries and RecordedValues. While it is possible to execute a query against PI OLEDB (Classic) Provider for multiple PI Points, they will result into multiple PI SDK calls - one for each PI Point. ArcMaxCollect limitations need to be considered with PI SDK and PI OLEDB  Providers too.

          2 of 2 people found this helpful
            • Re: what is the best way to display large set of data in WPF application.
              mdspath

              Hi Gregor ,

               

              Thanks for the response,

               

              The return from pointList.Summaries has got all the data correct ? while iterating it is not calling any sdk call right ?

               

              how retrieving in parts (3 to 7 days) improve the performance ? Because number of sdk calls will increase right ?

               

              Thanks

                • Re: what is the best way to display large set of data in WPF application.
                  gregor

                  Hi Mike,

                   

                  You are right. Breaking a large data query into smaller chunks likely doesn't reduce the overall amount of time to get the data or if it does, the change will not be huge. However, the first chunk will return way faster than your 'huge' query and you can start processing the data and continue executing the remaining chunks. This can but doesn't have to be done necessarily in a different thread. With regards to your use case this means you would built the table grid and start adding data after receiving the response to the first query. You can execute the additional query chunks and add the data as it arrives.

                   

                  One thing which I don't know about is if you are offering the data in a huge grid or if you offer it in pages. With the data request you already need to specify a paging configuration. You could use this also when offering the data to users. I agree the development effort is higher but the time until users get the first data for their query will be short and they will be happy with the user experience. Another positive aspect is that you application will work at scale when using a reasonable paging approach.  

                  2 of 2 people found this helpful
                    • Re: what is the best way to display large set of data in WPF application.
                      mdspath

                      hi Gregor,

                       

                      We have to retrieve the data not only for showing them in the datatable but also for ploting, filtering (telerik filtering)

                       

                      The object which is returned is taking more time when compared to the same amount items in artificial generated.

                       

                      Artificial data. This takes only 2 sec to process but

                       

                      try
                      {
                          DataTable dt = new DataTable();
                          List<double[]> temp = new List<double[]>();
                      
                          for (int i = 0; i < 13; i++)
                          {
                              double[] tt = new double[151404];
                              temp.Add(tt);
                              dt.Columns.Add("Column" + i);
                          }
                      
                          for (int i = 0; i < 151404; i++)
                          {
                              var row = dt.NewRow();
                              dt.Rows.Add(row);
                          }
                      
                          List<List<int>> dfg = new List<List<int>>();
                      
                          var op = Parallel.ForEach(temp, tt =>
                          {
                              dfg.Add(NewMethod(tt));
                          });
                      }
                      catch (Exception ex)
                      {
                      
                      }
                      

                       

                      private static List<int> NewMethod(double[] tt)
                      {
                          List<int> ty = new List<int>();
                          for (int i = 0; i < tt.Count(); i++)
                          {
                              //dt.Rows[i][0] = tt[i];
                              ty.Add(i);
                          }
                          return ty;
                      }
                      

                       

                       

                      Original data. this takes 17 secs

                       

                      var config = new PIPagingConfiguration(PIPageType.TagCount, 10000);
                      IEnumerable<IDictionary<AFSummaryTypes, AFValues>> listAvgResults = 
                              pointList.Summaries(myTimeRange, span, type, AFCalculationBasis.TimeWeighted, AFTimestampCalculation.Auto, config);
                      IEnumerable<AFValues> afValues = listAvgResults.Select(pointResults => pointResults[type]);
                      
                      var te = Parallel.ForEach(afValues, pointValues =>
                      {
                          NewMethod(batchID, vesselName, decimalPlaces, selectedVesselParameter, pointValues, lstParameters);
                      }
                      

                       

                      Is there any efficient way to SDK call ?

                       

                      How does the Excel loads data from PI so fast ? does it uses AF SDK call ?

                       

                      Basically summaries method return huge data which is not needed for us, so is there any other method for easy processing

                       

                      Thanks

                        • Re: what is the best way to display large set of data in WPF application.
                          gregor

                          Hi Mike,

                           

                          I suggest we keep the visualization piece aside and focus on the data retrieval. We could probably help you with some code snippet, preferably as console application since this allows to stay away from everything UI specific and to just focus on efficient data retrieval through AF SDK.

                           

                          For plotting, please consider using PIPointList.PlotValues and please note there is already a pretty short example.

                           

                          Mike Spath wrote:

                           

                          Basically summaries method return huge data which is not needed for us, so is there any other method for easy processing

                           

                           

                          That's indeed the right thing to ask. If summaries take long to complete and delivers way more data than you need (a user can consume) than there appears to be potential for optimization. Can you indicate what you really need?

                          Sometimes it is also reasonable to prepare the data e.g. by using Asset Analytics to calculate certain KPI's continuously for later usage in a custom client application.

                          1 of 1 people found this helpful
                          • Re: what is the best way to display large set of data in WPF application.
                            gregor

                            Sorry, I forgot to answer your question why PI DataLink loads PI Data into Excel so fast. The current version uses AF SDK for the purpose.

                             

                            You may be interested in PI SDK/AF SDK Equivalents for DataLink Functions shared by Gavin Chen 

                            To avoid any misunderstandings, the usage of PI SDK is not recommended because it is announced for deprecation and also is a poor choice when you are after performance.

                              • Re: what is the best way to display large set of data in WPF application.
                                mdspath

                                Hi Gregor,

                                 

                                Actually I am using AF SDK (I have mentioned in the question). Does using AF Data namespace improve any performance ?

                                If so, could you please give the web link or sample code to retrieve the data.

                                 

                                Thanks

                                  • Re: what is the best way to display large set of data in WPF application.
                                    gregor

                                    Hi Mike,

                                    Mike Spath wrote:

                                     

                                    Does using AF Data namespace improve any performance ?

                                    If so, could you please give the web link or sample code to retrieve the data.

                                     

                                    At least the OSIsoft.AF.Data namespace offers some alternatives and those potentially offer performance improvements or better user experience.

                                    To give you an example, when rendering a trend, we recommend asking PlotValues but you will want to update the trend every few seconds. For this purpose, signing up for updates (keywords PIDataPipe or AFDataPipe) is preferred upon asking PlotValues again with each data update event. PI Clients like PI ProcessBook and PI Vision are operating this way.

                                     

                                    Using an asynchronous call for data can also help to improve the user experience as it allows to update the UI as soon as data is received. However, when comparing a synchronous with an asynchronous request, we don't expect a significant difference. Especially when dealing with larger amounts of data, the majority of time is spend on the server preparing the data.

                                     

                                    I suggest you to watch the recording of the PI Developers Club Webinar Series - New Features and Best Practices with PI AF SDK 2016 and to read Async with PI AF SDK: Introduction and Async with PI AF SDK: From Tasks to Observables.

                                     

                                    There are several discussions related to data retrieval performance and I cannot get tired to remind that the subject starts with the data acquisition and to find proper settings for Exception and Compression.

                                      • Re: what is the best way to display large set of data in WPF application.
                                        mdspath

                                        Hello Gregor:

                                        Thank you for that feedback on using AFDataPipe. Way back when, when we started using the AF SDK, I was looking into the possibility of using the AFDataPipe but at the time, we did not have any issues with running. Now we have a mature product that is finding its way into working with larger data sets.

                                        I will dig back into DataPipes and see if I can make it work.

                                        Cheers!

                                        Mike

                                        • Re: what is the best way to display large set of data in WPF application.
                                          mdspath

                                          hi Gregor

                                           

                                          could you please give sample on how to use AF DATA sdk to get the data just like mentioned in the main question that I asked

                                           

                                          thanks

                                          mike

                                            • Re: what is the best way to display large set of data in WPF application.
                                              gregor

                                              Hi Mike,

                                               

                                              I am not exactly sure what you are asking but guess you might be interested in a sample using SummariesAsync for a PIPointList object. Please note that the SummariesAsync requires a PIPoint or AFAttribute object. It does not work with PIPointList or AFAttributeList.

                                               

                                                  class Program
                                                  {
                                                      static string host = "pidataarchive";
                                                      static PIServer pi;
                                                      static void Main(string[] args)
                                                      {
                                                          pi = new PIServers()[host];
                                                          string ptQuery = "Pointsource:R DataType:Float";
                                                          PIPointList ptList = new PIPointList();
                                                          IEnumerable<PIPoint> pts = PIPoint.FindPIPoints(pi, ptQuery, false, null, AFSearchTextOption.ExactMatch);
                                                          ptList.AddRange(pts);
                                                          AFTime omega = AFTime.NowInWholeSeconds;
                                                          AFTime alpha = omega.LocalTime.AddDays(-1);
                                                          AFTimeRange timeRange = new AFTimeRange(alpha, omega);
                                                          AFTimeSpan timeSpan = new AFTimeSpan(0, 0, 0, 1, 0, 0, 0);
                                                          ShowTimeWeigthedAvgsAsync(ptList, timeRange, timeSpan).Wait();
                                                          Console.Write("Done. Press any key to quit .. ");
                                                          Console.ReadKey();
                                                      }
                                                      static async Task ShowTimeWeigthedAvgsAsync(PIPointList pointList, AFTimeRange tRange, AFTimeSpan tSpan)
                                                      {
                                                          foreach (PIPoint point in pointList)
                                                          {
                                                              IDictionary<AFSummaryTypes, AFValues> summaries = await point.SummariesAsync(tRange, tSpan, AFSummaryTypes.Average, AFCalculationBasis.TimeWeighted, AFTimestampCalculation.Auto);
                                                              AFValues avgs = summaries[AFSummaryTypes.Average];
                                                              foreach (AFValue avg in avgs)
                                                              {
                                                                  Console.WriteLine("{0}: {1} at {2}", point.Name, avg.Value, avg.Timestamp);
                                                              }
                                                          }
                                                      }
                                                  }
                                              
                                                • Re: what is the best way to display large set of data in WPF application.
                                                  mdspath

                                                  hi Gregor,

                                                   

                                                  At least the OSIsoft.AF.Data namespace offers some alternatives and those potentially offer performance improvements or better user experience.

                                                   

                                                  i was referring to this.

                                                   

                                                  Thanks

                                                    • Re: what is the best way to display large set of data in WPF application.
                                                      gregor

                                                      Hello Mike,

                                                       

                                                      Please accept my apologies as it looks that I need to adjust this statement. OSIsoft.AF.Data offers alternatives but the class is for asset centric data retrieval. The methods for PI Point centric data retrieval are offered through the OSIsoft.AF.PI class.

                                                      Depending on the requirements there are methods which may be preferable e.g. the usage of an asynchronous method could allows updating the UI with data as it is received while additional asynchronous data request are still serviced. Another example is a sign-up for updates (PIDataPipe / AFDataPipe) which is an elegant approach to get only the delta between 2 request. Updates are delivered pretty quick without generating additional load e.g. to PI Archive Subsystem.

                                                      A bulk call like PIPointList.Summaries is already a good choice with regards to performance because it allows retrieving data for multiple PI Points in a single call to the server. We recommend breaking larger calls into smaller chunks to avoid sending expensive queries with potential to time out or running into limitations as with ArcMaxCollect being exceeded. There is theoretically the chance to improve the overall performance by executing multiple data request in parallel but we recommend using this approach cautiously to avoid too much interference or impact on other client operations.

                                                       

                                                      If you are not happy with the performance you are getting when asking averages with PIPointList.Summaries, please consider looking into the count summary to get an idea how much events need to be loaded by the server to do the calculation.

                                                    • Re: what is the best way to display large set of data in WPF application.
                                                      mdspath

                                                      Hi Gregor:

                                                      In trying to run the code example you gave, the await never line never returns, when we took that out and used point.Summaries, that returned but we are wondering if there is benefit to using the async. Any suggestions ?

                                                      Thank you,

                                                      Mike

                                    • Re: what is the best way to display large set of data in WPF application.
                                      Rick Davin

                                      Hi Mike,

                                       

                                      I performed edits to format your code.

                                       

                                      The title is very misleading.  This thread has absolutely nothing to do with displaying a large set of data in a WPF application.  It instead is about retrieving summaries of a large set of PI data.  That's the real challenge.

                                       

                                      AF SDK is the fasted of the Developer Technologies.  So we can rule out PI OLEDB.

                                       

                                      Do you have sub-second data?  What is the typical data rate?  And the fastest? And the slowest?

                                       

                                      How many tags are we talking about?  20?  Do all 20 tags have a similar data density?  E.g. do they all have around 1 million values for the requested time range, or do some have around a million and others have a thousand?

                                       

                                      What is your time range being used?  3 weeks?  And the interval is 10 seconds?  Please confirm.

                                       

                                      I would use a lower tag count here:     var config = new PIPagingConfiguration(PIPageType.TagCount, 10000);

                                       

                                      I'd go with 500, 250, or even 100 for dense summaries.  If you have such dense data, you may even need to drop to 50.  The idea here is that once a given page of summaries has been filled, it sends it to the client for additional processing, such as display.  So the client app does not need to wait forever and ever, but can keep busy with partial results as they stream in.  The happy medium would be those numbers of tags that the client needs to fully process before it waits for the server to send more.

                                       

                                      I see you use TPL calls.  Hard to tell from the raw code, but it seems to be a new parallel task would be called for each new tag.  Am I right?  And how many tags are we talking about?  20?  Please confirm.

                                       

                                      More to my point ... I do not think you need parallel here.  Or you should definitely profile it for performance timing.  It seems to me that the largest time is spent waiting on PI data to be retrieved.  A simple serial loop should suffice and perform better, especially if your PIPagingConfiguration is tuned better for your environment.

                                        • Re: what is the best way to display large set of data in WPF application.
                                          mdspath

                                          Hello Rick,

                                           

                                          Comments in Red.

                                           

                                          Do you have sub-second data?  Yes we allow the user to dial in 5 sec. to days, weeks 

                                          What is the typical data rate? 1 hr. And the fastest? 5 sec. And the slowest? 24 hr.

                                           

                                          How many tags are we talking about? We have multiple data sources, typically they have 10 tags each but in some cases 1 datasource can have 100 pipoints 20?

                                          Do all 20 tags have a similar data density? They all have similar density’s, not sure of amount E.g. do they all have around 1 million values for the requested time range, or do some have around a million and others have a thousand?

                                           

                                          What is your time range being used?  3 weeks?  2 – 4 weeks. And the interval is 10 seconds?  5 sec.

                                           

                                          Thanks

                                          Mike

                                            • Re: what is the best way to display large set of data in WPF application.
                                              Rick Davin

                                              Hi Mike,

                                               

                                              I don't know if you understood what I meant on data rate.  You say the typical data rate is a reading once every hour, but that you have sub-second data.  And the fastest data rate is every 5 seconds.  If your fastest data rate is 5 seconds, why would you need sub-second data?  The reason for sub-second data is when the data rate is faster than 1 second.

                                               

                                              For tags with a 1 hour data rate, its nonsensical to ask for 5 second data intervals.  You would not be showing the user anything new, other than pollute their screen with way too much noise.  Imagine driving in a car when your exit is 1 hour away, but every 5 seconds the children in the back keep asking "Are we there yet?"

                                               

                                              Now let's have fun with simple math.  A reading every 5 seconds is 7200 in a day.  Taking your low end of 2 weeks, that results in 100800 values.  For 1 tag.  Your end users trying to dial in to 5 second readings are going to get lost with too much data.

                                               

                                              Here are a few suggestions or alternatives to consider.

                                               

                                              Stop aggregating with a time interval smaller than the data rate for a given tag.  I would go even further and say to pick a time interval larger than that tag's data rate.  If you do have a 5 second tag, it's useless to aggregate it every 5 seconds.  The more performant operation would be just to display the tag's recorded or interpolated value.

                                               

                                              Consider displaying PlotValues to reduce the size of rows shown to the end user. 

                                               

                                              If you insist on overwhelming the user with a mountain of aggregated data, then consider an Analysis that persists the aggregation to a PIPoint.  This will make your WPF application perform better, though the end user will still be drinking from a fire hose of data.

                                               

                                              Given the disparity of your data rates, from a fastest of 5 seconds to a typical of 1 hour to the slowest of 1 day, consider having your application separate the tags according to their data rate, and perform the operations to each category (Fast, Typical, Slow) giving special handling to each case. 

                                               

                                              To have your WPF application seem more responsive, consider implementing a custom paging.  If you try to produce 100800 aggregated values, then the AF SDK calls will gather all 100800 results for 1 tag before working on the next tag.  This means Tag 1 would get its 100800 values while Tags 2-10 are still being aggregated, that is their columns are empty.  Instead, consider looping over a time range of 1 hour at a time.  That way all 10 tags would be shown for the first hour, and then your app loops over the next hour of data.  If your developer skills are top notch, then you could consider fetching 1 custom page to display, and also fetch the next anticipated page for quicker display when they scroll.  Each time there is a scroll request, display the pre-fetched page and then behind the scenes pre-fetch the next anticipated page.

                                                • Re: what is the best way to display large set of data in WPF application.
                                                  mdspath

                                                  Hi Rick:
                                                  We collect process data from various OPC sources, the data rate is set to 5 seconds. We allow the customer to filter (get summary data) from 5 sec. to days. So correct, the fastest data rate is 5 sec. We do not need sub second data. In a typical lab we have 300 tags and we need to make sure the customer can gather data over a 4 week period at 5 sec. resolution without timeouts.

                                                  I should have stated this in the beginning, we allow the customer to vary the search window but we should always assume that the customer wants to see data for 300 tags at 5 sec. resolution over 4 weeks. Of course we allow them to dial in a lower resolution.

                                                  Thank you for your suggestions on paging we will certainly look into and test that out!

                                                  Mike