Job.xml : selectNextJobsToExecute taking 10-15 Seconds to complete based on Number of records available to process.
On Higher Level it seems mostly caused by Inner Query we to fetch all records and then only we selecting based on number MAX records.
If you see actual SELECT DISTINCT… query give result RES is fetching all recods from ACT_RU_JOB then only filter for top 3 records applied which seems major issue.
SELECT SUB.*
FROM (SELECT RES.id_,
RES.rev_,
RES.duedate_,
RES.process_instance_id_,
RES.exclusive_
Row_number()
OVER (
ORDER BY RES.duedate_ ASC) rnk
FROM (SELECT DISTINCT RES.id_,
RES.rev_,
RES.type_,
RES.lock_exp_time_,
RES.lock_owner_,
RES.exclusive_,
RES.process_instance_id_,
RES.duedate_,
RES.priority_
FROM act_ru_job RES
WHERE ( RES.retries_ > 0 )
AND ( RES.duedate_ IS NULL
OR RES.duedate_ <= '2022-08-22 11:17:00.155' )
AND ( RES.lock_owner_ IS NULL
OR RES.lock_exp_time_ < '2022-08-22 11:17:00.155'
)
AND RES.suspension_state_ = 1
AND ( ( RES.exclusive_ = 1
AND NOT EXISTS(SELECT J2.id_
FROM act_ru_job J2
WHERE
J2.process_instance_id_ =
RES.process_instance_id_
AND ( J2.exclusive_ = 1 )
AND ( J2.lock_owner_ IS NOT NULL
AND J2.lock_exp_time_ >=
'2022-08-22 11:17:00.155'
)
) )
OR RES.exclusive_ = 0 )) RES) SUB
WHERE SUB.rnk >= 1
AND SUB.rnk < 4
ORDER BY SUB.rnk