I'm having a spring boot project which looks for more than 50.0M documents in MongoDB when fetching from a single table everything works fine however the problem I'm experiencing is when joining two tables in order to check for customer status in another table the execution time takes up to 10 minutes which is very disgusting here is the code I have.
What I have tried:
Controller Class
@RestController
@RequestMapping("/customer_api")
public class CustomerApi{
@Autowired
private CustomerService customerService;
@GetMapping("/customer/{year}/{month}")
protected int getCustomerByStatus(@PathVariable int year, @PathVariable int month) {
return customerService.getCustomerByStatus(year,month);
}
}
Service Class
@Service
public class CustomerService {
@Autowired
private MongoTemplate mongoTemplate;
public int getCustomerByStatus(int year, int month) {
YearMonth yearMonth_ = YearMonth.of(year, month);
LocalDate startDate = yearMonth_.atDay(1);
LocalDate endDate = yearMonth_.atEndOfMonth();
LookupOperation checkStatus = LookupOperation.newLookup().from("tbl_status") .localField("customerId").foreignField("customerId").as("customerStatus");
AggregationOperation matchData = Aggregation.match(Criteria.where("customerCode").is("1")
.andOperator(Criteria.where("orderDate").gte(startDate.toString()), Criteria.where("orderDate").lte(endDate.toString()),
Criteria.where("customerStatus.status").ne("Disabled"), Criteria.where("customerStatus.status").ne("Opted Out")));
Aggregation aggregation = Aggregation.newAggregation(checkStatus, matchData);
var data = mongoTemplate.aggregate(aggregation, "tbl_customers", Customer.class).getMappedResults().size();
return data;
}
}
Table Customer
@Data
@AllArgsConstructor
@NoArgsConstructor
@Document(collection = "tbl_customers")
public class Customer {
@Id
private String _id;
private String customerId;
private String region;
private String council;
private String country;
private String status;
private String customerCode;
private LocalDateTime fileCreation;
private String registeredBy;
}
Table Status
@Data
@AllArgsConstructor
@NoArgsConstructor
@Document(collection = "tbl_status")
public class DataFile {
@Id
private String _id;
private String orderDetails;
private String orderNumber;
private String orderLocation;
private String quantity;
private String customerId;
private LocalDateTime orderDate;
}
Any suggestion will be much appreciated.