Improve performance: Count records from subtable WITHOUT select statement
If you are counting records from a subtable, you can avoid using the 'select' statements...
Instead of using the code:
let t := this; count(select Invoice where Customer = t and Status = 1)
Use the code:
count(Invoices[Status = 1])
Optimizing your code in this way will improve the performance of your database and let you work more efficiently...
So, make sure to use 'select' statements ONLY when really necessary.
Have fun with Ninox!
Will Ninox be updating their documentation with the new and improved count syntax? From my searching just now, I only saw the Select version.
Also, I still don't find the "SELECT" documented anywhere by itself, only with other functions.
Documentation is SO frustrating and lacking. :(
This performance point is really important. Thank you Maria.
Let's say we have 100 customers and for each customer, about 5 invoices.
Using the Select function to find the invoices, the Ninox database engine will have to do 500 comparisons per customer (100*50). If you display this formula in a table view, Ninox will run it for each customer and 50.000 comparisons will be made (100*5*100).
If you use the brackets, it will only take 5 comparisons per customer and only 500 to display this formula in a table (100*50).
Now imagine that you have a table with 1000 customers and for each of them about 50 invoices. Using Select it would take 1000*50*1000 50,000,000 comparisons to display it in a table view, compared to 50,000 using brackets.
This calculation is not quite true, because Ninox does not calculate all the rows in a table view at once. It only calculates those that are displayed on the screen. But if you scroll through all the rows or do a sort or filter on the column that contains the formula, then the maximum number of comparisons will be executed.
- 1 yr agoLast active