Query Rule times out due to slow db view

Query Rule times out due to slow db view (with minimal returned data):
We have a process model that uses a script node to call a query rule. The query rule looks at an MS SQL view and returns a small set of data, usually less than 10 rows with 16 columns of data. We then email this relatively small CDT of data to users. This happens in the background hourly and the UI is invisible to the users, so the length of time the process takes is somewhat irrelevant to us (it runs async and could take 1-2 minutes without anyone caring).

The view's business logic performs a left join between 2 tables and includes about 6 items in the where clause. We currently do not have any indexes on our base tables and in development this wasn't a problem but after deploying this particular view to production we are seeing the script node time out most of the time. The prod tables are joining 2 tables with 100 thousand & 1.6 million rows (hinting at tabl...

OriginalPostID-221207

OriginalPostID-221207

  Discussion posts and replies are publicly visible

Parents
  • 0
    Certified Lead Developer
    @ryanh Per my understanding, a!queryEntity does not bypass the time limit. If you're looking for a way to fix the issue without optimizing the view, the documentation page on Query Rule Limits (https://forum.appian.com/suite/help/16.2/Post-Install_Configurations.html#Query_Rule_Limits) has a good suggestion: "consider using the query rules paging parameter to return less data (or return data in batches) and therefore avoid the limit"
Reply
  • 0
    Certified Lead Developer
    @ryanh Per my understanding, a!queryEntity does not bypass the time limit. If you're looking for a way to fix the issue without optimizing the view, the documentation page on Query Rule Limits (https://forum.appian.com/suite/help/16.2/Post-Install_Configurations.html#Query_Rule_Limits) has a good suggestion: "consider using the query rules paging parameter to return less data (or return data in batches) and therefore avoid the limit"
Children
No Data