4 Replies Latest reply on Jun 10, 2018 2:15 PM by vkaufmann

    PI SQL Commander - Event frame query

    JAVITHBASHA
    JAVITHBASHA May 17, 2018 7:28 PM

      I wanted to create reports in MS reports and I am trying to build event frame PI SQL query (Mentioned below). But it take more than 4 to 5 mins to execute and gets timeout.

      System explorer and PI datalink pulls out the same result in few seconds. I am trying to get the same content (Event start time , Primary Element Name , event frame attributes)

       

      My query from transpose function:

       

      Create VIEW [RN_AF].[DataT].[vAuto]

      (

      [EventFrame],

      [ANumber],

      [CrustBr],

      [M1L],

      [M1R],

      [M2L],

      [M2R],

      [M3L],

      [M3R],

      [M4L],

      [M4R],

      [M5L],

      [M5R],

       

      [StartTime]

      )

      AS

      SELECT ef.Name EventFrame, ts.*

      FROM

      (

       

      SELECT  *

      FROM [RN_AF].[EventFrame].[EventFrame]

      WHERE EventFrameTemplateID IN

      (

      SELECT ID

      FROM [RN_AF].[EventFrame].[EventFrameTemplate]

      WHERE Name = N'Gauging'

      )order by StartTime Desc 

      ) ef

      INNER JOIN [RN_AF].[EventFrame].[EventFrameHierarchy] efh

      ON efh.ID = ef.ID

      CROSS APPLY [RN_AF].[DataT].[FTGauging]

      (

      ef.ID

      ) ts

      OPTION (FORCE ORDER, IGNORE ERRORS, EMBED ERRORS)

        • Re: PI SQL Commander - Event frame query
          rschmitz
          rschmitz Employee May 21, 2018 11:47 AM (in response to JAVITHBASHA)

          Hi Javithbasha,

           

          One thing to remember with System Explorer when querying event frames is that it doesn't need to resolve the attribute values until you drill down into the event frame. It just needs to pull back event frames that match the search criteria. So I'm glad you also did this test in DataLink as well, as this would need to resolve the attribute values (assuming you selected them in the query). That being said, Datalink by default queries for only the past day of event frames unless otherwise specified. I notice in this query, you aren't restricting this to any time range, which means if you have years worth of thousands of event frames based on this template, the query will be understandably slow.

           

          The other thing that comes to mind with performance considerations is architecture we're looking at. Are you making the queries from the same machine you're testing the DataLink queries from? Also Are you making these queries in SQL commander directly or is there another layer or two of abstraction through which the queries are being made? I.e. you said this is for Microsoft reporting, so I imagine it's going through a Linked server, correct? Is it possible that SQL Server is being maxed out on it's memory/CPU usage already?

           

          Cheers,

          Rob

          • Re: PI SQL Commander - Event frame query
            vkaufmann
            vkaufmann Employee May 21, 2018 1:53 PM (in response to JAVITHBASHA)

            How many event frames is this query meant to return? How long does the query take if you leave off the CROSS APPLY statement?

             

            --Vince