Process template loading improvement

This technical improvement of the beqom v10 application focuses on the process template loading performance. Before hotfix package 55, when creating process templates, the application created a temporary table. While this behavior was fitted for complex views, it would unnecessarily slow down the performance of the operation when a simple table was used as the source of the process template.

The following snippet code illustrates the query that was used for the creation of the process template:

Copy

SELECT {1}
INTO#GlobalInfoColumns
FROM ( {0} ) ic
{2} -- join section for associated columns
WHERE@endDateFilter> ic.start_date_histo
AND@startDateFilter<= ic.end_date_histo

In order to optimize this behavior and improve the performance of the process template creation operation, a new option has been added to the process template. This option, called UseCTE, replaces temporary tables by a common table expression when it is set to true.

The following table details the new configuration, with its properties:

UseCTE option configuration

Table name	Column name	Properties
k_m_type_plan	use_cte_in_process_load	boolean Default value: false

When the value of the column is set to true, the process template creation query is changed as follows:

Copy

WITH#GlobalInfoColumns ({1})
AS
(
SELECT {1}
FROM ( {0} ) ic
{2} -- join section for associated columns
WHERE@endDateFilter> ic.start_date_histo
AND@startDateFilter<= ic.end_date_histo
 )

When the value of the column is set to false, the original query is retained.