❔ How to optimize a EF Core query with too many joins?
I have a specific endpoint that basically does 12 different joins. It is a central piece of the code that has to filter many different joined tables. I am using split query to prevent Cartesian Explosion from happening. All tables are perfectly indexed. The queries are super fast, but since we have 12 of them when splitting, it ends up taking 2 to 3 seconds to return the result. I was wondering if anyone has ever faced this issue and if anyone has any suggestion on a approach to fix this issue.
A few ideas I had:
- Some of these joins are not many to many, so I was thinking about a view that would pre-join these tables to reduce the number of joins - this would definitely work, but honestly would implement a drastic change on my database and query structure. I was trying to avoid it.
- I was wondering if there was a way to tell Split Query to only split many to many joins, since some of those are many to 1 and wouldn't cause a cartesian explosion. This way I could maybe reduce the number of queries to half and have it run twice as fast.
- Run separate queries entirely and pass ids around on subsequent filters - I honestly find it hard to believe this would have any benefit. I would have to load a lot of IDs in memory and then build other big queries with them to incrementally get to the result I need.
Thanks!
2 Replies
This sounds like a job for raw sql and a cte, if you can do that in ef
Was this issue resolved? If so, run
/close - otherwise I will mark this as stale and this post will be archived until there is new activity.