AnsweredAssumed Answered

PI OLE DB Provider - Performance and Usability

Question asked by Steve.Braich on Feb 4, 2016
Latest reply on Feb 26, 2016 by pthivierge

Hello,

After making an Excel spreadsheet tool that compares PI tag values from two systems using VBA, I was hoping to step up to PI SQL.  I installed PI SQL Commander and have been able to add my PI Servers as Linked Servers in SQL Server Management Studio. However, I am not that impressed.  I kind of expected more from PI SQL, but I haven't really been able to get any of my queries to work, due to really poor performance.  They timeout.

 

I am about to give up on PI SQL.  Before I do, I was hoping perhaps some of you with more experience might give me some advice.

 

Here are the queries I would like to run:

 

  1. Find all Deprecated Tags
  2. Compare PI Points from two difference PI Servers
  3. Get tags with certain meta-data

 

I don't think these are absurd requests.  So below is a little more detail on each one.  These queries are run from within SSMS.

 

1. Find all Deprecated Tags

Find tags that haven't had any data for a while.  This times out.  I have about 500K tags.  Sure that's a lot, but I'm hitting the pisnapshot table.

 

    SELECT --TOP 1000

           tag

      FROM [PI SERVER].[piarchive]..[pisnapshot]

     WHERE time < '2016-01-01'

 

2. Compare PI Points from two difference PI Servers

So, I know this one might be a little expensive, but again, so be it. I would think the system should be able to handle it.  Any ideas on how to get this to work?

 

    SELECT --TOP 1000

           s1.tag,

           s1.[time],

           s1.value AS Server1_Value,

           s2.value AS Server2_Value

      FROM [PI SERVER 1].[piarchive]..[piinterp2] s1

INNER JOIN [PI SERVER 2].[piarchive]..[piinterp2] s2 ON s2.tag = s1.tag

     WHERE s1.[time] = '2015-12-02 12:00:00'

       AND s2.[time] = '2015-12-02 12:00:00'

 

3. Get tags with certain meta-data

This is more about using expensive 'LIKEs' than anything else.  LIKE seems to work when I remove the wildcard in front.  I can't really in this case.  Any suggestions?

 

    SELECT --TOP 1000

           *

      FROM [PI SERVER].[pipoint]..[pipoint2]

     WHERE descriptor LIKE '%RETIRED%';

 

Given the performance issues I have been seeing, I'm not very impressed with PI SQL and really don't understand why.  Usually when something like SQL is offered as a way to access a products data, it's to run large sets of data with better performance than say something like DataLink.  Can any of give me some use cases where you have found it effective?  Also, if you can share some of your really useful PI SQL Queries, I would be much obliged.

 

Thanks!

Steve Braich

New member of the exclusive "PI Developers Club"

Outcomes