Preload and filter or multiple queries?

Questions / Help
,
1

Hey there,

I have an event table that has a has_many relation to user_events.
Currently it is being preloaded and then filtered according to attributes such as status etc.

I am wondering if instead of preloading the list and then filtering it, wouldn’t it be better to have separate queries for for the cases?

So then I could get the user_events by the event_id and the attribute I am looking for.
Instead of preloading and filtering just ask the database for the specific data I need.

What is your take on this?

Thank you

2

Might make sense to just use https://hexdocs.pm/ecto/Ecto.Query.html#join/5-expressions-examples

You can use filters on join if you follow those examples

1 Like
3

thanks,
maybe my explanation is unclear, I need all of them, I am just wondering which approach is better.
lets say a user_event can have a status of active or inactive, I want both in my response in separate lists.
is it better to have it preloaded when I am getting the event and then filter the full list or dont preload at all and get the items for the lists 1 by 1.

what if there are more possible status types? let’s say 10, does that make a difference in which approach is better?

4

It very much depends on how many resources (in this case: events) and how many filters you have. It usually is faster to keep as much calculations as possible in the DB, to reduce the amount of data you send back-and-forth between DB and the application code.

However, my general suggestion would be: Pick the one for now that is the simplest to program. If it becomes a problem (or seems like it might become a problem in the near future) in practice, profile to see what approach is faster on the data you have, and if there is a large difference.

3 Likes