4 Replies Latest reply on Feb 16, 2012 2:13 PM by mhamel

    Alternative to RecordedValuesAvailable

    cguimaraes

      Hi vCampus Team,

       

      There is any fast and easy alternative to the method "RecordedValuesAvailable"?

       

      I know that an option would be to load all the values to a PI Values and them count, but let’s suppose that I'm going to read 10 years of history, I would like to know previously the amount of events inside this timeframe before effectively read them.

       

      Thanks again

       

      Carlos

        • Re: Alternative to RecordedValuesAvailable
          Ahmad Fattahi

          Carlos,

           

          Unfortunately there is no other way to count the events in the archives at this point. This request has been around and the PI SDK team knew about it. However, the current structure only allows us to fetch and count by brute force.

           

          Hope it answers your question.

            • Re: Alternative to RecordedValuesAvailable
              MvanderVeeken

              I don't know the answer to this, but how does PIOLEDB handle a statement like "SELECT COUNT(*) FROM picomp where tag = 'sinusoid'". Would it first fetch all the values, and then execute the count client side?

                • Re: Alternative to RecordedValuesAvailable
                  andreas

                  The PI OLEDB provider counts on the client side.

                    • Re: Alternative to RecordedValuesAvailable
                      mhamel

                      It exists a better way to count events in the archive which is done on the server side. You can make use of the Summary or Summaries method to count the number of events for an entire range of time. This is a good approach for knowing how many events exist in the time range and then configure the proper strategy to utilize, so you can have different ones to better serve the end-user. For example, if you find that you would have to deal with more than 2 million events you might enable a filter to get a smaller data set to handle. I have added an example on how to use the Summary and Summaries methods.

                       

                      With Summaries method:

                       
                      'Objects
                      Dim Results as PISDKCommon.NamedValues = New PISDKCommon.NamedValues
                      Dim MyValues as PISDK.PIValues
                      Dim NumberOfEvents as int32
                      Dim MyPoint As PISDK.PIPoint
                      
                      'Get a reference on point CDT158.
                      MyPoint = _PIServerRef.PIPoints("cdt158")
                                     
                      'Perform an advanced calculation on the PI Server directly.
                      Results = MyPoint.Data.Summaries("*-7d", "*", btInside, asCount, 1, cbEventWeighted)
                      
                      'Extract the count from the NamedValues collection object.
                      MyValues = Results("Count").Value
                      
                      'Read the number of events.
                      NumberOfEvents = MyValues(1).Value
                      

                       

                       

                      With Summary method:

                       

                      'Objects
                      Dim MyValue as PISDK.PIValue
                      Dim NumberOfEvents as int32
                      Dim MyPoint As PISDK.PIPoint
                      
                      'Get a reference on point CDT158.
                      MyPoint = _PIServerRef.PIPoints("cdt158")
                      
                      'Perform an advanced calculation on the PI Server directly.
                      MyValue = MyPoint.Data.Summary("*-7d", "*", asCount, cbEventWeighted)
                      
                      'Read the number of events.
                      NumberOfEvents = MyValue.Value
                      

                       @Michael and Andreas: If you look carefully at how the PI OLEDB provider translates a counting events query such as this one:

                       
                      SELECT COUNT(*) FROM PICOMP2 WHERE TAG = 'cdt158' AND TIME BETWEEN 't-90d' AND 't'
                      

                       

                       

                      you will notice that the Summary method is used. I have added an excerpt of my log file below to show what it did.

                       
                      Execute     
                      Command to prepare: select count(*) from picomp2 where tag = 'cdt158' and time between 't-90d' and 't'
                      [piarchive..picomp2 picomp2] Column 'time' interval restriction: [18-Nov-2011 00:00:00, 16-Feb-2012 00:00:00]
                      [piarchive..picomp2 picomp2] [PI SDK] Server->GetPoints search: WhereClause = "tag" = "cdt158"
                      [piarchive..picomp2 picomp2] [PI SDK] PIPoint(cdt158)->Data->Summary search: StartTime = 18-Nov-2011 00:00:00, EndTime = 16-Feb-2012 00:00:00, SummaryType = astCount, CalculationBasis = cbEventWeighted
                      Execution time: 00:00:00
                      Execute     DB_S_ERRORSOCCURRED