I have been given a spreadsheet that is being used to calculate control loop performance, but is causing performance issues on the archive subsystem.
To calculate the uptime of a control loop, there are 10 different formulas used (DataLink), each with two tags, and with each formula comparing each tag against a certain condition, and then calculating the percent true for both comparisons, over a time period. An example of a formula would be:
=PITimeFilter(('Tag1'="Cond1" and 'Tag2'="Cond2") , '01-Dec-2010' , '31-Dec-2010' , "30d" , "days" , 0 , "servername")/30
The performance issues I've managed to identify are:
- The same two tags are used in all 10 formulas, but compared against different conditions. Therefore, 30 days worth of data is being retrieved and compared for each tag, 10 times. In addition to this, these tags are on a frequent scan class and really have a lot of data stored in a 30 day period.
- There are over 500 control loops for which this data needs to be calculated for - Hence the huge load on the archive subsystem.
What I thought about doing was to use the SDK instead, through VBA.
My plan was to retrieve the 30 days worth of data for each tag once off and store in two PIValues objects, one for each tag, and then somehow perform the comparisons and get the percent true. I've had a look through the SDK help files, but couldn't find any methods/objects that provide this functionality.
Is this even possible? If not, is there perhaps a workaround?
Thanks in antipication.