Database query performance

Anvil Q&A
21

I’m fairly certain that is some kind of bug. I had forgotten that I saw the same thing yesterday when I was testing your app. For now, I would write your function to update one column at a time.

I will submit a simplified version of the app for the Anvil team to consider as a bug report.

Also, when sharing a clone with others, you will want to remove (or make available) your dependencies as the app will not work directly for others who do not have those dependencies.

22

I’m sorry, do I have dependancies on my cloned app?
I didn’t think so.

23

I finally managed to migrate my table to BlobMedia fields and to avoid all those round-trips for permissions check, and now my app runs much more smoothly, thanks again to @alcampopiano and @stefano.menci.

Working with Blobmedia columns, though, has proven to be a difficult task.

  1. the bug already filed by campopianoa here forces to update a row one blobmedia field at a time. In my table I have 4 blobmedia columns and a bunch of other fields, so I need to write 5 update statements to update one row
  2. when you update a blobmedia field wiht a new value smaller (in bytes length) than the current value, you need to set the field to None before updating, else the remaining bytes of the current value will be left in place. In my case, for instance, my fields are “text/plain” BlobMedias. If I set the field to the string “ABCDE” and then I update the field to “ZZZ”, I end up with a field value of “ZZZDE”.

The combination of the two, led me to write 9 update statements to update 1 single row! :frowning:

Below a copyapp to show point 2), is that a new bug to file? :sweat_smile:

https://anvil.works/build#clone:JAXQKXPQ6ZHMSR7X=WJGMMM2DEYWUYK6C2S3GCQR3

Bye bye

3 Likes
24

Those are both bugs, thanks for taking the time to bring them to our attention. Don’t worry about creating a new topic, we’re aware of them now :slight_smile:

1 Like
25

For reference, these bugs are now both fixed!

Also, one of these bugs only occurred in the Basic Python environment, which might also be contributing to your performance issues. If you’re a paid user, you have access to the Full Python environments (2.7 or 3.7), which have distinctly better performance.

2 Likes