Optimizing search with multiple linked rows

Anvil Q&A
7

My code currently looks like this:

Client:

  def dd_change(self, **event_args):
    tl = TimerLogger("Dropdown filter timer")
    tl.start('Client request send')
    self.rp_locations.items = anvil.server.call(
      'filter_by_country_or_status', 
      (
        self.dd_country.selected_value, 
        self.dd_status_filter.selected_value
      )
    )
    tl.end('Client request completed')

Server:

@anvil.server.callable('filter_by_country_or_status', require_user=True)
def filter_by_country_or_status(filter_tuple: tuple=None):
  result = None
  tl = TimerLogger('Server timer')
  tl.start('Server start')
  if not any(filter_tuple):
    try:
      filter_tuple = load_filters()
    except:
      return result
  else:
    store_filters(filter_tuple)
  tl.check('Server cookie check done')
    
  if filter_tuple[0] and filter_tuple[1]:
    customers = app_tables.customer.search(status=filter_tuple[1])
    tl.check('Server: Customers fetched.')
    result = app_tables.location.search(country=filter_tuple[0], customer=q.any_of(*customers))
    tl.check('Server: Locations fetched.')
    
  elif not filter_tuple[0] and filter_tuple[1]:
    # Status chosen
    result = [c['location'] for c in app_tables.customer.search(q.fetch_only('location'), status=filter_tuple[1])]
  elif filter_tuple[0] and not filter_tuple[1]:
    # Country chosen
    result = app_tables.location.search(country=filter_tuple[0])
  else:
    result = app_tables.location.search()
  tl.end('Server end')
  return result

Output from timers

Accelerated Tables enabled:

20:11:21 | Dropdown filter timer: ( 0.000 secs) | Client request send
18:11:22 | Server start: ( 0.000 secs) | start
18:11:22 | Server start: ( 0.029 secs) | Server cookie check done
18:11:22 | Server start: ( 0.332 secs) | Server: Customers fetched.
18:11:26 | Server start: ( 4.661 secs) | Server: Locations fetched.
18:11:26 | Server start: ( 4.661 secs) | Server end
20:11:27 | Dropdown filter timer: ( 6.265 secs) | Client request completed

Accelerated Tables disabled:

20:15:43 | Dropdown filter timer: ( 0.000 secs) | Client request send
18:15:44 | Server timer: ( 0.000 secs) | Server start
18:15:44 | Server timer: ( 0.045 secs) | Server cookie check done
18:15:45 | Server timer: ( 1.373 secs) | Server: Customers fetched.
18:16:02 | Server timer: (18.262 secs) | Server: Locations fetched.
18:16:02 | Server timer: (18.263 secs) | Server end
20:16:03 | Dropdown filter timer: (19.738 secs) | Client request completed

So Accelerated Tables are definitly doing wonders!

In regards to number of fields in each table, it looks like this:

  • Customer (2548 rows)
    • 6 x Text
    • 2 x Linked:
      • Location (1-to-1)
      • Status (1-to-many)
  • Location (2547 rows)
    • 1 x Text
    • 2 x Number
    • 2 x Linked
      • Customer (1-to-1)
      • Country (1-to-many)
  • Status (10 rows)
    • 3 x Text
    • 1 x Media (32x32 pixels)
  • Country (12 rows)
    • 1 x Text

When displaying the query results (from Location table) in the DataGrid, a “name” column from the Customer table is displayed, and a Status-widget is constructed from the “Status” table, linked from the Customer.
So I am displaying data from a “double-link”:
Location ← Customer ← Status

Does the result that is returned from the server-code, fetch the linked data at the time of the query, or does it do multiple roundtrips, after it reached the client?