• State Not Answered
  • Replies 10 replies
  • Subscribers 7 subscribers
  • Views 5009 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
    A Score Level 2
    @ryan
    Looks like you are there could be a problem in your view statement and this might bring you performance issues going forward. When using a VIEW script we need to ensure that there should not be any direct select statements. If you are joining let's say more than 5 tables other than joins if possible try creating a resultset and make alias inside your view statement. Make sure all the tables involved in view have proper indexing.

    Once this is properly aligned make use of queryEntity only if all the 14 or 20 columns of your view is not required , else if you can use queryrule.

    1)Afaik., that configuration 10 sec is for queryrule but query entity always has upper hands.
    2)I have seen QE in reports which ideally took more than 20 sec
    3)One more options could be try getting data in batches (use paging parameter like startindex 1 to 5 , next 6 to 10 and so on ) inside your process model and append the data to result set and try using that.

    Hope either anything above could help !!!
Reply
  • 0
    A Score Level 2
    @ryan
    Looks like you are there could be a problem in your view statement and this might bring you performance issues going forward. When using a VIEW script we need to ensure that there should not be any direct select statements. If you are joining let's say more than 5 tables other than joins if possible try creating a resultset and make alias inside your view statement. Make sure all the tables involved in view have proper indexing.

    Once this is properly aligned make use of queryEntity only if all the 14 or 20 columns of your view is not required , else if you can use queryrule.

    1)Afaik., that configuration 10 sec is for queryrule but query entity always has upper hands.
    2)I have seen QE in reports which ideally took more than 20 sec
    3)One more options could be try getting data in batches (use paging parameter like startindex 1 to 5 , next 6 to 10 and so on ) inside your process model and append the data to result set and try using that.

    Hope either anything above could help !!!
Children
No Data
© 2024 Appian. All rights reserved.
We want to hear from you! Submit a review on Gartner or G2.