• State Not Answered
  • Replies 10 replies
  • Subscribers 7 subscribers
  • Views 5012 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
    Certified Lead Developer
    @ryanh It might be worth considering in the long term using lookup tables for the different statuses and categories, and having table A use foreign keys instead of storing the text names. Comparing integers (especially once you add indexes) will perform significantly better than comparing text strings on such a large table.

    In the mean time though, how many rows are returned if you remove the last line that seems to be causing the problems? It might be an option to return all results regardless of processstatus_txt (and maybe combine this with the batching approach if there are a lot of rows), and finish the filtering in Appian. It will probably be slower overall, but it might at least reduce the frequency of your script task erroring.
Reply
  • 0
    Certified Lead Developer
    @ryanh It might be worth considering in the long term using lookup tables for the different statuses and categories, and having table A use foreign keys instead of storing the text names. Comparing integers (especially once you add indexes) will perform significantly better than comparing text strings on such a large table.

    In the mean time though, how many rows are returned if you remove the last line that seems to be causing the problems? It might be an option to return all results regardless of processstatus_txt (and maybe combine this with the batching approach if there are a lot of rows), and finish the filtering in Appian. It will probably be slower overall, but it might at least reduce the frequency of your script task erroring.
Children
No Data
© 2024 Appian. All rights reserved.
We want to hear from you! Submit a review on Gartner or G2.