I need to retrieve last available value from picomp2 archive for group of points for specified time range using PI Oledb.
I prepared following query:
SELECT A.* FROM piarchive..picomp2 A INNER JOIN ( SELECT tag, MAX(TIME) AS TIME FROM piarchive..picomp2 WHERE TAG LIKE '%tag_name' GROUP BY tag ) B ON A.time = B.time AND A.tag = B.tag
It does the job, but there are two problems:
1. Number of points selected at once. If there are more than 100 points then performance of the query is not satisfactory.
2. After adding time range to subquery (even the small one like 1h) performance drops drastically (even for one point).
In final process I will need to retrieve data for ~7000 devices at daily routine but my approach doesn't look promising.
I have tried splitting the query into steps (1. selecting max time 2. selecting value) and executing query in the loop tightening condition to result smaller rowset, but it didn't help much.
Is there any better way to achieve what I need using PI Oledb or maybe there are other, better methods for this task than oledb?