The performance of table views is influenced by three factors:

• the complexity of the query (filter)
• the number of rows to be loaded
• the data loaded per row

Ninox calculates views in the following sequence:

1. Query from the server all record IDs that match the filter criteria of the view.
2. Load all records that result directly from this filtering;
   and (in the same step) load all records that are expected to be additionally needed to calculate the columns.
3. Calculate all rows and columns

Large number of rows

Performance problems usually occur when either the number of rows (result from step 1) is very high, for example 100,000 rows or more, or when very much additional data is loaded in step 2.

The former can be solved by filtering the data sets more strongly - often users only need current data, for example the invoices that are still open, only active customers or tasks that are not older than two weeks.

A lot of additional data records are loaded

In practice, it is more common to see calculated columns that pull data from linked tables. For example, if you create a table view of all customers and show the sum of the invoice amounts in this view to display the total turnover per customer, Ninox will also load all invoice records and all invoice items in step 2 to be able to calculate the result.

You can solve this problem by hiding the corresponding columns. If this is not possible in individual cases, the view should be filtered even more - or you replace the calculated field with an actual data field in which the value is calculated and stored by a trigger.

Complex formulas

Related to the previous case is the situation where very complex formulas are stored in the columns of the view. To illustrate this:

Suppose a view shows 1000 rows. One of the columns compares a value of the row with 1000 records from another table. This quickly results in 1,000,000 comparisons that Ninox has to calculate, even though the view had a manageable size to begin with.

Don't just look at the immediate formula that is displayed - perhaps the formula uses other formula fields and the complexity is hidden across several levels.

"Sub-selects"

Ninox also provides a select statement in the scope of the script language. Ninox cannot optimise the calculation of select as well as the use of links. select should not be used in formulas of table views, because each row of the view triggers a separate query.