Assistance Needed for Drupal 9 Custom Module Development: Having Performance and Database Queries Problems

Hello Everyone,

I've encountered some difficulties with database queries and overall module performance while working on a custom module for a Drupal 9 website. I'm hoping that someone here can help me fix these problems by offering some advice or best practices.

Here is some background information about my work: Content display is dependent on a number of user-selected filters, and the module is made to retrieve and present content from multiple tables in the Drupal database. But I'm seeing serious problems with performance. As the database gets bigger, some of the queries are running far more slowly than I had expected. The performance is still lagging despite my best efforts to optimize them, and I have a suspicion that the current strategy is not scalable.

The details I'm dealing with are as follows:

Complex Joins: I'm wondering if there's a more effective way to handle joining multiple tables in Drupal, as some of my queries call for it.
Caching Strategies: I've read about Drupal's caching options, but I'm not sure which caching layer—or layers—would be most effective for regularly updated data.
Query Debugging: Although I am aware of the Devel module for performance testing, I would appreciate any extra advice on how to troubleshoot sluggish queries.

I would be very grateful if someone could suggest modules, code snippets, or best practices to optimize custom queries and boost module efficiency, or if they have experience with similar issues. Any recommendations for caching setups that could improve the efficiency of these queries are also welcome.

Thanks in advance for your help and support.

Regards
DaisyGcp