• State Not Answered
  • Replies 10 replies
  • Subscribers 7 subscribers
  • Views 5008 views
  • Users 0 members are here
Related Discussions
Home » Discussions » AI and Rules

Query Rule times out due to slow db view

ryanh

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
    ...e scan issues).

    The docs for 7.11 show the setting conf.data.query.timeout=10, which I believe is what we are using. The query running directly on the production db is taking 4-25 seconds to run (sub 1 second in dev). While we are hoping to create indexes on the tables to speed up the performance, short term we would like to get this running. In theory we could change the system timeout but this could affect other apps on the server.

    1) does the conf.data.query.timeout setting apply to ALL queries in Appian or just Query Rules?
    2) would rewriting the query rule to be a queryEntity allow us to bypass the 10 second time limit?
    3) is there another way to get our process model to not have the script node crash while waiting for the query rule to receive the results from the db?

    Yes, optimizing the db to return data in <10 seconds would be best, but if we don't care how long it runs is there a way to quickly rewrite the query rule, script node or process model to ...
Reply
  • 0
    ...e scan issues).

    The docs for 7.11 show the setting conf.data.query.timeout=10, which I believe is what we are using. The query running directly on the production db is taking 4-25 seconds to run (sub 1 second in dev). While we are hoping to create indexes on the tables to speed up the performance, short term we would like to get this running. In theory we could change the system timeout but this could affect other apps on the server.

    1) does the conf.data.query.timeout setting apply to ALL queries in Appian or just Query Rules?
    2) would rewriting the query rule to be a queryEntity allow us to bypass the 10 second time limit?
    3) is there another way to get our process model to not have the script node crash while waiting for the query rule to receive the results from the db?

    Yes, optimizing the db to return data in <10 seconds would be best, but if we don't care how long it runs is there a way to quickly rewrite the query rule, script node or process model to ...
Children
No Data
© 2024 Appian. All rights reserved.
We want to hear from you! Submit a review on Gartner or G2.