New Beta: Accelerated Tables

[meredydd]

Hi all,

Do you want:

• Faster searching of Data Tables?
• More predictable performance?
• Fewer round-trips to the server when displaying linked rows?
• Control over which columns to fetch from which tables?

Good news – we have just completed a full rewrite of the Data Tables back-end, bringing all of these and more, and we’re launching it as a beta today. This does not change the existing Data Tables API – it will just make your apps faster and smoother! (We’ve also added some extra functionality, and we’ll be adding more - scroll down for details.)

To opt into this beta, open the Data Tables section of the Beta Editor configuration:

Check the box, then run your app! Nothing should change, apart from your database access getting faster

NB: This flag is stored as part of your source code, so it’s safe to enable it in the development version of your app, without affecting the functioning of the rest of your app until you deploy that change to production. The format of the database itself hasn’t changed, so you can flip back and forth.

We’re looking for feedback. Obviously, please tell us if anything goes wrong – but also let us know what performance impact you see

---

Advanced feature: Partial fetches

If you want to gain better control of your searches, and retrieve less data, use the new fetch_only search modifier to choose only certain columns to cache.

from anvil.tables import query as q

# Get rows from the "users" table, only fetching the "email" and
# "enabled" columns:
rows = app_tables.users.search(q.fetch_only("email", "enabled"))

This will return partially-cached Row objects: If you touch any other columns in that row, you will incur a round-trip to the server while the rest of the data is fetched.

You can even control fetching of linked data, eg:

# Get rows from the "users" table, following the linked "group"
# column but only caching the "name" column of the linked rows.

rows = app_tables.users.search(q.fetch_only("email",group=q.fetch_only("name")))

You can also pass fetch_only to the get() and get_by_id() methods.

---

For more information, check out the documentation:

Accelerated Tables Beta

[rickhurlbatt]

This is excellent thank you Team!
I have done some initial testing and the results are very exciting.

Some of my forms now load 5 times faster!

I will definitely be reverting all of my code to use linked rows!

[stefano.menci]

I ran a few performance tests on NON real world apps:

• Writing is a little slower
  • delete_all_rows takes 4 times longer (my guess it that, while in beta, they are focusing on stability and read performance, this is a rarely used function and I don’t really care if it’s slow)
  • add_row takes between 10% and 25% times longer
• Reading is much faster
  • search takes anywhere between a little longer and much longer, because that’s were the caching is done, but…
  • row['col_1'] is a gazillion times faster
  • row['linked_table_1']['col_1'] is a gazillion² times faster
  • row['linked_table_1']['linked_table_2']['col_1'] is a gazillion³ times faster

Basically the little time lost during the search is gained by orders of magnitude when accessing the fields of the row.

Now it is possible to make a list comprehension on a search iterator without fear that it will be too slow.

I have tested circular relations and everything is managed very well.

I have not done real world tests. I imagine there will be cases where a table with a deep cascade of links to other tables could lead to caching more data that the app actually needs and the search might waste too much time caching it. In those cases the q.fetch_only will help preventing useless brute force caching.

---

Thank you Anvil.

After years learning not to use linked tables because of performance problems (because they are slow in general and even more slow when you hit the n+1 problem (always)), I need to relearn how to design my databases.

And after repeating in the forum the advice not to use linked tables, all my posts are obsolete now.

Really, thank you Anvil.

[idplastiphar]

This is exactly what I have been hoping for !!

Thank you anvil crew !

[nathanguyette]

Awesome. I’ve been looking forward to something like this for a long time

[jshaffstall]

I’d done some timings related to simple object and link column accesses a while back before accelerated tables were a thing:

first simple object: 0:00:00.131000
second simple object: 0:00:00.125000
first link: 0:00:00.125000
second link: 0:00:00
row.update: 0:00:00.000733
updating fields: 0:00:00.002156
first simple object 2: 0:00:00.118000
second simple object 2: 0:00:00.152000
first link 2: 0:00:00.129000
second link 2: 0:00:00

The first and second accesses to a simple object column both took about the same amount of time, presumably because simple object columns weren’t being cached. Link fields were cached, so the second access to those took no time.

The timings after updating data on the server were checking to see how the automatic update of the row on the client worked, and mirrored the first set of timings.

With accelerated tables turned on:

first simple object: 0:00:00
second simple object: 0:00:00
first link: 0:00:00
second link: 0:00:00
row.update: 0:00:00.000780
updating fields: 0:00:00.002120
first simple object 2: 0:00:00
second simple object 2: 0:00:00
first link 2: 0:00:00
second link 2: 0:00:00

Everything is cached up front, so all the column access basically takes no time. This is great!

[hugetim]

Yes!

[hugetim]

@meredydd

(We’ve also added some extra functionality, and we’ll be adding more - scroll down for details.)

I just want to make sure I’m not missing anything: the details on the second part, what you’re still planning on adding later, are not revealed yet, right?

p.s. Adding fetch_only capability to anvil.users.get_user is at the top of my personal wish list.

[rickhurlbatt]

Just some feedback on this. I have been doing a lot of testing on the value of this new feature.

I consistently see a huge improvement (ie 3X or greater when using linked rows for the majority of my tests) and the functionality additions such as the automatic cached row updates are extremely valuable.

I also found that this new service is slightly faster than the simple object approach and provides a whole lot more functionality and dev speed since you don’t have to constantly search for linked rows based on a row id or uuid.

This is such a gamechanger that I have blocked out the next four weeks to do a ground up rebuild on my production app to capitalise on the new features.

#NoSleepTillBrooklyn

#AnvilForTheWin

[alexBR]

It seems like this addresses issues with get_id() requiring a time consuming remote call.

I currently use row._spec[‘id’] to work around that, but I see that’s not an option with accelerated tables (i get KeyError: ‘id’).

Is get_id() the fastest option now? I switched to it now that row._spec[‘id’] doesn’t work and it does seem very fast.

[rickhurlbatt]

As far as I am aware you are correct get_id() no longer requires a server call

[p.colbert]

Also see BETA: AnvilWrappedError: No module named 'anvil.tables.v2' - #4 by p.colbert

[tomka]

When I activate the accelerated tables feature and call a server function (simple anvil.server.call() ) I get this error:

RuntimeUnavailableError: Downlink disconnected: 98fe2fe647a3

App is on Full Python 2. Maybe that is the issue?

[splinter]

fetch_only capability on get_user would be very helpful from a security perspective, limiting access to more sensitive fields but still being able to store them in the user row would be a significant improvement.

get_user returns a live object without accelerated tables turned off, but is returning something completely different with it turned on, accessing something like user["email"] returns an error TypeError: list indices must be integers or slices, not str with it turned on, breaking a lot of my code.

[rickhurlbatt]

Question: Has there been some more updates to this new data tables service - it seems that data table searches for me are even faster than what they were a few weeks ago - almost on par with SQL searches?