Issue while executing Queryentity.

Question

Hi All, 
 We are using a queryentity to retrieve the data from DB View. While retrieving the data we are seeing below intermittent issue. Our query rule is too long, I have attached that. Any suggestions to optimize this rule. 
 
 at function a!queryenti

Fullscreen 
 Query_Code.txt 
 Download 
 
 a!queryEntity_22r2(
 entity: cons!My_VIEW,
 query: a!query(
 selection: if(
 rule!APN_isEmpty(ri!selectionColumns),
 {},
 a!querySelection(
 columns: a!forEach(
 items: ri!selectionColumns,
 expression: a!queryColumn(field: fv!item)
 )
 )
 ),
 logicalexpression: a!queryLogicalExpression(
 operator: "AND",
 ignorefilterswithemptyvalues: true,
 filters: {
 a!queryFilter(
 field: "abc",
 operator: "in",
 value: ri!abcs,
 applywhen: not(rule!APN_isEmpty(ri!abcs))
 ),
 a!queryFilter(
 field: "testName",
 operator: "in",
 value: ri!testName,
 applywhen: not(rule!APN_isEmpty(ri!testName))
 ),
 a!queryFilter(
 field: "bNumber",
 operator: "in",
 value: ri!bId,
 applywhen: not(rule!APN_isEmpty(ri!bId))
 ),
 a!queryFilter(
 field: "cluNumber",
 operator: "in",
 value: ri!cluNumber,
 applywhen: not(rule!APN_isEmpty(ri!cluNumber))
 ),
 if(
 rule!APN_isEmpty(ri!priorities),
 {},
 if(
 and(
 not(isnull(ri!flowTypes)),
 contains(
 touniformstring(ri!flowTypes),
 cons!FlowType[2]
 )
 ),
 {},
 a!queryFilter(
 field: "priority",
 operator: "in",
 value: { ri!priorities }
 )
 )
 ),
 a!queryFilter(
 field: "nfiType",
 operator: "in",
 value: ri!nfiTypes,
 applywhen: not(rule!APN_isEmpty(ri!nfiTypes))
 ),
 a!queryFilter(
 field: "emailSentBy",
 operator: "in",
 value: ri!emailSentBy,
 applywhen: not(rule!APN_isEmpty(ri!emailSentBy))
 ),
 a!queryFilter(
 field: "emailSentDate",
 operator: ">=",
 value: cast(
 typeof(datetime(2017, 10, 27, 23, 59, 00)),
 todatetime(ri!fromEmailSentDate & " 12:00 am")
 ),
 applywhen: not(rule!APN_isEmpty(ri!fromEmailSentDate))
 ),
 a!queryFilter(
 field: "emailSentDate",
 operator: "<=",
 value: cast(
 typeof(datetime(2017, 10, 27, 23, 59, 00)),
 todatetime(ri!toEmailSentDate & " 11:59 pm")
 ),
 applywhen: not(rule!APN_isEmpty(ri!toEmailSentDate))
 ),

 },
 logicalexpressions: {
 a!queryLogicalExpression(
 operator: "OR",
 filters: if(
 rule!APN_isEmpty(ri!submissionDescription),
 {},
 {a!forEach(
 items: ri!submissionDescription,
 expression: a!queryFilter(
 field: "submissionDesc",
 operator: "includes",
 value: fv!item 
 )
 ),
 a!forEach(
 items: ri!submissionDescription,
 expression: a!queryFilter(
 field: "bDescription",
 operator: "includes",
 value: fv!item 
 )
 )}

 ),
 ignorefilterswithemptyvalues: true()
 ),
 a!queryLogicalExpression(
 operator: "OR",
 filters: if(
 rule!APN_isEmpty(ri!workflowCreatedBy),
 {},
 a!forEach(
 items: ri!workflowCreatedBy,
 expression: a!queryFilter(
 field: "workflowCreatedBy",
 operator: "includes",
 value: "; " & fv!item & ";"
 )
 )
 ),
 ignorefilterswithemptyvalues: true()
 ),
 a!queryLogicalExpression(
 operator: "OR",
 ignorefilterswithemptyvalues: true(),
 filters: if(
 rule!APN_isEmpty(ri!gll),
 {},
 a!forEach(
 items: ri!gll,
 expression: a!queryFilter(
 field: "gll",
 operator: "includes",
 value: "; " & fv!item & ";"
 )
 )
 )
 ),
 a!queryLogicalExpression(
 operator: "OR",
 ignorefilterswithemptyvalues: true(),
 filters: if(
 rule!APN_isEmpty(ri!glsd),
 {},
 a!forEach(
 items: ri!glsd,
 expression: a!queryFilter(
 field: "glsd",
 operator: "includes",
 value: "; " & fv!item & ";"
 )
 )
 )
 ),
 a!queryLogicalExpression(
 operator: "OR",
 ignorefilterswithemptyvalues: true(),
 filters: if(
 rule!APN_isEmpty(ri!tradeNames),
 {},
 a!forEach(
 items: ri!tradeNames,
 expression: a!queryFilter(
 field: "tradeName",
 operator: "includes",
 value: "; " & fv!item & ";"
 )
 )
 ),

 ),
 a!queryLogicalExpression(
 operator: "OR",
 ignorefilterswithemptyvalues: true(),
 filters: if(
 rule!APN_isEmpty(ri!documentCodes),
 {},
 a!forEach(
 items: ri!documentCodes,
 expression: a!queryFilter(
 field: "documentCode",
 operator: "includes",
 value: "; " & fv!item & ";"
 )
 )
 )
 ),
 a!queryLogicalExpression(
 operator: "OR",
 ignorefilterswithemptyvalues: true(),
 filters: if(
 rule!APN_isEmpty(local!subtype),
 {},
 if(
 and(
 contains(
 ri!flowTypes,
 cons!flowType[1]
 ),
 contains(
 ri!submissionTypes,
 cons!TYPES[2]
 )
 ),
 a!forEach(
 items: ri!subTypes,
 expression: a!queryFilter(
 field: "subType",
 operator: "includes",
 value: "; " & fv!item & ";"
 )
 ),
 {}
 )
 )
 ),
 a!queryLogicalExpression(
 operator: "OR",
 ignorefilterswithemptyvalues: true(),
 filters: if(
 rule!APN_isEmpty(ri!rcns),
 {},
 a!forEach(
 items: ri!rcns,
 expression: a!queryFilter(
 field: "rcn",
 operator: "includes",
 value: "; " & fv!item & ";"
 )
 )
 )
 ),
 a!queryLogicalExpression(
 operator: "OR",
 ignorefilterswithemptyvalues: true(),
 filters: if(
 rule!APN_isEmpty(ri!flowTypes),
 {},
 a!forEach(
 items: ri!flowTypes,
 expression: a!queryFilter(
 field: "workflowType",
 operator: "includes",
 value: "; " & fv!item & ";"
 )
 )
 )
 ),
 a!queryLogicalExpression(
 operator: "OR",
 ignorefilterswithemptyvalues: true(),
 filters: if(
 rule!APN_isEmpty(ri!submissionTypes),
 {},
 a!forEach(
 items: ri!submissionTypes,
 expression: a!queryFilter(
 field: "submissionType",
 operator: "includes",
 value: "; " & fv!item & ";"
 )
 )
 ),

 ),
 a!queryLogicalExpression(
 operator: "OR",
 ignorefilterswithemptyvalues: true(),
 filters: if(
 rule!APN_isEmpty(ri!workflowStatuses),
 {},
 a!forEach(
 items: ri!workflowStatuses,
 expression: a!queryFilter(
 field: "workflowStatus",
 operator: "includes",
 value: "; " & fv!item & ";"
 )
 )
 )
 )
 }
 ),
 paginginfo: if(
 rule!APN_isEmpty(ri!pagingInfo),
 a!pagingInfo(
 startIndex: 1,
 batchSize: 50,
 sort: a!sortInfo(field: "id", ascending: false)
 ),
 ri!pagingInfo
 )
 ),
 fetchtotalcount: if(
 or(
 rule!APN_isEmpty(ri!fetchTotalCount),
 ri!isData = true
 ),
 false,
 ri!fetchTotalCount
 )
 )

ty an error occured while retreiving the data. Details: unexpected error executing query 
 Thanks

Mathieu Drouin · Answer

You could try reducing the number of columns you are returning. 
 It seems like you are querying a view. I would suggest that you look at the underlying query and see if you can't optimize that. You could also add some indexes on some of the underlying tables. Finally you could build materialized views (physical tables that you refresh from time to time).