Anvil User Group Aug 27

Show and Tell
1

Hey everyone!

The next Anvil User Group is going to be on Aug 27. Here’s our draft agenda:

  1. 5 minutes - Quick Intros (Name, what you use Anvil for)
  2. 5 minutes - Breakout rooms for 5 minute get-to-know-you chats
  3. 15 minutes - @Quoc will demonstrate his Anvil - Supabase integration for user authentication and Supabase Postgres
  4. 15 minutes - @divyeshlakhotia will demonstrate his browser routing setup (work in progress) that uses Anvil’s new routes feature
  5. 20 minutes - General discussion

Note that this call will be recorded and transcribed for the attendees.

By RSVPing to this event using the buttons above, you can add the event to your calendar.

Here’s another link to the call below:
Anvil User Group August
Tuesday, August 27 · 11:00am – 12:00pm
Time zone: America/Toronto
Google Meet joining info
Video call link: https://meet.google.com/gro-zusp-xxw
Or dial: ‪(CA) +1 289-317-9072‬ PIN: ‪449 558 105‬#
More phone numbers: https://tel.meet/gro-zusp-xxw?pin=3188673189582

2

Dang, I would love to present AutoBatch, which is getting close to feature-complete, but this date is a little too close to a big work deadline. Next time, though. Thanks for organizing!

3

I’ll pencil you in for the next one!

1 Like
5

Reminder that this is tomorrow (Tuesday) Aug 27! See you all then!

3 Likes
6

It’s now if anyone is interested!

7

Summary

  • Supabase is much faster than querying the Anvil data tables, especially when you get to more than a few dozen rows
  • Token based authentication is more flexible to be used with multiple different clients (anvil app, native app, chrome extension)
  • If you query from the front end, you have to set up row based security in Supabase
  • Browser routing allows you to make your SPA work more like a MPA
  • Built browser routing to work similarly to anvil-extras hash routing
2 Likes
8

For anyone interested, this is the example app built using Browser Routing

I will be working on releasing this as a dependency soon.

4 Likes
9

Nice summary - are there any recordings of these? Would love to catch up on the discussions, unfortunately there always a bit late in my timezone.

1 Like
10

I’ll be sending the recordings out this weekend!

4 Likes
11

Thanks! I’m so sorry I couldn’t make this one (we were interviewing candidates), so I shall look forward to the recording with interest!

Is there somewhere we can point people to subscribe for notifications of the AUG? I know @daniel.bolinson’s app had a mailing list function for notifying people when a new AUG had been scheduled – could we have something similar? I’m writing the August newsletter right now, and would love to plug the AUG, but I don’t know where to send people yet.

12

We are not currently using that, rather we’re keeping people in the Forum. Perhaps subscribing to the “announcements” category of the forum would suffice? Otherwise we can go back to Dan’s mailing list

13

I’ve never accessed the Anvil database from the front end, and I’ve never used any external database.

Using an external database to take advantage of flexible and powerful queries makes sense, considering the limitations of the Anvil database. But I’m surprised to hear that you are using it to improve performances.

I have apps that query thousands of rows in the Anvil tables and never had performance problems. I do have a dedicated server with SQL access, but I only use SQL for complex relational queries, never for performance reasons.

Is Supabase faster than Anvil data tables because it’s faster for the front end to query and get a result from the Supabase server than giving the client access to an Anvil table and querying from the client?

Or is it faster because, even when working from the server, it’s faster to fetch data from the Supabase server than from the Anvil data table?

I doubt it’s the latter, because (very likely) the Anvil database server is closer to the Anvil web server than the Supabase server.

If it’s the former, then it will be interesting in my next app to rethink the approach: instead of having the client calling the Anvil server for everything, the client will access the database directly for certain things, the Anvil server for other things. Then every server call will need to contact the Supabase server to check for authentication and do whatever it needs to do.

Hmm… Can you please somehow quantify or describe the improvement that justifies adding complexity to the app and managing my own server?

2 Likes
14

In my experience, it takes at least 2-3 seconds to query using the .search() method, even with just a dozen rows. That is with the server startup + query time combined on the old Professional plan. I have done all the optimizations and can only get it down to 2 seconds minimum.

