Skip to main content

Celonis Product Documentation

Basic rule set for performant scripting

When optimizing your scripts, follow these rules:

Category

Rule

Comment

CREATE

Use views if they are not/seldom accessed in the transformations (e.g. views solely for the datamodel)

Further information: Table vs. View

Use tables if many transformations access it (e.g. tmp table for activities)

fFurther information: Table vs. View

Combine tables if possible

Noo extra table with the same information, e.g. case table and tmp table

Order a table that is often joined by the columns used in this join

OPERATORS

BETWEEN is better than AND

DATE≥1970 AND DATE≤1980 is slower than DATE BETWEEN 1970 AND 1980

Avoid using NOT clauses, reformulate the condition to be positive

NOT<1970 is slower than ≥1970

SELECT

Avoid DISTINCT

further information: Usage of DISTINCT

Reduce SELECT * to needed columns

Avoid clean up by clean code

e.g., write else statements for activities or limit data in the joins sufficiently

JOINS

Avoid (nearly) unused joins

e.g., if only used for one column that 'might' be used in an analysis

Tables should be inner joined in the order of ascending size

Don’t modify columns in the join

e.g., don’t use right() or cast() in the join itself

Join columns should be grouped to their respective sides of an equation based on their source table

T.a+T.b+X.b= X.a is slower thanT.a+T.b = X.a-X.b

UNION

Use UNION ALL if possible

Using UNION ALL instead of UNION avoids DISTINCT operations in the background (further information about the usage of DISTINCT here)

CONDITIONS

Move conditions to extraction if possible

e.g., if a table is only used once and filtered in that case

Move WHERE-conditions directly into inner joins

(further information)