-
Re: PI SQL Commander - Event frame query
rschmitzMay 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
vkaufmannMay 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
-
Re: PI SQL Commander - Event frame query
JAVITHBASHA May 25, 2018 10:27 AM (in response to vkaufmann)Hardly 500 records. All i want to see in SQL commander is Event frame start and end time , Associated Element Name , its attribute Values. Just as PI Datalink results
-
Re: PI SQL Commander - Event frame query
vkaufmannJun 10, 2018 2:15 PM (in response to JAVITHBASHA)
Sounds like there is something not being optimized correctly in this query. Is this a query you have written or is it one of the defaults? Something helpful to determine the bottle-neck is to enable the provider logs using the steps outlined in the documentation. Typically its best to set the debug level to 3. If you can enable this and post the corresponding file that can be very helpful.
--Vince
-
-