I'm trying to fetch data using Spring Boot for my REST API from PostgreSQL Database, I have more than 30 million records in my database, the API takes a lot of time to pull data from the database and sometimes it results on time out error when trying to fetch data, I don't know what I'm missing on this implementation or it's the query that takes a lot of time and if is the query how best can I optimize it so as I can fast track the pulling of large volume of data more faster from the database and get the count depending on some conditions, I joining three tables each with more than 30+ million records.
Below is my code structure / implementation
What I have tried:
// Below is my Interface Class
@Repository
public interface ClientRepository extends JpaRepository<Client, String> {
@Query("select DISTINCT c, o from Client c left join Order o on c.ClientID = o.ClientID left join Status s on c.ClientID = s.ClientID where o.Code ='2' and "
+ "o.OrderDate >=:startDate and o.OrderDate <=:endDate and s.StatusDate >=:startDate and s.StatusDate <=:endDate and (s.OrderStatus != 'Cancelled' or s.OrderStatus != 'Pending')")
List<Client> countClientOrder(LocalDateTime startDate, LocalDateTime endDate);
}
// Below is my service implementation class
@Service
public class ClientService {
@Autowired
private ClientRepository clientRepository;
public int countClientOrder(int year, int month) {
YearMonth yearMonth_ = YearMonth.of(year, month);
LocalDate original_startDate = yearMonth_.atDay(1);
LocalDate original_endDate = yearMonth_.atEndOfMonth();
LocalDateTime startDate = original_startDate.atStartOfDay();
LocalDateTime endDate = original_endDate.atStartOfDay();
var data = clientRepository.countClientOrder(startDate, endDate).size();
return data;
}
}
// Below is my Rest API
@RestController
@RequestMapping("/orders_api")
public class ClientApi {
@Autowired
private ClientService clientService;
@GetMapping("/requested_on/{year}/{month}")
protected int getClient_Orders(@PathVariable int year, @PathVariable int month) {
return clientService.countClientOrder(year,month);
}
}
// Client Model
@Data
@Entity
@AllArgsConstructor
@NoArgsConstructor
@Table(name = "tbl_clients")
public class Client {
@Id
private String ClientID;
private String ClientNames;
private String NickName;
private String Gender;
private String Country;
private String Email;
private String Phone;
@OneToMany(targetEntity = Order.class, cascade = CascadeType.ALL)
@JoinColumn(name = "ClientID", referencedColumnName = "ClientID")
private List<Order> order;
@OneToMany(targetEntity = Status.class, cascade = CascadeType.ALL)
@JoinColumn(name = "ClientID", referencedColumnName = "ClientID")
private List<Status> status;
}
// Orders Model
@Data
@Entity
@AllArgsConstructor
@NoArgsConstructor
@Table(name = "tbl_orders")
public class Order {
@Id
private String OrderId;
private LocalDateTime OrderDate;
private String OrderType;
private String Code;
private Int Quantity;
private String OrderLocation;
private String ClientID;
@JsonIgnore
@ManyToOne
@JoinColumn(name = "ClientID", insertable=false, updatable=false)
private Client client;
}
// Status Model
@Data
@Entity
@AllArgsConstructor
@NoArgsConstructor
@Table(name = "tbl_status")
public class Status {
@Id
private String StatusId;
private LocalDateTime StatusDate;
private String OrderStatus;
private String Notes;
private String ClientID;
@JsonIgnore
@ManyToOne
@JoinColumn(name = "ClientID", insertable=false, updatable=false)
private Client client;
}
Any Suggestions will be much appreciated just to speed up my rest api to pull data faster.