6

Improve performance: Count records from subtable WITHOUT select statement

If you are counting records from a subtable, you can avoid using the 'select' statements...
For example:

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!

8 replies

null
    • ninox.1
    • 1 yr ago
    • Reported - view

    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. :(

    • Ninox developper
    • Jacques_TUR
    • 1 yr ago
    • Reported - view

    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. 
     

      • John_Halls
      • 1 yr ago
      • Reported - view

      Jacques TUR Maria I come from a FileMaker background and was always taught to try and keep these calculations to a minimum. In an invoicing situation I might have the sum of the invoice lines as a calculation, sum(InvoiceLines) while I am creating the invoice, but once saved that calculation is transferred to a number field in the Invoice table and at the same time the total of all invoices (balance outstanding, etc) for a customer can be saved to a number field in the customer table too.

      Regards John

      • Ninox developper
      • Jacques_TUR
      • 1 yr ago
      • Reported - view

      John Halls It is true that this method is even faster for searches. On the other hand, it means creating calculation "duplicates" and therefore monitoring the validity of these duplicates in one way or another. Sometimes it is very simple, as in the case of an invoice which is closed and for which no further changes will be made. Sometimes it is a question of values that can change at any time, and in this case a monitoring mechanism must be put in place for each change, with the risk that a change will not be taken into account. 

      Generally speaking, it is a balance to be found between the complexity of managing duplicates and calculation time. 

      In any case, what Maria was talking about in this post is the optimisation of the calculation time itself, whether it is done once or at each display.

      • John_Halls
      • 1 yr ago
      • Reported - view

      Jacques TUR Very true Jacques

      • Ninox developper
      • Jacques_TUR
      • 1 yr ago
      • Reported - view

      John Halls 

      In fact, the array of links that allows to find all the Invoices that point to the Customer records and that are stored in each record of the Customer table (Customer.Invoices[]), are already duplicates managed directly by Ninox.

      In a SQL database this does not exist and it is necessary to use a Select function to find all the Invoices that point to each Customer. These are called joins. And when the tables are large, it takes a long time. One of the strong points of Ninox is that it memorises the joins.

      So when you already know SQL and you discover Ninox, you expect to find SQL functions and you may be confused. But in reality Ninox allows to extract data much more easily and quickly than SQL, it's just badly or not documented. And here I agree with Mike , there is a lack of documentation on the subject.

      Maria , what about you think of creating a documentation space that shows the equivalences between SQL and Ninox functions? For example how to do joins, sorting, grouping. I feel that this would help many new users (and maybe even old ones) to understand how the Ninox database works and how it is possible to integrate the Ninox language into select function (select Customer where (for i in Invoices do .... end) ). 
      It would also help to talk about array management (as we have already done here) and JSON structures. In short, these are rich explanations, aren't they 😊?

Login to reply

Content aside

  • 6 Likes
  • 1 yr agoLast active
  • 8Replies
  • 896Views
  • 6 Following