What I’m trying to do:
I have four tables:
- Has around 4500 rows
- Status (Linked in Customer)
- Has 10 rows
- Location (Linked in Customer)
- Has same amount of rows as Customer (4500)
- Country (Linked in Location)
- Has around 15 rows
Customer and Location are “1-to-1”, meaning a Customer have a linked Location and a Location has a link back to the Customer.
I’m sure this is a bad practice, but it has helped me speed up some other lookups I am doing elsewhere.
End goal is to populate a DataGrid (RepeatingPanel) with Locations, based on two Dropdowns: (Country and Status)
The user can select either Country, Status or both from the Dropdowns.
If only Country is selected, the query takes ~1 second.
If only Status is selected, the query takes ~0.5-5 seconds.
If both are selected, the query can take up to 10 seconds.
I am looking at ways to speed up the queries, and any pointers are welcome!
Preferably not any, that would need me to recreate the structure in my tables. Although that might be the best solution, it would take time, that I don’t have, at the moment… something something, technical debt, I know…
What I’ve tried and what’s not working:
I’ve tried different approaches, shown in the code below, but I think I might be missing something crucial.
I even tried the new and improved
q.fetch_only(), but that resulted in errors. If you think it’s the road to take, I’ll have another look at it.
def filter_by_country_or_status(filter_tuple): if filter_tuple and filter_tuple: # Country  and Status  chosen # Approach 1 customers = app_tables.customer.search(status=filter_tuple) result = [c['location'] for c in customers if c['location']['country'] == filter_tuple] # Approach 2 locations = app_tables.location.search(country=filter_tuple) result = [l for l in locations if l['customer']['status'] == filter_tuple] elif not filter_tuple and filter_tuple: # Status chosen result = [c['location'] for c in app_tables.customer.search(status=filter_tuple)] elif filter_tuple and not filter_tuple: # Country chosen result = app_tables.location.search(country=filter_tuple) else: result = app_tables.location.search() return result
My experiment with
locations = app_tables.customer.search(q.fetch_only('location'), status=filter_tuple) result = [l for l in locations if l['country'] == filter_tuple]
This results in a:
anvil.tables.NoSuchColumnError: No such column 'country'
Thank you in advance! I’ll be happy to further explain the setup.
As the tables contain customer data, I cannot provide a public clone.
Accelerated Tables are enabled.