Click here to Skip to main content
15,922,325 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
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.
Posted
Updated 21-Mar-23 4:13am

1 solution

It takes forever because you're looking for a couple of strings, "Disabled" and "Opted Out", probably without any indexing on the field. This is probably the worst way to get the status in terms of performance. If you were looking for integer values for those states, like an integer value (0, 1, 2, 3, ...), the query would go a LOT faster. Database engines are typically not very performant at string operations, and looking for strings falls into that category.

Proper indexing would also help.
 
Share this answer
 
Comments
Office Systems 21-Mar-23 10:19am    
Thanks so much @Dave Kreskowiak for your response, how can I index my status field which has string values so as I can improve performance?
Dave Kreskowiak 21-Mar-23 10:20am    
I couldn't tell you. I don't do SpringBoot nor MongoDB.
Office Systems 21-Mar-23 10:24am    
Thanks Dave, I appreciate.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900