The PI OLEDB Provider has a number of options when it comes to performing queries that contains functions and calculations.
Note: This document expands on the content covered from 4:49-7:52 in the Not authorized to view the specified discussion 2660 as well as providing the information in written form.
The three options are the following:
- Create a function view in the Function catalog
- Use the Picalc table in the Piarchive catalog
- Use a performance equation in the SELECT clause of the query
Each have their difference in terms of usability and performance. Performance can be difficult to predict, however, it's best to simply write the query a couple different ways and execute them in PI SQL Commander to determine which is the fastest approach for that specific query. For usability, each approach should work for third party clients using the PI OLEDB Provider directly, but in situations such as MS SQL Server linked servers, OpenQuery() will need to be used if there's a performance equation in the select clause while the picalc table and custom functions should execute without error.
To show each of these, let's try to find the number of seconds that sinusoid has been above 50 in the past day.
In PI SQL Commander, you can navigate to the PI OLEDB Provider > [PI Data Archive] > Catalogs > pifunction > Views, and see that it starts as an empty folder.
This is because there are a very large number of options and overloads, and we allow users to enable specifically the ones that they need. To do this, navigate in Windows Explorer to:
- %pihome%\OLEDB\Tools\pifunction Catalog Manager
- Run pifunctionCatalogMgr.exe as administrator
From here, find the function in the list that you would like to use. Notice that there is an overload for every combination of allowable datatypes for each argument. In our case, we would like to use the TimeGT function which has 12 overloads (4 arguments with 2 datatypes per arguments minus certain unallowable combinations results in 12 combinations).
To determine which combination is the one you need, go to the documentation for the performance equation within the PI Server documentation. In our case, we're interested in the TimeGT Performance Equation .
From the documentation we see what the four arguments are, and these match up in order for the OLEDB function that we're creating.
In our case, we'd like to create the function that allows us to provide a string name for the tag (arg1 = WString), a DateTime for both the start and end times (arg2,arg3 = DateTime), and a numeric value for the fourth argument since sinusoid is not a digital state tag (arg4=Float64).
Once the correct combination is selected, we hit "Create Selected" to create the function view.
Once it's created, return to PI SQL Commander and refresh the views to see that it exists, and expand the columns list to confirm that the correct option was created.
Note: a full refresh of either disconnecting and reconnecting to the PI Data Archive may be necessary to see new pifunction view, or even closing and reopening PI SQL Commander.
To query this view, the arguments need to be passed according to the above documentation:
WHERE [arg1] = 'sinusoid'
AND [arg2] = '*-1d'
AND [arg3] = '*'
AND [arg4] = 50.0
picalc table in piarchive catalog:
This whole process can be simplified by specifying the performance equation syntax in the [expression] in the picalc table, as shown here:
SELECT value, CAST(CAST(value as float64) as Time) as castedValue
WHERE [expr] = 'TIMEGT(''sinusoid'',''*-1d'',''*'',50)'
AND time = '*'
Note: in this query, the picalc table returns the number of seconds, but the output type is variant. In order for it to be casted to Time, to show the hh:mm:ss syntax, it needs to be first casted as a numeric type.
Performance Equation in the SELECT Clause:
This is the simplest method of all, where you don't need to access any of the PI OLEDB Provider tables:
This query will execute successfully:
Alternatively, it can be combined with existing PI OLEDB Provider query results in manners such as this:
SELECT tag, TimeGT(tag,'*-1d','*',50) as value
WHERE [pointsource] = 'R'
performance comparison (and show logs):
The final step is to choose the correct option for the situation. the pifunction view is the most intrusive in terms of setting up, but it's part of the schema so that leads to situations where it would be beneficial over the picalc table's usage of expressions. If the decision comes down to performance, it can be useful to run each one against your specific query to test for the best option.
To start, we'll enable OLEDB logging in PI SQL Commander while connecting to the PI Data Archive:
Next we'll run each of the three queries that outputs the single value as a Time datatype, then look at the logs.
Note: remember to highlight an informative title comment when running the query to make it easier to find in the logs
If you see the "SQL Engine" messages, you can see that the three approaches are very different in terms of how they were executed.
Note: Going through these logs is not a topic or goal of this course, but it's being used here to demonstrate that there are differences in these methods, and due to these differences, there will be performance differences in non-trivial queries. This is important to understand, and the logs help to visualize this.
For each of these queries, the execution time is shown both in the logs and in the PI SQL Commander results output, and this is the most important factor.