Performance optimization: eliminate sub queries and joins by using cached functions (persisted computed columns)

[Q&A] General
1

Novulo applications run a MSSQL database servers and Novulo expressions that populate grids, forms, exports and end points translate into SQL statements.

As a result, when Novulo applications are populated with large amounts of data, and data is being searched using multiple tables, performance optimization can be needed - just like with any other database-driven application.

The Novulo platform does contain a series of mechanisms that allow for efficient query optimization when searching results in large sets of data. In this post we discuss using:

  1. Creating indexes from the Architect
  2. Implementing cached functions

Typical use cases for queries in large sets of data that require fast response times include searches for:

  • Products on a website for a specific category, brand, price range or property on Product lister pages
  • Billable sales lines in batch invoicing processes
  • Products to be purchased in automated purchase runs
  • Employees and availability in capacity planning applications

Preventing joins and subqueries in high-performance searches

When searching data in a database tables, the fastest results are achieved when searching in one table, with a perfectly matching index.

Think of it as a book with an index: when you search for a specific word in the index, you can browse to the desired page quickly and you don’t need to read the whole book.

In database-driven applications, you often use more than one table by joining tables. As a Novulo implementation consultant, developer or key user, you are probably joining tables very often by just using dots in your expression.

For example, when you search for the invoice lines sent to Novulo, you will run the following expression triggering two joings.

{invoicelines, this, invoice.contact.contact_name.startswith("Novulo")}

Now, you are searching in three tables.

  1. Contacts, to find the contacts with a contact name starting with “Novulo”
  2. With that result, find invoices that belong to the contacts just found
  3. With that result, find the invoice lines that belong to the invoices just found

Now, imagine that we store the contact name at the Invoice line, by using a Cached Function on InvoiceLines: invoice_contact_name(), which is =invoice.contact.contact_name.

Now, we can do:

{invoicelines, this, invoice_contact_name().startswith("Novulo")}

And the database server will only need one table to search, making it faster.

Now, when you only search for this every now and then, don’t bother creating the cached function. But if this is an expression that’s being searched multiple times per minute, it can make sense to cache it.

So: when searching, try to eliminate joins (dots) by caching functions where relevant

See also: Optimizing slow expression with and OR statement over different tables (terrible OR)

2 Likes