Alena Peterová wrote:
This would be useful. We found some difficulties in our project:
- HrEnableContractProcess - the queue has 3233 items, the task runs 6 minutes
- HrEndContractProcess - the queue has 7154 items, processed 216 items, the task runs 27 minutes. The same time when run again - 0 processed items.
Processing of the items is quick, but "computing" them takes a lot of time. We want to run synchronization from HR every 15 minutes, but due to these performance issues, we can't start HrEndContractProcess as often.
The project runs on 10.4.1 + SQL Server, 6 CPU cores, 4 GB RAM for Tomcat. Maybe the SQL Server makes it worse, because there are projects on PostgreSQL with much more contracts and compute them faster.
Very interesting query statistics from this project - the 3 queries below take up 60 % of total CPU time.
I tried Microsoft SQL Server Management Studio - Activity Monitor and found a missing index in the first two queries.
After I added this:
CREATE INDEX idm_processed_t_testindexreferencedentity ON bcv_idm_storage.idm_processed_task_item (referenced_entity_id);
The duration of the following tasks was much improved:
HrEnableContractProcess from 3:37 minutes to 0:32
HrEndContractPRocess from 33:03 minutes to 1:18.
-----------------
select count(idmprocess0_.id) as col_0_0_ from idm_processed_task_item idmprocess0_ where idmprocess0_.scheduled_task_queue_owner=@P0 and idmprocess0_.referenced_entity_id=@P1
select TOP(@P0) idmprocess0_.id as id1_109_, idmprocess0_.created as created2_109_, idmprocess0_.creator as creator3_109_, idmprocess0_.creator_id as creator_4_109_, idmprocess0_.modified as modified5_109_, idmprocess0_.modifier as modifier6_109_, idmprocess0_.modifier_id as modifier7_109_, idmprocess0_.original_creator as original8_109_, idmprocess0_.original_creator_id as original9_109_, idmprocess0_.original_modifier as origina10_109_, idmprocess0_.original_modifier_id as origina11_109_, idmprocess0_.realm_id as realm_i12_109_, idmprocess0_.transaction_id as transac13_109_, idmprocess0_.long_running_task as long_ru20_109_, idmprocess0_.result_cause as result_14_109_, idmprocess0_.result_code as result_15_109_, idmprocess0_.result_model as result_16_109_, idmprocess0_.result_state as result_17_109_, idmprocess0_.referenced_dto_type as referen18_109_, idmprocess0_.referenced_entity_id as referen19_109_, idmprocess0_.scheduled_task_queue_owner as schedul21_109_ from idm_processed_task_item idmprocess0_ where idmprocess0_.scheduled_task_queue_owner=@P1 and idmprocess0_.referenced_entity_id=@P2
select idmlongrun0_.id as id1_92_, idmlongrun0_.created as created2_92_, idmlongrun0_.creator as creator3_92_, idmlongrun0_.creator_id as creator_4_92_, idmlongrun0_.modified as modified5_92_, idmlongrun0_.modifier as modifier6_92_, idmlongrun0_.modifier_id as modifier7_92_, idmlongrun0_.original_creator as original8_92_, idmlongrun0_.original_creator_id as original9_92_, idmlongrun0_.original_modifier as origina10_92_, idmlongrun0_.original_modifier_id as origina11_92_, idmlongrun0_.realm_id as realm_i12_92_, idmlongrun0_.transaction_id as transac13_92_, idmlongrun0_.task_count as task_co14_92_, idmlongrun0_.task_counter as task_co15_92_, idmlongrun0_.dry_run as dry_run16_92_, idmlongrun0_.instance_id as instanc17_92_, idmlongrun0_.recoverable as recover18_92_, idmlongrun0_.result_cause as result_19_92_, idmlongrun0_.result_code as result_20_92_, idmlongrun0_.result_model as result_21_92_, idmlongrun0_.result_state as result_22_92_, idmlongrun0_.running as running23_92_, idmlongrun0_.scheduled_task_id as schedul31_92_, idmlongrun0_.stateful as statefu24_92_, idmlongrun0_.task_description as task_de25_92_, idmlongrun0_.task_properties as task_pr26_92_, idmlongrun0_.task_started as task_st27_92_, idmlongrun0_.task_type as task_ty28_92_, idmlongrun0_.thread_id as thread_29_92_, idmlongrun0_.thread_name as thread_30_92_ from idm_long_running_task idmlongrun0_ where idmlongrun0_.instance_id=@P0 and idmlongrun0_.result_state=@P1 order by idmlongrun0_.created asc
