Skip to content

Dont expand formulas

Davide Silvestri requested to merge dont-expand-formulas into develop

What is in this MR

An important refactor for the formula language to update fields in the correct order instead of expanding every referenced formula in every field.

In a table with a text field A, a formula F1 referencing A and a formula F2 referencing F1, before this refactor we were replacing every reference of F1 with the corresponding formula. This had the advantage of updating a row in a single update statement where all the formulas can use the non-formula field types.

In a table with numerous formula fields that reference other formulas though, we found that this method generates a large ANTLR tree and results in a sluggish SQL query. As a result, the simple cases are easy and efficient, while the complex cases are too slow to be practical.

This MR contains the logic to utilize the field dependencies table for determining the correct order to update the formula fields. This involves splitting the update into multiple statements to update the intermediate results first and the final formula last. By doing so, we can issue smaller and faster update statements without expanding intermediate formulas.

How to test this MR

  • Create a complex scenario:

    • Create a table with number and single selects fields.
    • Create multiple formulas comparing the single select field with different values and performing different calculations based on the comparison's result.
    • Create other formula fields, using previous formulas and making additional comparisons/calculations.
    • Do it again and again

  • Or ask @_dvd to share a test workspace

  • start the backend in production mode with: BASEROW_BACKEND_DEBUG=off DJANGO_SETTINGS_MODULE=baserow.config.settings.base ./docker/docker-entrypoint.sh gunicorn-wsgi

  • Notice the difference when:

    • opening the table
    • creating a new row
    • updating a cell
    • updating a field

  • Verify everything in the formula language still works the same as before, just faster

  • Please ensure that the previous formulas are still functioning as they did before. When you save them again, they will no longer expand referenced formulas and will start using the intermediate result instead. Note that older formulas that have not been resaved might be slower now than before because the inner formula is still the expanded one so it could still be updated in a single UPDATE statement instead of multiple ones in the right order. However, if everything works as expected, we could consider creating a formula migration in the future to re-save every formula to start using the new logic.

Merge Request Checklist

  • changelog.md has been updated if required.
  • New/updated Premium/Enterprise features are separated correctly in the premium or enterprise folder
  • The latest Chrome and Firefox have been used to test any new frontend features
  • Documentation has been updated
  • Quality Standards are met
  • Performance: tables are still fast with 100k+ rows, 100+ field tables
  • The redoc API pages have been updated for any REST API changes
  • Our custom API docs are updated for changes to endpoints accessed via api tokens
  • The UI/UX has been updated following UI Style Guide

Closes #2757 (closed)

Edited by Bram Wiepjes

Merge request reports