The speed of receiving data through ORM, how faster?

Question

ORM

There is a fairly large project on Kohana3. The ORM is used by Jam . But this is not so important.
The site has various lists with pagination filters, which require 5 or more associations with other tables to generate (everything is configured at the ORM level). The whole thing is very conveniently done and works well with CMF tools. Naturally, the lists are obtained by something like findAll (), then the result is sorted in the loop, while in the loop, the associations are accessed and each element of the list is generated. Thanks to lazy load, we end up with a request for each data access. And in the end, instead of one large request, we have hundreds of small ones, which is much slower in terms of page generation speed.
In general, it seems that such complex queries (with associations) cannot be executed by ORM tools in one query. Correct me if I'm wrong. You can use an example on other ORMs, anyway, I think that they all work approximately the same way.
So, the issue of optimizing the speed of page generation is acute. I see several solutions:
1) Write large and thick SQL queries with your hands. Pros: they will run faster; less load on the database. Cons: it will take a lot of time, because. I don't know SQL very well; it looks bad in code; this is not very convenient in the presence of filtering and pagination.
2) Use Redis. Pros: again, it's fast; there is almost no load on the database; there are sets that can be combined for use in filtering; it seems like sets can be used for pagination. Cons: I only heard/read about redis; you need to understand how to update the data in it when updating the database, and it will be updated often.
3) Stupidly cache (Memcache) all lists, including all filters and pages. But this is somehow too much in the forehead.
What approach is the most optimal in terms of labor costs / result? Maybe there is another option?
PS: I personally liked option 2 more. In this regard, the question, if someone agrees with me: how best to implement pagination? To store each page separately, or to receive in parts from the general list of objects? Sorry if the question is not correct, I repeat, I never touched Redis.

Answer 1

In general, all options should be used.
Those requests, whose results do not require real-time verification, can be cached. For difficult situations, you can write by hand. I also recommend looking into how greedy fetching is done in your ORM. This feature allows most (not all, but very many) requests not to be pulled one at a time, but to be combined into one big one.
There is no silver bullet here. Take a profiler, find the slowest places and think about which of these approaches fits the best here. Implement it. And so on down the list.