How to optimize performance on returning a list from a search

Anvil Q&A
1

Hey guys,

So I’m trying to populate an anvil_extras autocomplete suggestions list from values stored in my datatable.

This is the server code I have so far. This is called at the start of the form and is passed directly into the suggestions property of the autocomplete.
It works fine but my problem is that it runs too slow on initialization of the form so I was wondering if there’s a better way to do this?

def get_caps_list():
  suggestions = []
  for r in app_tables.capabilities.search():
    suggestions.append(r['company'])
    suggestions.append(r['vendor'])
    suggestions.append(r['solution_category'])
    suggestions.append(r['solution'])
    suggestions.append(r['vendor'] + " " + r['solution_category'])
  final = list(set(suggestions))
  final.sort()
  return final

One thought I had after reading other posts on here is to read the entire datatable into a dictionary at the start of the app and then just pass that dict around to be used as needed. If you think that would work could you please give me some guidance on how that would look, specifically where that dictionary should be declared for best practice and how to populate that dict? Otherwise any advice is greatly appreciated.

Thank you in advance!!!

2

The most important thing is to minimize the number of round trips.

If you are calling other server functions for other reasons, make one server function that calls all those functions and return one dictionary with all the values returned by those functions. The rule that I strictly follow is to make only one server call per user interaction. When the user clicks on something, I make one server call that collects all I need to collect. And in cases like this, I try to call it once per session, instead of once per form, and store it in a global variable defined in the Globals module.

Instead of doing this:

# on the server
@anvil.server.callable
def get_caps_list_1():
    [...]
    return list

@anvil.server.callable
def get_caps_list_2():
    [...]
    return list

# on the client
list1 = anvil.server.call('get_caps_list_1')
list2 = anvil.server.call('get_caps_list_2')

Do this:

# on the server
@anvil.server.callable
def get_caps_lists():
    return {
        'list_1': get_caps_list_1(),
        'list_2': get_caps_list_2(),
    }

def get_caps_list_1():
    [...]
    return list

def get_caps_list_2():
    [...]
    return list

# on the client
list1, list2 = anvil.server.call('get_caps_lists')

Another little improvement, negligible if you are reading just a few rows, is to make sure you have Accelerated Tables enabled:
image

1 Like
3

Some suggestions:

  1. make you sure you have checked accelerated tables Anvil Docs | Accelerated Tables Beta
  2. only fetch the columns your are actually using:
from anvil.tables import query as q
for r in app_tables.capabilities.search(q.fetch_only('company','vendor','solution_category','solution')):
  1. make sure you call get_caps_list() only once (in the initialisation of the form)
1 Like
4

That’s a good technique if the data is relatively static (e.g. unlikely to change during the run of the app). I have a client side Cache module that holds the results of various server calls for later use.

2 Likes
5

Thank you, this is great, I will definitely change the structure of my app accordingly. I have one follow up question if you have time.

So one of the functions of my app is to allow the user to search the datatable, in this search I use ilike and full_text_match queries. I was wondering if there’s a way to mimic this but on a dictionary instead. I was thinking of a dictionary containing all values from my table capabilities.
The code for that search function is below:

#on the server
def search_capability(search_term):
  
  search_terms = search_term.split()
  search_terms = ["%{}%".format(term) for term in search_terms]
  query_list = [q.ilike(term) for term in search_terms] + [q.full_text_match(term) for term in search_terms]
  result = app_tables.capabilities.search(
    q.any_of(
      company=q.all_of(*query_list),
      solution_category=q.any_of(*query_list),
      vendor=q.any_of(*query_list),
      solution=q.any_of(*query_list)
    )
  )
  result = similarity_sort(search_terms, result)
  return result

This currently runs every time the user performs a search. I’m not entirely sure how I would translate this to work on a dictionary so I would only have to access the server once? Is there a way to perform queries on values in a dictionary? I completely understand that this is pretty much a second question so I’m going to mark your previous response as the solution. If you have time though I’d greatly appreciate your expertise.

6

Searching on a dictionary would require some code that cycles through all the elements. Just plain python. You can create a client side search_capability(search_term) function that does the job.

If the capabilities table is not too large, you could load it when the form loads as a list of dictinaries, then do the searches on the client side by iterating the list every time you need it.

If the table is too long to completely load on the form, then you will need to do a server call every time your search_term change.

If search_term changes because of an UI action, then it is usually not a problem to make one server call. I’m in the US midwest, a server call to the UK takes about half a second, and it is not a problem, as long it’s one and only one server call.

I actually often make the delay even longer: I use the search_term_change event to set a timer’s interval to 0.3 seconds, then use the tick event to do the server call. This adds 0.3 seconds to the 0.5 of the server call, but it skips calls between quick keystrokes.

1 Like