Description of the challenge:
We are now in the process of developing a .NET application (a service) that will query PI records from piarchive..picomp2 for records periodically. We use PI OleDB interface towards PI. The number of tags that will be queried are approx. 3000. They will be queried for records automatically about every 60 seconds.
Today we use the following kind of query:
SELECT tag, time, value, status, DIGSTRING(status) statusText FROM piarchive..picomp2 WHERE tag = '18DYP1071A' AND time > CAST('2011-09-27 23:51:31.0000000' as DateTime) AND time <= CAST('2011-09-27 23:53:31.0000000' as DateTime) ORDER BY time ASC
As you can see from the query above each tag will be queried separately. This is quite a time consuming process. In our test system we have estimated the time for querying 900 tags to take about 70 seconds. It does not matter if the number of records returned from the queries in sum is 200 or 6000 records. The execution time is related to the sql query execution.
We use OleDbConnection and datareader for executing the query:
using (reader = db.ExecuteReader(sql))
For your information: Data read from PI will be serialized to a specific XML format and sent to other clients.
Is there a better way to query for the records? The reason way we create a query pr tag is that we want to query from the last received timestamp pr. tag, so that only records not sent to clients previously are fetched from the PI system.
I also wonder: Since we create queries pr. tag, will that have serious impact on the PI system?