AnsweredAssumed Answered

Fastest way to retrieve last available value from picomp2 in particular time range

Question asked by adamcp on Mar 8, 2017
Latest reply on Mar 8, 2017 by bpayne

Hello,

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?

Outcomes