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.
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.
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.1
do as transaction
2
for company in select Companies do
3
company.(Contacts := first(select Contacts where Id = company.Id))
4
end
5
end
Result: All records in the Companies table are linked to an entry from the Contacts table within one transaction.
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.Some functions cannot be executed on the server because they are related to your browser or app, e.g.,
alert().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.1
let response := do as server
2
http("GET", "https://api.ninoxdb.de/v1/teams/" + teamId() + "/databases/" + databaseId() + "/tables/" + tableId(this) + "/records", {
3
Authorization: "Bearer 0xxx0000-000x-00xx-x0x0-0x0x0000000x"
4
}, null)
5
end;
6
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.1
[{
2
"id":1,
3
"sequence":129,
4
"createdAt":"2021-03-11T08:43:53",
5
"createdBy":"xx0xxXX0XxxxXXxXx",
6
"modifiedAt":"2022-01-21T14:20:36",
7
"modifiedBy":"xx0xxXX0XxxxXXxXx",
8
"fields": {
9
"Product name":"Ninox Cola",
10
"Product ID":"PID-123456789",
11
"Price":0.99
12
}
13
}]
Normally Ninox executes write transactions in sequence.
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.
🚀
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.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.
1
if Status = 3 then
2
'Sent On' := today();
3
do as deferred
4
sendEmail({
5
from: "[email protected]",
6
to: "[email protected]",
7
cc: "[email protected]",
8
bcc: "[email protected]",
9
subject: "Your invoice",
10
text: "Pay €100."
11
})
12
end
13
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.Last modified 7mo ago