Database query performance

Anvil Q&A
1

My App has a table zoo_session_log with 64 rows.
Field zsl_session is a date field.
I want to fetch the most recent record upon zsl_session, I understand the quickest way to do that is:

@anvil.server.callable
def read_cache_last(season):
  print "read_cache_last"
  print datetime.datetime.now()
  for first_row in app_tables.zoo_sessions_log.search(tables.order_by('zsl_session', ascending=False), zsl_season=season):
    break
  else:
    return None
  print datetime.datetime.now()
  return first_row

The debug printout is as follows:

read_cache_last
2019-09-28 13:11:29.207000
2019-09-28 13:11:38.645000

9 seconds to execute an order-by query on a 64 records table?
Is that normal, even though on a free plan?
Is there a quicker way to do that search?

Thanks

2

More on this, another quite impressive case.
In order to manage user-access to functions, I use a “bitwise” approach:

  • each authorizable functionality has an integer ID corresponding to a specific bit position (es. 1 / 2 / 4 / 8 / 16 / 32 / …)
  • each combination of authorized functionalities is represented by an INT given by the sum of the single IDs (es 4+8=12 means user is authorized to the third and fourth functionalities)
  • each user has a permissions field with the INT of their authorized functionalities.

Being that, when I have to check if - for instance - the button btn_starpvda has to be shown to the user, I call

self.btn_starpvda.visible = anvil.server.call('can_use',user['permissions'],'btn_starpvda')

The function can_use is very simple, it queries the permissions table:

@anvil.server.callable
def can_use(user_permissions, functionality):
  permission_id = app_tables.permissions.get(perm_functionality = functionality)
  # Bitwise check
  return ((permission_id['perm_id'] & user_permissions) == permission_id['perm_id'])

The permissions table has 12 (!!) records.
When I check (in the client code) for several buttons to show, this is the code:

    print "Button enabling"
    print datetime.datetime.now()
    self.btn_starpvda.visible = anvil.server.call('can_use',user['permissions'],'btn_starpvda')
    print datetime.datetime.now()
    self.btn_scarica_dati.visible = anvil.server.call('can_use',user['permissions'],'btn_scarica_dati')
    print datetime.datetime.now()
    self.btn_recalc_stats.visible = anvil.server.call('can_use',user['permissions'],'btn_recalc_games')
    print datetime.datetime.now()
    self.btn_insulti.visible = anvil.server.call('can_use',user['permissions'],'btn_insulti')
    print datetime.datetime.now()

And this is the timeline:

Button enabling
2019-09-28 15:34:17.045100
2019-09-28 15:34:17.337570
2019-09-28 15:34:17.992960
2019-09-28 15:34:18.680005
2019-09-28 15:34:19.338165

It looks really slow to me, even though, being on the free plan, I understand I have no indexes.
But it’s a 12 records table.

Thanks.

3

Have you tried something like this? With 1000 records, it works almost instantly on my end.

print(datetime.now())
recent_row = app_tables.my_table.search(tables.order_by('dates', ascending=False))[0]
print(datetime.now())
4

From your “print statement” syntax I guess you’re on python 3 - hence a paying user.
Is that true?
BTW I’m going to try that right now.
But that would fail if table is empty, right?

5

Tried this right now:

@anvil.server.callable
def read_cache_last(season):
  print "read_cache_last"
  print datetime.datetime.now()
  try:
    first_row = app_tables.zoo_sessions_log.search(tables.order_by('zsl_session', ascending=False), zsl_season=season)[0]
  except:
    return None
#  for first_row in app_tables.zoo_sessions_log.search(tables.order_by('zsl_session', ascending=False), zsl_season=season):
#    break
#  else:
#    return None
  print datetime.datetime.now()
  return first_row

Timeline

read_cache_last
2019-09-28 13:50:37.192000
2019-09-28 13:50:46.511000

Still 9 seconds

6

Are you able to share a clone link?

7

Sure, but the app is quite big… I need some time to trim the clone down… :slight_smile:
Thanks BTW, as usual, for your time and help.
Greatly appreciated.

8

Sorry, I realize that is inconvenient. If it helps, you can clone this app, and share your table with this app. Whatever is easiest for you.

https://anvil.works/build#clone:3W44KT5OBBUQW63V=NFTDLBOEJSGRM7R3JSEBIEP7

Perhaps others will have more insight as to why the query is taking a while.

9

Here it is:
https://anvil.works/build#clone:3KEZJUS4BLHGXTFD=3D3AFZ7VUCSICK75XVTORXWO

Just click “go” and watch print debug output.

10

Okay, the issue is that you have two columns in your data table where each cell is holding a very large amount of text.

