Query Rule times out due to slow db view

ryanh over 7 years ago

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...

Discussion posts and replies are publicly visible

Parents

0 ryanh over 7 years ago

I am going to try and get with our DBAs today to see if we can get some indexes built, up until now it hasn't been an issue, but when this code moved to production a day ago we started seeing it. It ran fine in under 4 seconds early morning and late night when others weren't hitting the db, but during the day it dragged on and the node timed out and hence the process.

In terms of using paging on the data, the problem is, we are only attempting to return 0-10 rows of data, so it isn't the quantity being returned, but the length of time the query takes. It appears as if most of the performance problems are caused by the very last and line where we check the values is not one of 3 unique values. The date column doesn't seem to cause any appreciable slow down issues. Here is a simplified version:

select 16 columns from table A left join table B
on (a.filenum_txt = b.filenum_txt and a.batchnum_txt = b.batchnum_txt)
Where b.riskcallingid_int is null
and a.riskapprovaldecision_txt = 'Pending'
and (a.suspendedcategory_txt = 'Risk' or a.suspendedcategory_txt = 'Multiple')
and a.fromdate_dte >= '2016-03-30'
and a.previousstatus_txt = 'Suspended'
and not a.processstatus_txt = 'Complete'
Cancel
Vote Up 0 Vote Down

Sign in to reply

Verify Answer

Cancel

Reply

0 ryanh over 7 years ago

I am going to try and get with our DBAs today to see if we can get some indexes built, up until now it hasn't been an issue, but when this code moved to production a day ago we started seeing it. It ran fine in under 4 seconds early morning and late night when others weren't hitting the db, but during the day it dragged on and the node timed out and hence the process.

In terms of using paging on the data, the problem is, we are only attempting to return 0-10 rows of data, so it isn't the quantity being returned, but the length of time the query takes. It appears as if most of the performance problems are caused by the very last and line where we check the values is not one of 3 unique values. The date column doesn't seem to cause any appreciable slow down issues. Here is a simplified version:

select 16 columns from table A left join table B
on (a.filenum_txt = b.filenum_txt and a.batchnum_txt = b.batchnum_txt)
Where b.riskcallingid_int is null
and a.riskapprovaldecision_txt = 'Pending'
and (a.suspendedcategory_txt = 'Risk' or a.suspendedcategory_txt = 'Multiple')
and a.fromdate_dte >= '2016-03-30'
and a.previousstatus_txt = 'Suspended'
and not a.processstatus_txt = 'Complete'
Cancel
Vote Up 0 Vote Down

Sign in to reply

Verify Answer

Cancel

Children

No Data