Hi All,
I am using a query entity to fetch the data from microsoft sql.
a!queryEntity( entity: cons!ABc, query: { a!query( seletion: { a!querySelection( columns: {column1,column2,column3,column4,column5,column6,column7,column8,column9,column10 } ) }, logicalExpression: a!queryLogicalExpression( operator: "AND", logicalExpressions: if( isusermemberofgroup( loggedInUser(),Group1 ), a!queryLogicalExpression( operator: "OR", logicalExpressions: { a!queryLogicalExpression( operator: "AND", logicalExpressions: a!queryLogicalExpression( operator: "OR", filters: { a!queryFilter( field: "productType", operator: "=", value: Value1 ), a!queryFilter( field: "productType", operator: "=", value: value2 ), a!queryFilter( field: "productType", operator: "=", value:value3 ) } ), filters: a!queryFilter( field: "transferType", operator: "=", value: value4 ) ), a!queryLogicalExpression( operator: "AND", logicalExpressions: a!queryLogicalExpression( operator: "OR", filters: { a!queryFilter( field: "productType", operator: "=", value: value5 ), a!queryFilter( field: "productType", operator: "=", value: value6 ) } ), filters: a!queryFilter( field: "transferType", operator: "=", value:value7 ) ) } ), if( isusermemberofgroup( loggedInUser(),value9 ), a!queryLogicalExpression( operator: "AND", filters: { a!queryFilter( field: "productType", operator: "=", value: value10 ), a!queryFilter( field: "transferType", operator: "=", value: value11 )/*, a!queryFilter( field: "status", operator: "not in", value: {"value14","value13"} )*/ } ), {} ) ), filters: { a!queryFilter( field: "assignee", operator: "is null" ) } ), pagingInfo: a!pagingInfo( startIndex: 1, batchSize: -1, sort: { a!sortInfo( field: "Field", ascending: true ), a!sortInfo( field: Date", ascending: true ) } ) ) }).data
It is taking very high time in the Prod. Can anyone suggest me how can i optimize it so that it can take less time.
Thanks in Advance
Discussion posts and replies are publicly visible
This is fairly complex logic to handle on the Appian-side. Could you try offboarding some of it to the database in the form of a view that you could query with less complex inputs?
If it's not possible to decrease complexity, it may be worthwhile to break this functionality into separate queries. This would not decrease the overall time, but would be less likely to result in a query timeout.
Also, you're querying with a -1 batch size, meaning this query will return every row that fits the filter criteria; if there are many rows, I suggest adding more optimal paging logic.