Tips and tricks for fast databases

Learn how to boost the performance of your databases

First, a checklist of common questions and advice:

What are the potential bottlenecks?

A look at the process monitor helps identify areas for improvement.

Is there a way to optimize the conceptual structure of the database?

Take a look at your data model to see if there is room for improvement. Large databases have longer access times.

Is there a way to split the database into smaller tables, possibly using sub-tables?

Do you notice longer loading times when accessing certain views?

Can you split the data into multiple views by filtering the views? Or hide columns that are not directly needed? Is there a way to reduce the number of columns with formulas?

Also, can data be grouped so that less data needs to be loaded at once?

Have the scripts been optimized? How are certain data accessed?

Review your scripts. Often a script can be split into multiple scripts to speed things up.

The next section provides some suggestions for optimizing scripts.

Tips and tricks

We'll show you how you can improve access and processing times just by building your script:

• Why nested loops are tricky
• How do as ... end can improve performance
• Why you should use select carefully

Why nested loops are tricky

When using select or a for loop inside another loop, consider whether it is really necessary.

Can the nested part be executed independently? If so, run the loops one after the other.

Example

Let's assume that Table1 and Table2 each have 1,000 records.

let amount := 0;
for i in select Table1 do
    for j in select Table2 do
        amount := amount + 1
    end
end;
amount

What is the value of amount after running the script (in line 7)?

Answer: 1,000,000

For each of the 1,000 records in Table1, we run through all 1,000 records in Table2 each time.

When nesting loops, iterations multiply (m * n).

Do this instead

If possible, execute the loops in sequence, then add the iterations (m + n).

let amount := 0;
for i in select Table1 do
    amount := amount + 1
end;
for j in select Table2 do
    amount := amount + 1
end;
amount

What is the value of amount (in line 8) in this case?

Answer: 2,000

This is because we go through all the records in Table1 first, and then those in Table2.

How do as ... end can improve performance

In Ninox we distinguish between read and write transactions:

• read transactions, such as loading a table, can take place simultaneously
• write transactions, such as adding data, are executed in sequence

More on transactions and how to optimize performance of scripts.

Poor performance is commonly caused by write transactions that hold up other write transactions.

Example 1

This basic script creates 1,000 records.

for i in range(1000) do
    create Table1
end;

At first glance, there is no obvious issue.

However, when running this script in a button in the web app, a dedicated transaction is created for each iteration.

So, when creating the first record, there are 999 more transactions waiting to be executed.

Do this instead

Bundle many small write transactions into 1 transaction.

do as transaction
    for i in range(1000) do
        create Table1;
    end
end;

In this case, the entire loop is executed within 1 transaction. This reduces the queue to 0 in our example.

Example 2

However, it also works the other way around. In the following example, a script in a Trigger after update sends an email to all customers from the Customers table.

Strictly speaking, we are adding the instructions from the trigger script to the preceding write transaction (which initiates the trigger).

for customer in select Customers do
    sendEmail({
        from: "support@ninox.com",
        to: customer.'Email address',
        subject: "Newsletter",
        text: 'Email template'
    })
end

The write transaction is inflated and takes longer than necessary.

Do this instead

Execute non-write components of a large write transaction in a separate transaction.

do as deferred
    for customer in select Customers do
        sendEmail({
            from: "support@ninox.com",
            to: customer.'Email address',
            subject: "Newsletter",
            text: 'Email template'
        })
    end
end

In this case, our trigger script is no longer executed with the preceding transaction, but in a subsequent transaction.

More on do as ... end.

Why select should be used sparingly

When using select, it is helpful to define the desired selection more precisely.

However, avoid using select directly with brackets [...].

Example 1

select Customers['Company location' = "Germany"]

In this case, all Customers are selected first, then filtered by Company location.

Do this instead

Drag the filter condition from the bracket [...] into the select statement.

select Customers where 'Company location' = "Germany"

where selects only matching records from the beginning.

select statements are extremely useful. As a general rule, we consider them as performance-heavy. Any select statement that isn't used is a good select statement.

Example 2

The goal is to divide customers from Germany into 3 groups, depending on their respective sales revenue.

let deLarge := select Customers where 'Company location' = "Germany" and Sales > 10000;
let deMedium := select Customers where 'Company location' = "Germany" and Sales <= 10000 and Sales > 5000;
let deSmall := select Customers where 'Company location' = "Germany" and Sales <= 5000

The select statements in this case are fine on their own. However, we can optimize the script as a whole because the select statements share a lot of similarities.

Do this instead

Aim to use as few select statements as possible. Store reusable return values of select statements in variables.

let deCustomers := select Customers where 'Company location' = "Germany"; let deLarge := deCustomers[Sales > 10000]; let deMedium := deCustomers[Sales <= 10000 and Sales > 5000]; let deSmall := deCustomers[Sales <= 5000];

This script uses only one select statement, which is stored in a variable. This variable is then used to specify the selection.

Also, check for a Ninox function that can help you with your goal. In particular, if it can reduce select statements and loops.

Example 3

The goal is to find the lowest sales revenue in the Customers table.

let smallestSales := Infinity;
for customer in select Customers do
    if customer.Sales < smallestSales then
        smallestSales := customer.Sales
    end
end;
smallestSales

This script is far too complicated for such a simple use case.

Do this instead

Use the appropriate Ninox function, in this case: min(). There are also other useful functions like max(), sum(), first(), count(), and many more.

min((select Customers).Sales)

This script reduces the 7 lines from the previous script to one, which will surely reduce headaches while reading.

More on functions overview.