Optimizing search with multiple linked rows

Anvil Q&A
6

What’s your current version of the code look like?

With accelerated tables enabled, all the linked rows are fetched during the search, so searches themselves might take longer than without accelerated tables. You might time your current version of the search with and without accelerated tables to see if that’s affecting the search time.

If accelerated tables searches are longer than non-accelerated tables, you could (assuming there isn’t something else obvious with your current code):

  1. Use q.fetch_only to not fetch all the linked fields (assuming you don’t need those linked fields for that query)

  2. Change your db structure to avoid as many linked fields

Denormalizing your db structure to optimize queries isn’t specific to Anvil. Typically you might end up duplicating some data and working hard to make sure your code keeps all the duplicate values in sync. I’d put that off until you’ve exhausted all the other possibilities, though.

Also on the db structure front, you don’t say how many fields each data table has, but large numbers of fields can slow down searches (at least pre-accelerated tables, I don’t know about after). You can reduce the number of fields by putting some fields in a simple object column (typically ones you don’t need to search on). Again, I wouldn’t go that route until it’s absolutely necessary.

1 Like