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 [New Afton Concentrator].[EventFrame].[EventFrame]
WHERE StartTime > '2018-2-1' AND EventFrameTemplateID IN
FROM [New Afton Concentrator].[EventFrame].[EventFrameTemplate]
WHERE Name = N'Unit Down Template'
INNER JOIN [New Afton Concentrator].[EventFrame].[EventFrameHierarchy] efh
ON efh.ID = ef.ID
CROSS APPLY [New Afton Concentrator].[DataT].[TransposeEventFrameSnapshot_Unit Down]
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?