What defines acceptable performance will vary by use case (and by developer) but I can generally live with 2-3 seconds, especially with caching and loading screens.

Since @Quoc wanted something faster, he went with Supabase which allowed him to query from the client and avoid starting up the Anvil server. Supabase is super fast like Firebase (see Mark’s original post on Firebase where he was able to make his app virtually spinner-free).

I’m almost certain your dedicated database speeds queries up, even when you don’t use SQL.

Querying data tables from the client is a non-starter in most use cases since you can’t implement row level security in Anvil data tables when querying from the client. You can (and should) do that in Supabase.

Personally I have not tried the Supabase database but I would if I needed faster performance for cheap.

15

Via Server-side code, you can. Just have your Server code do the work, on request from the Client, and don’t give the Client any more access than it needs.

See Data Security.

16

There isn’t a big enough “eye-popping” emoji for my reaction to this. 2-3 seconds?! I know Data Tables performance could stand to be improved, but if you’re seeing that kind of latency from a simple search of a few dozen rows (as opposed to because you’re fetching a ton of rows or huge data, or importing tons of slow modules in Server Module startup, or something like that), then, uh, can you please DM me a repro?!

2 Likes
17

I have created a little test app: Anvil | Login

The app fills a table with 15,100 rows, allocating 100 for the small user and 5,000 for the large user.

The form has one button to get a writable view with all the rows of user small, one with all the rows of user large and one to query a random number of rows (between 10 and 90) from the view previously fetched.

I have tested this little app on my free account and on my business account with dedicated server with and without persistent server.

Here is what I found:

  • populating the database with 15100 rows in the free account is 30% faster than with the dedicated server (:astonished:)
  • the import time does affect server calls (without persistent server), but does not affect client side queries
  • fetching a readable view is slightly faster than fetching a writeable view
  • the time required for fetching a view is:
    • not affected by the number of rows in the view or in the table
    • 0.9 seconds on the free account and 0.15 seconds on the dedicated
  • the time required for counting the rows is:
    • not affected by the number of rows in the view or in the table
    • 1.5 seconds on the free account and 0.65 seconds on the dedicated

I am surprised to find out that adding rows with the dedicated server is noticeably slower than with the free account.

Everything else is as expected:

  • on the free account almost 1 second per server call (with no heavy imports) and a little time working with data on the client
  • on the dedicated account everything (except adding rows) is faster, but often not enough to justify a change in strategy.

I loved @Quoc’s presentation. I loved to see how it is possible to replace both the Data and the Users services with one external resource. And I loved to see how it is possible to shift most of the logic to the client side.

I’m closely monitoring this because I’m concerned that the Anvil database might have significant limitations. But this concern is 7 year / 100 apps old by now, and still haven’t found an app that can’t work with it.

As mentioned earlier, all my apps make one single server call per user interaction, and all the database querying happens on the server. I can’t think of an app that has response time above 1 second (except PDF or heavy report generation). I found one a few months ago. It was made by a colleague of mine that wasn’t following the one-call-per-user-interaction rule, and it was returning row objects to the client rather than json objects, and after a few years it became unusable. I refactored all the accesses to the database and now every user interaction happens in less than one second.

Perhaps my little test app should be extended as @meredydd asks to include use cases that really spill into 3+ seconds?

2 Likes
18

Same experience here as @stefano.menci - 1s per server call + 1s to fetch a view. Not to mention counting the rows. Again, not a big deal to me but that is in line with my experience.

Here’s a private link to the meeting recording and transcript: AUG 240827 – Google Drive

You’ll have to request access through that link - just add a note to it with your name and I’ll approve.

1 Like
19

I made the transition to firebase, a very similar product to Supabase (it’s no-sql predecessor) , because of similar speed experience as @yahiakalabs . I was looking for a real-time solution for a logistics software. The caching, messaging brokerage management, and resources needed made the transition necessary, was overwhelming.

All to say, I think anvil’s database is great but definitely there are some use cases for a quicker solution. It’s also quite noticeable when you go from 1+ seconds response to almost instant for the user base.

I’ll add that I definitely could have use more optimization in my original implementation of the anvil-database, but nonetheless saw the transition as a easier/long term solution.

20

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.

3 Likes