Query Large Collections

Ilyas_Ziyaoglu · 2024-04-27T06:35:49.505Z

Hi All! I have a participateTransaction collection. There is campaign participation transactions with given voucher codes. There is 22 milion users in the platform. The total count of records in the collection increased to 67 milion in one year. In admin panel, there is a page that provide filter and search functionalities to admin users. I am using Java, SpringBoot, MongoRepository for db operations. I am using indexing and pagination already but the query time takes about 4-6 minutes. I want to decrease this time to 30 seconds at least. I applied archive solution for older than one year records, because the participants can use the voucher codes over one year than participation. So I have to store the data in the participateTransaction collection for one year at least.

Is there a vertical solution that you think can decrease query time to 30 seconds at least ?

Joel_Lord · 2024-05-23T12:02:15.518Z

Hi Ilyas, and welcome to the MongoDB forums!

Without taking a look at your data schema and indexes, it’s really hard to give you an exact answer. There are some things that you can try, though.

The first step would be to try to better understand why the query is slow. You can find more about your query performance by using an explain. I’ve found that this docs page can be very useful in understanding the explain results.

If you’re using MongoDB Atlas, or MongoDB OpsManager, you could also try to take a look at the MongoDB Performance Advisor that can provide you with tips on how to increase the general performance of your cluster.

Often times, slow queries can be fixed with indexing. If you are using MongoDB Atlas, you can take a look at this tutorial for more tips and tricks about how to optimize query performance.

Finally, performance issues can sometimes be dealt by optimizing your data schema with some of the data modeling patterns. If you have many $lookup stages or computation in your queries, consider using patterns such as the Extended Reference pattern, or the Computed pattern. You can find great courses on data modeling in MongoDB University.

I hope this will help you find the best solution for your use case!
-Joel