We linked the Thinkwise Guidelines Validations. Validations for Thinkwise Guidelines now available in the Thinkstore! | Thinkwise Community (thinkwisesoftware.com)
Branch/Version 2023.1
It sometimes get stuck:
After some research the validations that uses a cursor to search through template code are the one that get stuck. For instance “tsf_guideline_subroutine_does_not_have_a_coded_transaction”
Using a user local temporary table AND Introducing an index (after the data is added, for additional performance) seems to solves the problem, but since it only gets randomly stuck it's hard to proof. But at worst, the performance is significantly increased. For instance “tsf_guideline_subroutine_does_not_have_a_coded_transaction” on our model goes down from 4 seconds to 0 (<1) seconds.
create table #control_proc_template (control_proc_id varchar(100)
,template_id varchar(100)
,template_code nvarchar(max)
,code_grp_id varchar(100)
)
insert #control_proc_template (control_proc_id
,template_id
,template_code
,code_grp_id
)
select cpt.control_proc_id
,cpt.template_id
,cpt.template_code
,cp.code_grp_id
from control_proc cp
join control_proc_template cpt
on cpt.model_id = cp.model_id
and cpt.branch_id = cp.branch_id
and cpt.control_proc_id = cp.control_proc_id
where cpt.model_id = @model_id
and cpt.branch_id = @branch_id
and cp.generated = 0
and cp.generated_by_control_proc_id is null
and cp.development_status <> 2 -- inactive
and cp.control_proc_type <> 2 -- dynamic model
/* create index on temporary table */
create nonclustered index control_proc_template_template_code
on #control_proc_template (control_proc_id,template_id)
include (template_code)Will this be picked up in a newer version of the Thinkwise Guidelines Validations and if so, when? Or is it your advice to change the scripts our self?
