AnsweredAssumed Answered

Scaling Event Frame Queries in PI SQL Commander

Question asked by Steve Boyko Champion on May 18, 2018
Latest reply on May 18, 2018 by vkaufmann

I am trying to organize some event frames to pull data into Power BI and I am finding that they don't scale well. I've created a transpose function to retrieve event frames as rows in PI SQL Commander (PI-OLEDB) and the retrieval works fine for 100 event frames (1 second), 1000 event frames (3 seconds), but 5000 event frames times out after 3 minutes.

 

What I see on the SQL Server side is that it is CPU bound, presumably doing the joins.

 

Here is the SQL I am using, which is basically the predefined query you get in SQL Commander.

SELECT ef.Name EventFrame, ts.*

FROM

(

SELECT *

FROM [New Afton Concentrator].[EventFrame].[EventFrame]

WHERE StartTime > '2018-2-1' AND EventFrameTemplateID IN

(

SELECT ID

FROM [New Afton Concentrator].[EventFrame].[EventFrameTemplate]

WHERE Name = N'Unit Down Template'

)

) ef

INNER JOIN [New Afton Concentrator].[EventFrame].[EventFrameHierarchy] efh

ON efh.ID = ef.ID

CROSS APPLY [New Afton Concentrator].[DataT].[TransposeEventFrameSnapshot_Unit Down]

(

ef.ID

) ts

OPTION (FORCE ORDER, IGNORE ERRORS, EMBED ERRORS)

This should return around 4000 event frames.

 

Do you have any suggestions on how I can make this work for more than a few event frames?

Outcomes