There seems to be quite a lot of duplication across rows and within cells in those columns, so you should try to store that information more efficiently. Perhaps those columns are not even necessary, I’m not sure.

Anyway, that is the issue. If you remove those columns, the query runs instantly.

1 Like
11

Ok, unfortunately those columns hold the content I need to read.
I thought that as long as the column searched are efficient the query should run fast, then maybe the read could be slow.
Even if I reengineer my tables with this query retrieving an ID and, with this ID, pickup the lengthy content columns in another table, that second query will become slow just like this is now.
I will give it a try though…

What about the query on the smallest, 12 rows, table?

12

There must be a better way of storing that information. Moving it as is to another table won’t make a difference as far as I know. Perhaps storing it as a media blob (CSV/JSON) will speed things up. Either way, looking at the information, it just seems like there should be a better way.

1 Like
13

This code makes many round trips. You should try getting all you need in one trip, something like server.call('get_all_user_permissions') which will return all the permissions for the currently logged in user in one dictionary. Then the form will work with a fast local dictionary.

1 Like
14

Hi campopianoa
trying to do as you suggest, I added 2 sister columns of type Media: zsl_QIS_media and zsl_pagellini_media.
Now I am trying to populate them with the simple:

@anvil.server.callable
def convert_table():
  i = 0
  for row in app_tables.zoo_sessions_log.search():
    QIS_blobmedia = anvil.BlobMedia(content_type="text/plain", data=row['zsl_QIS'])
    pagellini_blobmedia = anvil.BlobMedia(content_type="text/plain", data=row['zsl_pagellini'])
    row.update(zsl_QIS_media=QIS_blobmedia, zsl_pagellini_media=pagellini_blobmedia)
    i += 1
    print i
  return

(debug prints are just to see it progress)
BUT I get this error at the QIS_blobmedia = anvil.BlobMedia(...) call:

TypeError: __init__() got an unexpected keyword argument 'data'

Docs state this constructor:

BlobMedia(content_type, data, [name=None])

so I really can’t understand where I am wrong now.

Next step would be delete the old “text” columns, rename the new ones and test again query’s speed.

I updated my clonable app, you can clone it again if you want to test it, and click on “TRY CONVERT” button.

Thanks and BR.

15

Hi Stefano
yeah I know,
I just found useful at code-time to write statements like:

  self.btn_recalc_games.visible = anvil.server.call(‘can_use’,user[‘permissions’],‘btn_recalc_games’)

I thought the time of round trip + query a 12 records table would be instant-like.
I was wrong.
Using your suggestion, the server call will return all the permissions table as a dicitonary, since all user permissions are represented by an INT, and I’ll have to migrate my can_use function on the client, passing to it both the dictionary and the user permissions INT.
I’ll try that.

Thanks

16

Hello,

If you specify them as positional arguments (rather than using the keywords) it seems to work.

Also, since those text cells contain so much data, the server will time out during your conversion, so you’ll have to process in chunks.

The good news is that once things are converted, you’ll find that you can return your data very quickly.

Here is a clone to demonstrate:

A side note: if you need to keep track of integers within a loop in Python, consider trying the enumerate function rather than the i=0 → i+=1 pattern.

1 Like
17

Oh I see… many thanks man.
And thanks for the enumerate hint too.

I followed the code sample in the docs:

my_media = anvil.BlobMedia(content_type="text/plain", data=file_contents, name="hello.txt")

and switched off my brain :wink:

From your cloned example:

read_cache_last
2019-09-29 22:50:21.100000
2019-09-29 22:50:21.487000

Whoooaaahhh SPPEEEEEDDDD!!!

I can’t wait to try that tomorrow. :star_struck:

I’ll post back as soon as these changes are in place.

1 Like
18

I’m not sure why it only works with the positional arguments given the documentation.

@shaun Just letting you know that keyword args (for content type and data) do not seem to be allowed when making Blob Media, despite instructions in the documentation.

19

Thanks @alcampopiano, this is an error in the documentation. It should be

my_media = anvil.BlobMedia(
  content_type="text/plain",
  content=file_contents,
  name="hello.txt"
)

That is, content rather than data. That’ll be fixed when we next deploy.

20

This topic is bringing me mad.
I feel stupid. :unamused:
Can anyone tell why my function convert_table(): updates column QIS_media but not the other column pagellini_media? (Nor it does with starpvda_media and commento_sanpa_media on the row those fields are not None.

The code looks pretty straightforward.
https://anvil.works/build#clone:3KEZJUS4BLHGXTFD=3D3AFZ7VUCSICK75XVTORXWO