Updating a row using accelerated tables; efficiency question?

Anvil Q&A
1

Is the second code more efficient? or no difference?

1-

katherine_row = app_tables.my_table.get(q.fetch_only("name"),name='Katherine')
katherine_row.update(email = 'katherine@google.com')

2-

katherine_row = app_tables.my_table.get(q.fetch_only("name","email"), name='Katherine')
katherine_row.update(email = 'katherine@google.com')
1 Like
2

My extremely wild guess is that fetch_only only affects the functions that return row objects and makes no difference on update.

In other words row.update(email = 'katherine@google.com') will do something like UPDATE {row.table_name} SET email = 'katherine@google.com' WHERE id = {row.id};, and will not care about what columns have been previously loaded (I made up the members of row and used simple string substitution for clarity, that’s not how it actually works).

2 Likes
3

:sweat_smile: Thank you @stefano.menci!, however, It would be very helpful to know for sure, before changing multiple lines of codes :grimacing:.

4

As with most “efficiency” questions, it’s usually best to measure it both ways, and see.

When there are layers of caching and other optimizations under the covers – and there are some well-hidden layers we don’t normally think about – intuition becomes very untrustworthy.

2 Likes
5

I’d imagine if you dropped a timer on both the difference would be negligible to be honest. But now I think we all need to know :joy:

1 Like
6

I absolutely agree!

And, unless the impact on performances requires it, it is also usually best to do nothing, rather than changing the code and making it less readable.

In this case for example it may be well worth using the q.fetch_only argument, because the table may have linked tables or columns with large amount of data, and fetching less data may be a noticeable waste of time. But I wouldn’t waste my time investigating the difference between the following 3 options, unless I was desperately hunting for the cause of low performances:

  • q.fetch_only("name", "email")
  • q.fetch_only("name")
  • q.fetch_only()
1 Like
7

Hi @clinical,

The answer is “no difference” - when you’re writing a new value to a column, it doesn’t matter whether we have a value already cached. Either way, your code will make two server calls - one for the get() and one for the update(), each with fairly small payloads.

But the deeper answer is what @stefano.menci said - fetching an extra few bytes for a short string like a name or email isn’t going to move the needle much. The big driver of performance is going to be whether you’re fetching hundreds or thousands of short-to-medium-length strings (eg when you’re fetching a lot of rows from a table with a search()), or if you’re fetching a small number of rows but they have one or more huge columns with kilobytes of data (eg a long piece of text or a giant SimpleObject). That’s where fetch_only really shines, because it lets you save a lot of unnecessary data transfer.

3 Likes