cw.ng

Performance Issue on AF and SQL Server

Discussion created by cw.ng on Nov 7, 2011
Latest reply on Nov 7, 2011 by cmanhard

Hi,

 

We have created an element templated called "Phase Template". Within the template, we create the following attribute, formula and Pi point

 

Pi Point:

  • CurrentValue -   TimeMethod=AtOrBefore;RelativeTime=*;UOM=A
  • PreviousValue - TimeMethod=AtOrBefore;RelativeTime=*-1h;UOM=A

Formula:

  • Overloaded -  A=CurrentValue;B=PreviousValue;C=Threshold;[if A>C and B>C then 1 else 0]

Attribute:

  • Substation - [An Integer Number]
  • Threshold -  [A Double Number]
  • Timestamp - [A DateTime]

We have created a .NET program to retrieve list of AF Elements which fulfills some criteria. The query is as follows:

Our .NET programs run every 30 minutes(and eventually run every 15 minutes), but we found that the SQL sever uses up 100% CPU. It is suspected that when AF issue query to SQL and the query might be expensive to run in SQL server.

 

Since we have 40000 elements in AF(we intend to add up to 120,000 elements), is there any method to improve the performance of SQL query issued by AF?

 

Thanks in advance. 

 
Dim myAFTemplate As AFElementTemplate = myAFDatabase.ElementTemplates(sPhaseTemplateName)
Dim myValQueries(2) As AFAttributeValueQuery
Dim myValQuery As New AFAttributeValueQuery()

myValQuery.AttributeTemplate = myAFTemplate.AttributeTemplates("Overloaded")
myValQuery.Operator = Search.AFSearchOperator.Equal
myValQuery.AttributeValue = 1
myValQueries(0) = myValQuery
myValQuery = New AFAttributeValueQuery()
myValQuery.AttributeTemplate = myAFTemplate.AttributeTemplates("SubStation")
myValQuery.Operator = Search.AFSearchOperator.GreaterThanOrEqual
myValQuery.AttributeValue = CInt(sLower)
myValQueries(1) = myValQuery

myValQuery = New AFAttributeValueQuery()
myValQuery.AttributeTemplate = myAFTemplate.AttributeTemplates("SubStation")
myValQuery.Operator = Search.AFSearchOperator.LessThanOrEqual
myValQuery.AttributeValue = CInt(sUpper)
myValQueries(2) = myValQuery

Dim phases As AFNamedCollection(Of AFElement) = AFElement.FindElementsByAttribute(myRootElement, Nothing, myValQueries, True, AFSortField.Name, AFSortOrder.Ascending, Integer.MaxValue) 

Outcomes