I have been working on getting this search functionality done all day today and I finally got it working but it is eating up workflow and is pretty slow.
Search: Enter Inputs press a button, load page with URL params.
On Page Load:
Images above walk through step by step, this solution takes about a second to load 2 results on first page load, this search is going to be used to display thousands of records, so if 2 take a second, I would hate to see 1000.
Please let me know of anyways to optimize this without transferring my entire database to supabase (heavily considering it)
Take note of how :filtered works 99% of the time. A filter operator is applied after all matches to your search constraints are loaded.
So for examplea Search will load 1000 matches (matching search constraints) and then run the filters to those 1000 matches.
So what happens is you are incurring WU from the search constraints returning a lot nore data than you want, and then incurring extra clientside processing, filtering those 1000.
In your case, you have multiple searches with filters that then also need to check for intersects. Hence the heavy WU and delay from clientside processing.
Beforr shifting to an external database try see if you can do every search without filters. This might involve reworking your DB setup.
the intersects will be very taxing to do
the reason you need to search and intersect and filter so much is a database structuring issue more than anything
basically you should have a child data to hold the data so it is more efficient
a simple example:
company has a list of contacts
could store as list on company but then you’d need to store roles on contacts and do
companys contacts : filtered to this contacts role = ceo
or similar to what you’re doing:
search for companies contacts intersected with search of contacts filtered to role
that’s very inefficient way to do it
the most efficient way would be a child data to hold that data instead
“company contact”
fields: company, contact, role
now on the page you can just do search for company contact
anytime I see “intersect” in an app it’s usually a database structuring issue. Intersect is fine on SMALL lists but it is terrible on big lists and it is even more terrible when combined with filters after the intersect.
you’re basically saying - here’s a list of 100,000 things, match them to 1000 things and also these other 1000 things and also filter to these things…
vs
search once
1 Like
I do have a decent use-case for an Intersect! I have a search filter available on my cocktail recipe site, where users set what’s in their bar inventory, and then can filter cocktail results only by cocktails that contain all ingredients they have in their bar - and it works quite nicely. I recognize that avoiding intersects in most use-cases is important, but this is one area that I couldn’t get around. By using intersect: Count, it also allows me to finesse search results, so in the future I could allow a user to show cocktails where they’re missing only 1 ingredient, for example.
that’s likely a small list so intersect would be fine in that case and I think intersect is the right solution there
intersect tends to be very bad for large lists and very bad for multiple intersects joined
1 Like
Here is a look at our database:
I have been working for a while on reworking to add new features but its obviously added more complexity to the search.
Tender holds access to Stops and Financial
Stop holds access to Facility’s Commodities, additional number and requirements.
If you have any tips on I can further optimize, it would be greatly appreciated.
Alright, let me know what you think.
I updated my database to hold Origin & Destination, Stop & Facility directly in the Tender itself. then I can do one search directly in the tender. However, I still have to do a search for matches within the constraint, See below images.
Database Additions:
Workflow Changes:
Solved/Moving on
I transitioned to holding the first stops data directly in the Tender itself, this elements all extra search’s
I am not a fan of having data repeated but this is the solution I am going with for now.
Thank you all for the help.
