hanyong

AFElement.FindElementsByAttribute

Blog Post created by hanyong Employee on Oct 30, 2012

 

 

One of the use case that we get from customers for PI Asset Framework (AF) is the ability to search for elements based on attribute values. One example: Customers may want to collect information of equipment from a specific vendor, then they would probably want to post the query like "Get all turbine that is manufactured by 'Sidewinder' <manufacturer name>". On the other hand, user may also want to identify asset based on time-series information like status. For example "Find all reactors which the status is 'On'".

 

If you look into the AF SDK Programming Reference, you will find a specific method implemented to fulfill this requirement "AFElement.FindElementsByAttribute.

 

For example, I have a test environment i defined a capacitor template with the following attribute templates

 

3731.Snap5.jpg

 

A sample implementation is:

 
AFElementTemplate template = afdb.ElementTemplates["Capacitor"];
if (template != null)
{
    // 1st value condition: Manufacturer = Borne Engineering
    AFAttributeTemplate attrtemplate2 = template.AttributeTemplates["Manufacturer"];
    AFAttributeValueQuery query1 = new AFAttributeValueQuery(attrtemplate2,
    OSIsoft.AF.Search.AFSearchOperator.Equal, "Sidewinder");

    // 2nd value condition: Differential Energy > 100
    AFAttributeTemplate attrtemplate1 = template.AttributeTemplates["Differential Energy"];
    AFAttributeValueQuery query2 = new AFAttributeValueQuery(attrtemplate1,
    OSIsoft.AF.Search.AFSearchOperator.GreaterThan, 100);

    // AFAttributeValueQuery array allow us to pass 1 or more value query in the search
    AFAttributeValueQuery[] queries = new AFAttributeValueQuery[] { query1, query2 };

    DateTime start1 = DateTime.Now;
    elementList = AFElement.FindElementsByAttribute(null, "*", queries, true, AFSortField.Name,
    AFSortOrder.Ascending, 100000);
    DateTime end1 = DateTime.Now;
    Console.WriteLine("Number of Elements Found = {0}", elementList.Count);
    Console.WriteLine("Time Taken for search to complete = {0} ms", (end1 - start1).TotalMilliseconds);
}

This implementation works great if you use templates when building up you AF Tree. However if you scale this up and try to search among 10s or 100s of elements, you will start to see that this does causes a significant load on the server. For example if I try to run the code with more than 50,000 capacitors stored in my AF server, and we look at the time to complete the search and load on AF Server and SQL Server services

 

3617.Snap1.jpg

 

5460.Snap4.jpg

 

It does take significant amount of time to complete and we can see that the server services are constantly working throughout the period that the search is taking place. Knowing that getting values through data reference is mostly handled at the client, it is surprising that AF Server and SQL Server is spending so much time working as the search going on.

 

After investigating into this with the help from Chris Manhard, it has become obvious that as the search is running, element and attribute information is gradually being loaded by the client. Hence we get the performance that we see above. 

 

What if we add 2 additional steps before calling the FindElementsByAttribute method? 

 
AFElementTemplate template = afdb.ElementTemplates["Capacitor"];
if (template != null)
{
    // Additional code: FindElementByTemplate then LoadElement before searching by attribute
    AFNamedCollectionList elist = AFElement.FindElementsByTemplate(afdb, null, template, true, AFSortField.Name, AFSortOrder.Ascending, 100000);
    AFElement.LoadElements(elist);

    // 1st value condition: Manufacturer = Borne Engineering
    AFAttributeTemplate attrtemplate2 = template.AttributeTemplates["Manufacturer"];
    AFAttributeValueQuery query1 = new AFAttributeValueQuery(attrtemplate2,
    OSIsoft.AF.Search.AFSearchOperator.Equal, "Sidewinder");

    // 2nd value condition: Differential Energy > 89
    AFAttributeTemplate attrtemplate1 = template.AttributeTemplates["Differential Energy"];
    AFAttributeValueQuery query2 = new AFAttributeValueQuery(attrtemplate1,
    OSIsoft.AF.Search.AFSearchOperator.GreaterThan, 100);

    // AFAttributeValueQuery array allow us to pass 1 or more value query in the search
    AFAttributeValueQuery[] queries = new AFAttributeValueQuery[] { query1, query2 };
    DateTime start1 = DateTime.Now;
    elementList = AFElement.FindElementsByAttribute(null, "*", queries, true, AFSortField.Name,
    AFSortOrder.Ascending, 100000);
    DateTime end1 = DateTime.Now;
    Console.WriteLine("Number of Elements Found = {0}", elementList.Count);
    Console.WriteLine("Time Taken for search to complete = {0} ms", (end1 - start1).TotalMilliseconds);
}

Here's the difference in performance

 

6114.Snap3.jpg

 

3582.Snap2.jpg

 

While the peak CPU load on the AF Server and SQL Server is higher, the time AF Server and SQL Server spent working and the time taken to complete the search even with the additional steps is still much shorter than just searching by attribute directly. So if you are searching by attribute from a large number of elements, this can be a kind of workaround that you can try using to improve the search performance.

 

After communicating to Chris about this, a work item has already been created for this behaviour in AFElement.FindElementsByAttribute method, so you can look forward to better performance in the method when this work item is completed.

 

 

Outcomes