'Function' takes forever

We experience this as well (though since well before the last version) 

Hey Blommetje,

Do you also experience the same duration when generating the code group Functions? Any indication on the database which query might be the issue?

The only thing I think that could take long is the magic-replacement of parameters used in templates. Do you have any function that either has many parameters of has many replacement for one single parameter? 

No, it appears the code group under Functionalities seems to work ok. It only takes seconds. I do not have any replacements in play here. 

Blommetje

When you encounter the same trouble, could you inspect the Activity monitor of SQL Server using SSMS? The Active Expensive Queries should then show the exact query that is taking quite long. You can then right click the running query and inspect the code. This is important to us in determining the possible cause. You can also try a exec tsf_optimize query to ensure it is not an index or statistics issue.

Another recommendation I can give, if available, is to activate the Query store for the Software Factory database. This will collect several queries and used plan that can help when encountering problems.
(Right click the database in SSMS > Properties > Query store > Set "Operation Mode (Requested)” to Read / Write)

Hi Mark, 

Tried it. The Functions took about 11 minutes. The query that was shown this time (and that I timed) is;

insert into #lines
                select iif(replace(ltrim(rtrim(replace(t.value, CHAR(13), ''))), char(9), '') = '', '', replace(t.value, CHAR(13), '')), t.ordinal, p.order_no
                from dbo.string_split_ordinal(@template_code, char(10)) t
                left join #template_parmtrs p
                  on t.value like '%\[' + p.parmtr_id + '\]%' escape '\'
                where not exists (select 1
                                    from #template_parmtrs p2
                                   where t.value like '%\[' + p2.parmtr_id + '\]%' escape '\'
                                     and (p2.occurrence_count > p.occurrence_count or (p2.occurrence_count = p.occurrence_count and p.parmtr_id > p2.parmtr_id)))

However, in a split second I thought I also saw; Not entirely sure.

select @code_file_generated_code = string_agg(po.prog_object_generated_code, '') within group (order by po.order_no, po.prog_object_id) 
    from prog_object po
    outer apply (select ex.prog_object_id
                 from get_excluded_prog_objects(po.model_id, po.branch_id) ex
                 where ex.prog_object_id = po.prog_object_id) excl
    where po.model_id       = @model_id
      and po.branch_id = @branch_id
      and po.code_file_id   = @code_file_id
      and po.prog_object_generated_code is not null
      and po.generated_code_stale = 0 -- Must be not stale
      and (@only_selected   = 0 
        or po.selected      = 1)
      and excl.prog_object_id is null

Does this make any sense? 

Thanks, 

Blommetje

Hey,

To me it does, but understandly it's quite cryptic to others 😋 The first code is responsible for replacing program object item parameters occurrences with the parameter value. The second piece of code combines all program objects’ code to become a code file.

To really debug this well we most likely will need a SF database backup and reproduce the issue. Could your create a TCP ticket for this? Our support team can then look into this.

Hi,

Opened a ticket in tcp for follow up. 

To be continued! 

Blommetje