Skip to main content

Celonis Product Documentation

Creating Temporary Join Tables [Performance]

If you use similar joins multiple times, create temporary tables that cover (sub-) joins and use them instead of executing the same join statements repeatedly.

Important

A common problem with long running transformations and slow data model loads is due to missing statistics in Vertica. This issue can be resolved by adding the Vertica ANALYZE_STATISTICS statement directly in the SQL. For more information, refer to Vertica Transformations Optimization.

Example - Step 1: Creating Temporary Join Table

DROP TABLE IF EXISTS temp2;
CREATE TABLE temp2 AS
SELECT
   ...
FROM
   ANY_TABLE
   JOIN ANOTHER_TABLE ON 1=1
       AND ANY_TABLE.ID = ANOTHER_TABLE.ID
;

Example - Step 2: Replacing Repeated Join Statements With Temporary Join Table

SELECT
    ...
FROM temp2
WHERE 1=1
AND any_column IN ('example')
AND any_column <> 0
;
 
SELECT
    ...
FROM temp2
JOIN SECOND_TABLE ON 1=1
   AND temp2.ID = SECOND_TABLE.ID
   AND SECOND_TABLE.any_column IN ('example1','example2')
   AND temp2.any_column <> 0
;