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)
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