hjkuyp

PI OLEDB Query with large result set taking too long to complete

Discussion created by hjkuyp on Jun 26, 2012
Latest reply on Jun 26, 2012 by hanyong

We are attempting to obtain the snapshot value for appoximately 30,000 tags every 5 minutes using the following PI OLEDB within a PowerShell script:

 

select t1.tag, t1.value, t1.status from piarchive..pisnapshot t1 inner join pipoint..pipoint2 t2 on t1.tag = t2.tag where t1.tag like 'EMS.ANALOG.*' and t2.scan <> 0

 

The results are being returned successfully but it takes almost 10 minutes for this to complete. The INNER JOIN appears to be what is killing the query performance but we need to identify tags that are no longer collecting the data and filter them from the results. Without the join it completes in about 3 minutes.

 

We've also tried breaking this up into multiple smaller queries but this still doesn't allow us to meet our 5 minute constraint.

 

Does anyone have any suggestions to optimize this query to run more quickly? If there is a better way to accomplish this I'd certainly be open to suggestions as well.

Outcomes