In the past few days I’ve been doing some little changes to an old app that was using only sql. There was no import app_tables at all, because it was using very complex relational queries.
The changes I’m doing today only access one table, so I have added the necessary imports and used Anvil’s app_tables. I have been sloppy, that is for example I have a few queries, then a loop that does a bunch of app_tables.my_table.get(), then more queries. I could build one single sql query that does it all in one shot, or perhaps I could use smarter q.something operators. But it was easier for me to go with a few python loops rather than diving into ctes and complex sql queries or spending 15 minutes refreshing my memory on q.something operators.
The result is that querying a table with 1000+ rows 300 times and returning lists with about 10KB of data, takes less than 3 seconds. 300+ queries is the worst case scenario, most of the cases require 50-ish queries, and the UI is updated in less than 1 second, and I was asked to do it slower, because sometimes the user doesn’t even know that something has happened. That’s a nice problem to have!
I really enjoyed seeing the presentation about the integration with Supabase, I want and need to see how other users approach the limitations of Anvil. And the lack of relational queries is a limitation. But this was presented as a solution to a performance problem, and I just can’t see it.
I agree with Meredydd:
There was once a case where a table had become too slow, and having 10 users with the browser open, with the app with a timer that polls the server every two seconds was starting to cause a problem. In that case I split the table in two, the one that is continuously polled with the data from last week, max 1000-ish rows, and the historical content only used for reporting in a second table, with tens of thousands of rows.
I have other tables with 200,000+ rows that work just fine, other tables with very large simple object columns that work just fine, but in years of Anvil use and hundreds of tables used, that one table was the only one tickling the Anvil database the wrong way.
So, there may be edge cases where performances are actually a problem, but even when they are a problem, it’s never above 2-3 seconds.
I have the feeling that if the IDE showed all the round trips, one would know what’s slowing down the app and could fix it, rather than jump to an external database.