Database

The following recommendations focus primarily on MariaDB, but the principles apply broadly to other SQL-based systems — even if specific syntax may differ.

Use the Designer for SQL Queries

SELECT *
FROM PERSON
JOIN CONTACT ON (CONTACT.PERSON_ID = PERSON.PERSONID)
JOIN ORGANISATION ON (ORGANISATION.ORGANISATIONID = CONTACT.ORGANISATION_ID)
LEFT JOIN ADDRESS ON (ADDRESS.ADDRESSID = CONTACT.ADDRESS_ID)

To analyze performance:

1. Extract and test the SQL directly in ADITO Designer using the "Execute SQL" button.
2. Observe execution time, but keep in mind:
   • Times improve on repeat executions due to caching.
   • Background processes (e.g., imports) can skew results.
   • Consider:
     • "Max. rows" field setting
     • Raw execution time
     • UI rendering time
     • Total time reported

Use EXPLAIN for Query Plans

Prefix a query with EXPLAIN to see how the database will execute it. The output shows:

• Execution order of joined tables
• Which indexes are used (key) and which are possible (possible_keys)

Figure: Query plan analysis using EXPLAIN

You will notice that both the key and possible_keys columns are empty for the PERSON, CONTACT, and ORGANISATION tables — indicating that no index is being used for these joins.

---

Disable autoOrderByID

Set autoOrderByID = No unless your application explicitly requires ADITO to enforce a default result ordering.

When enabled, ADITO automatically appends ORDER BY ID to queries that do not already specify an ORDER BY clause. This ensures predictable ordering — but at the cost of additional processing that may be unnecessary if the application defines its own sorting logic.

autoOrderByID=No

-- With autoOrderByID enabled (default behavior) SELECT * FROM CUSTOMER ORDER BY ID;

-- Better: define application-specific ordering explicitly SELECT * FROM CUSTOMER ORDER BY LASTNAME, FIRSTNAME;

:::tip
Relational databases **do not guarantee the order of result rows** unless an `ORDER BY` clause is explicitly defined. Even if results appear sorted (e.g., by insertion order or ID), this behavior is not reliable and can change depending on indexes, query plans, or database updates.
:::

:::warning
Enable `autoOrderByID = Yes` only if stable row order is needed and not explicitly defined in the query — for example, in legacy systems. Relying on implicit ordering is discouraged and can lead to subtle bugs or performance issues.
:::

![autoOrderByID](./images/autoOrderById.png)
*Figure: Example configuration for `autoOrderByID`*

#### User Property `selectionIgnoreCase`

Set the user-specific property `selectionIgnoreCase = false` for all users to ensure optimal index usage during filtered queries.

This applies when creating new users or importing existing ones.

```sql
-- Inefficient: disables index usage due to UPPER function
SELECT * FROM PERSON WHERE UPPER(FIRSTNAME) = UPPER('JOHN');

-- Efficient: allows index usage
SELECT * FROM PERSON WHERE FIRSTNAME = 'JOHN';

Figure: selectionIgnoreCase setting in the user interface

tip

When selectionIgnoreCase = true, the system performs case-insensitive filtering by applying the UPPER() function to both the column and the value. This prevents the database from using indexes efficiently.

warning

Set selectionIgnoreCase = true only if your database or collation is case-sensitive and you require case-insensitive matching.

---

Enable Database Paging

Paging helps optimize performance by limiting the number of records loaded into memory during database queries. This setting is especially relevant for large result sets in on-premise installations.

To enable paging:

1. Open the ADITOserver.vmoptions file in your local ADITO installation.
2. Add the following JVM parameter:

-Dadito.database.paging=true

From ADITO version 2023.1.0 onward, paging is enabled by default.

tip

Cloud systems provisioned via the ADITO Self-Service Portal (SSP) are preconfigured for optimal paging settings and can only be adjusted by ADITO IT.

---

Loading Methods

Beyond RecordContainer-based access, the ADITO platform provides several methods for loading data. These differ significantly in performance and permission handling:

Method	Respects Permissions	Notes
db.xxx	❌	Fast, but ignores permission rules
SqlBuilder	❌	Similar to db.xxx; for custom query generation
LoadEntity	✅	Fully respects client-defined permission models
tools.getUsersByAttribute	Depends	Avoid PROFILE_FULL unless explicitly necessary

Best Practices

• Avoid permission-aware methods unless needed: Respecting permissions introduces overhead. Use only when data security is essential.
• Never use loading methods inside loops: This causes repeated database calls and serious performance penalties. Refactor code to batch-load data.
• Load only required fields: Configure loading methods carefully to fetch only the fields you need.

User Profile Loading

The tools.getUserXXX methods support various profile constants. Use the lightest profile that satisfies your use case:

• PROFILE_TITLE: Loads only the user's display name/login
• PROFILE_DEFAULT: Loads common user fields
• PROFILE_FULL: Loads all user data (use only if absolutely necessary)

📖 See the JSDoc documentation for complete details on profile constants.

---

IndexRecordContainer

If performance cannot be improved through SQL or standard RecordContainers, consider using an IndexRecordContainer.

(Contact ADITO Support for guidance.)