Complex Search Eating Up Workflow

fatcatfreight · 2025-02-12T00:29:21.987Z

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)

ihsanzainal84 · 2025-02-12T01:09:28.598Z

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.

mitchbaylis · 2025-02-12T02:11:03.400Z

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

msgiblin · 2025-02-12T02:31:37.358Z

mitchbaylis:

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.

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.

mitchbaylis · 2025-02-12T02:45:31.829Z

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

fatcatfreight · 2025-02-12T03:52:19.039Z

Here is a look at our database:

image548×461 24.7 KB

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.

fatcatfreight · 2025-02-12T14:38:44.517Z

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:

image348×164 2.98 KB

fatcatfreight · 2025-02-12T17:58:14.773Z

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.