• State Not Answered
  • Replies 10 replies
  • Subscribers 7 subscribers
  • Views 4994 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
    Removing the last row where we check Complete doesn't affect the results much if any, we get less than 10 rows back either way.

    I created an index on each of the 2 production tables this query joins. They are on the filenum_txt and a.batchnum_txt columns (the index is on the combination of the 2 columns). During normal daytime loading in production the query was running 15-30 seconds, immediately after adding the indexes to the 2 tables it dropped to <1 second. Thankfully the 2 columns in the index are the main columns used in the majority of our queries and should help out other parts of the app (though since we don't join these tables normally the speed improvements won't be nearly as big).

    I have since added a number of other indexes in development and am testing with them. As for the other apps my coworkers build/support, I am pushing them to evaluate their larger tables that are heavily queried.

    Our monthly system health check report from Appian only shows a small number of queries that run longer than a few seconds which is good and this will be a starting point for us.
Reply
  • 0
    Removing the last row where we check Complete doesn't affect the results much if any, we get less than 10 rows back either way.

    I created an index on each of the 2 production tables this query joins. They are on the filenum_txt and a.batchnum_txt columns (the index is on the combination of the 2 columns). During normal daytime loading in production the query was running 15-30 seconds, immediately after adding the indexes to the 2 tables it dropped to <1 second. Thankfully the 2 columns in the index are the main columns used in the majority of our queries and should help out other parts of the app (though since we don't join these tables normally the speed improvements won't be nearly as big).

    I have since added a number of other indexes in development and am testing with them. As for the other apps my coworkers build/support, I am pushing them to evaluate their larger tables that are heavily queried.

    Our monthly system health check report from Appian only shows a small number of queries that run longer than a few seconds which is good and this will be a starting point for us.
Children
No Data

© 2024 Appian. All rights reserved.