Optimize performance of scripts

do as transaction ... end | do as server ... end | do as deferred ... end

Ninox executes scripts in a perpetual exchange between the browser or app and the server. Usually, this works fine because the exchange is fast enough to process a script quickly.

However, in some cases you may further optimize the performance of your scripts by placing instructions in special code blocks.

More about Tips and tricks for fast databases.

do as transaction ... end

To ensure scripts are executed within the same transaction, it's best to use do as transaction.

do as transaction was developed specifically for mobile apps (iPhone, iPad, Android) and desktop app (Mac) to process scripts locally. For example, when the internet connection is interrupted.

When use do as transaction in the web app, the script is always executed on the server.

The nesting of loop and select commands is usually very performance-heavy, so we recommend wrapping such scripts in a do as transaction block to speed up the process.

If there are any issues executing the script for whatever reason, the transaction is discarded, i.e., a transaction is either executed completely or not at all.

More about Transactions.

Example

Let's assign a Contact from the Contacts table a matching ID to the Contact field in each record of the Companies table. The entire process is executed within do as transaction.

do as transaction
	for company in select Companies do
		company.(Contacts := first(select Contacts where Id = company.Id))
	end
end

Result: All records in the Companies table are linked to an entry from the Contacts table within one transaction.

do as server ... end

Occasionally, it may be beneficial to fully execute a portion of your script on the server first, before sending it back to your computer or app.

We don't recommend using do as server in triggers because triggers in a browser are always executed on the server as well as locally in the app. Rather use do as server in buttons.

do as server is most often used in conjunction with the http() function to execute API calls server-side first. This bypasses the browser's CORS (cross-origin resource sharing) policy, which would otherwise block the http call. Then you receive the requested data from the server.

More about API calls.

Some functions cannot be executed on the server because they are related to your browser or app, e.g., alert().

Example

Let's get some data from a table in a database. First, you send a GET request to the respective database. Using the dot operator . , access the values of the response.

let response := do as server
				http("GET", "https://api.ninoxdb.de/v1/teams/" + teamId() + "/databases/" + databaseId() + "/tables/" + tableId(this) + "/records", {
				Authorization: "Bearer 0xxx0000-000x-00xx-x0x0-0x0x0000000x"
		}, null)
	end;
response.result

Result: The result value of the response is returned, which in this case consists of only one record and the information contained in the individual fields.

[{
    "id":1,
    "sequence":129,
    "createdAt":"2021-03-11T08:43:53",
    "createdBy":"xx0xxXX0XxxxXXxXx",
    "modifiedAt":"2022-01-21T14:20:36",
    "modifiedBy":"xx0xxXX0XxxxXXxXx",
    "fields": {
                "Product name":"Ninox Cola",
                "Product ID":"PID-123456789",
                "Price":0.99
              }
}]

do as deferred ... end

Normally Ninox executes write transactions in sequence.

Tip: Get familiar with the difference between Read transactions and Write transactions.

Some of these write transactions may take more time, causing subsequent transactions to wait a bit. This may cause Ninox to slow down unintentionally.

Use the do as deferred statement to ensure read transactions within a write transaction are split off into a separate (read) transaction—all of which can be executed in the background.

This prevents subsequent transactions from getting blocked and boosts performance. 🚀

A real-life use case

When you modify data in a large table, this may take several seconds instead of a few milliseconds. Downstream statements then have to wait until all data is completely modified.

do as deferred speeds up the processing of statements because statements that occur between do as deferred and end are processed separately and thus do not interfere with other processes.

Example

Optimize a status change with a resulting triggered email as follows.

Set a field Status = 3. This causes the trigger stored in the Status field to trigger after change the following instructions (see code block):

1. The field sent on is set to today's date.

2. An email with a payment request is sent. This is executed separately.

if Status = 3 then
	'Sent On' := today();
	do as deferred
		sendEmail({
			from: "[e-mail]",
			to: "[e-mail]",
			cc: "[e-mail]",
			bcc: "[e-mail]",
			subject: "Your invoice",
			text: "Pay €100."
		})
	end
end

Result: All records whose status is set to 3 have today's date stored in the Sent On field. In a detached transaction, an email requesting payment is also sent to all records with the status 3.