Write data table entry without reading whole row?

Anvil Q&A
1

I recently had a painful realisation that by default, when you get a data table row, you actually read all columns of that row even before you do anything. The pain came in when I added a column to a key data table which had large amounts of data in it, and everything slowed to a crawl.

For example, this code:

userDataRow = app_tables.user_data.get(user=user)
print(user["email_address"])

becomes much slower if you add a data-filled column to the user_data data table, even though you’re not actually looking for the bulky data.

This can be addressed using q.fetch_only, as in this helper function I made:

import anvil.tables.query as q

def getUserDataEntry(column):
  userDataRow = app_tables.user_data.get(q.fetch_only(column), uid=getUid())
  returnValue = userDataRow[column]

So I’ve solved the problem of accessing a single data value without needing to get the whole row.

Now what I’m stuck on is how to write a data value without accessing the whole row.

By default I would do this:

userDataRow = app_tables.user_data.get(user=user)
user["email_address"] = "test@test.com"

But again, the first line is very expensive. Any suggestions?

2

You already answered your own question:

userDataRow = app_tables.user_data.get(user=user, q.fetch_only('email_address'))
user["email_address"] = "test@test.com"

The get method returns a row object. The row object can immediately spend time fetching data or do it later lazily. All you need to do is tell the row object not to waste time fetching useless data.
Perhaps it will work with q.fetch_only().

1 Like
3

Haha, d’oh! Thanks!

Well I will leave this here in case it’s helpful to someone in the future. It took an extreme case for me to realise how much performance I was losing to unnecessary data-fetching.

4

Same goes with the opposite: some column types are not automatically fetched, so they are fetched lazily, which is slower, especially if done on the client side. I don’t remember what column types are lazily loaded, but I think it was more with the old tables and fewer with the new accelerated tables.