Hi, I’m currently running into performance problems caused by a scheduled system flow with a very rapid schedule (every second). This seems logical, we are running this code a lot so it stands to reason that it uses a lot of resources, except that the queries in our model executed by this flow, are extremely optimized. Our monitoring indicates that the query used by IAM to fetch the schedule for this flow in the stored procedure process_flow_schedule_start currently uses a ton of CPU time. So much so, that even with a slowed down schedule, it makes the top 3 most resource intensive queries run on the database server.
User complained about a slow working application and we’ve had to slow down this flow which is a shame because this flow is really important for managing all our asynchronous API-calls and slowing down the schedule slows down a lot of processes for the users.
We would like to restore the rapid schedule. Using this rapid schedule is something that was recommended to us several times on this forum and I've seen posts of other users about fast schedules as well. It’s seems weird to me that nobody else has run into this problem before.
Is there something we can change to avoid this problem? I’ve considered making the flow loop instead of a rapidly restarting it but this doesn’t allow for multiple API calls to be executed simultaneously and some of the integrations are quite slow to respond.
The current fix I found was to change the stored procedure on the IAM database by making the process_flow_schedule_start query tables on the IAM databse instead of a view:
The original querying the view gui_appl_system_flow:
--control_proc_id: proc_process_flow_schedule_start
--template_id: proc_process_flow_schedule_start
--prog_object_item_id: proc_process_flow_schedule_start
--template_description:
declare
@model_id model_id,
@branch_id branch_id,
@schedule_id schedule_id,
@multiple_running_instances_allowed no_yes
-- Find the currently active schedule for the pending flow
select
@model_id = f.model_id,
@branch_id = f.branch_id,
@schedule_id = f.schedule_id,
@multiple_running_instances_allowed = f.multiple_running_instances_allowed
from gui_appl u
join gui_appl_system_flow f
on f.model_id = u.model_id
and f.branch_id = u.branch_id
and f.gui_appl_id = u.gui_appl_id
where u.gui_appl_id = @gui_appl_id
and u.active = 1
and f.process_flow_id = @process_flow_idChanged to:
--control_proc_id: proc_process_flow_schedule_start
--template_id: proc_process_flow_schedule_start
--prog_object_item_id: proc_process_flow_schedule_start
--template_description:
declare
@model_id model_id,
@branch_id branch_id,
@schedule_id schedule_id,
@multiple_running_instances_allowed no_yes
-- Find the currently active schedule for the pending flow
select
@model_id = s.model_id,
@branch_id = s.branch_id,
@schedule_id = iif(i.model_id is not null, i.schedule_id, s.default_schedule_id),
@multiple_running_instances_allowed = s.multiple_running_instances_allowed
from gui_appl g
join sf_process_flow s
on s.model_id = g.model_id
and s.branch_id = g.branch_id
left join iam_process_flow i
on i.model_id = g.model_id
and i.branch_id = g.branch_id
and i.gui_appl_id = g.gui_appl_id
and i.process_flow_id = s.process_flow_id
where g.gui_appl_id = @gui_appl_id
and g.active = 1
and s.process_flow_id = @process_flow_id
and s.is_system_flow = 1
and (
s.iam_custom_schedule_allowed = 1 -- The user can configure custom schedules
or exists( -- There are schedules
select 1
from sf_process_flow_schedule o
where o.model_id = g.model_id
and o.branch_id = g.branch_id
and o.process_flow_id = s.process_flow_id
)
)
This hugely improves the performance of this query and fixes our problem but it’s far from ideal since we wouldn’t like to maintain code changes on procedures provided by Thinkwise and we may unknowingly break things. It may be profitable for Thinkwise to change this, if so what is the best way to request this? Via an idea on this forum, TCP or some other way?
