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.
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?
The PI OLEDB provider counts on the client side.
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