Click here to Skip to main content
15,900,907 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
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.
Posted
Updated 4-Jul-23 22:05pm
Comments
[no name] 26-Jun-23 12:10pm    
How many records does your query return? If it's more than a "few pages", ask yourself why.
Office Systems 26-Jun-23 12:49pm    
The query returns more than 4 million rows @Gerry Schmitz
Richard MacCutchan 26-Jun-23 14:22pm    
Well that is why it takes so much time. Do you really think a user can handle that much datsa?
Office Systems 26-Jun-23 14:37pm    
I just want to count the results I can't return such data to the user there is no means I can count these values depending on these conditions provided @Richard MacCutchan?

1 solution

Quote:
I just want to count the results I can't return such data to the user there is no means I can count these values depending on these conditions provided
Your code is loading all 4 million matching records into a list, simply to return the size of that list.

Change your query to select the COUNT of the records, and return that instead. I'm not familiar with Spring Boot, but something like this should work:
Java
@Repository
public interface ClientRepository extends JpaRepository<Client, String> {
    @Query("SELECT Count(1) FROM (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'))")
    int countClientOrder(LocalDateTime startDate, LocalDateTime endDate);
}
 
Share this answer
 

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