1 Reply Latest reply on May 18, 2018 11:07 PM by vkaufmann

    Scaling Event Frame Queries in PI SQL Commander

    Steve Boyko

      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?

        • Re: Scaling Event Frame Queries in PI SQL Commander
          vkaufmann

          Hey Steve,

           

          This is kind of the plight of EF scalability issues plus some performance issues in OLEDB Enterprise. My first recommendation for you would be to check out the new RTQP engine which is releasing in the upcoming release of the PI Server but is available now as a CTP feature. There was a lot of effort put into query scalability and overall performance. However, if you want to dig deeper into your current query, enabling level 3 logging on the provider would be the first step. That would give you insight into which join is rate limiting the query and from there we can determine if there are steps that can be taken to improve the query.

           

          --Vince

          1 of 1 people found this helpful