I am facing performance issues with a Spring Boot Java App which gets data from a SQL Server database. A simple query like:

SELECT a, b, SUM(c)
FROM table
WHERE date = '2023-02-01' AND year = 2023
GROUP BY a, b

is executed. The result of the query is 12 rows. When I execute this query in SSMS, the result is there immediately. If the query gets executed via the Spring Boot app in a browser window or with Postman, it takes randomly 3.5 to 10 seconds to get the result.

The table contains roughly 8 million rows and 14 columns:

• 1 PK INT column
• 1 DATE column
• 2 DECIMAL(12,2)
• 10 NVARCHAR(x)

The query in the Spring Boot app is written in Native SQL annotated with @Query. All my colleagues use ASP.NET as back-end, and they are taking the same SQL Server. Their performance is great. It looks like there are configuration issues.

What I have tried so far:

1. I added sendParametersAsUnicode=false to application.properties
2. I copied the entire table: instead of NVARCHAR(x) I took VARCHAR(x) for all string-related columns.
3. I tracked the execution time with Postman: >99% of execution time falls in the category transfer time.
4. I tried to annotate NVARCHAR(x) columns to annotate with @Nationalized
5. I read about this:

   Table Class:

   @Entity
   @Table(name="Fact_Snapshots_Agg")
   public class FSAGG {
       @Id
       @GeneratedValue(strategy = GenerationType.AUTO)
       Long id;
       Date filedate;
       String jahr;
       String a;
       String b;
       String d;
       String e;
       String f;
       String g;
       String h;
       float c;
       float i;
       String j;
       String k;
   }
   plus constructors and getters and setters
   

   Resource File:

   import java.util.List;
   
   import org.springframework.beans.factory.annotation.Autowired;
   import org.springframework.web.bind.annotation.CrossOrigin;
   import org.springframework.web.bind.annotation.GetMapping;
   import org.springframework.web.bind.annotation.PathVariable;
   import org.springframework.web.bind.annotation.RequestMapping;
   import org.springframework.web.bind.annotation.RestController;
   
   import com.analytics_test.model.FSAGGTInterface;
   import com.analytics_test.service.FSAGGService;
   
   @RestController
   @CrossOrigin
   @RequestMapping("/FSAGG")
   public class FSAGGResource {
       private final FSAGGService fsaggService;
       
       @Autowired
       public FSAGGResource(FSAGGService fsaggService) { this.fsaggService = fsaggService; }
       
       @GetMapping("/Actuals/Total/{jahr}/gesamt")
       public List<FSAGGTInterface> getActualsTotalGesamt(@PathVariable("jahr") String jahr) { return fsaggService.getActualsTotalGesamt(jahr); }
   
   }
   

   Repository File:

   package com.analytics_test.repository;
   
   import java.util.List;
   
   import org.springframework.data.jpa.repository.JpaRepository;
   import org.springframework.data.jpa.repository.Query;
   import org.springframework.data.repository.query.Param;
   
   import com.analytics_test.model.FSAGG;
   import com.analytics_test.model.FSAGGTInterface;
   
   public interface FSAGGRepository extends JpaRepository<FSAGG, Long> {
       
       @Query(value = "a as a, b as b, SELECT SUM(c) as c "
                 "FROM FSAGG WHERE filedate = '2023-02-01' AND year = :year "
                 "GROUP BY a, b"
               )
       List<FSAGGTInterface> getActualsTotalGesamt(@Param("year") String year);
       
   }
   

   Service Class:

   package com.analytics_test.service;
   
   import java.util.List;
   
   import org.springframework.beans.factory.annotation.Autowired;
   import org.springframework.stereotype.Component;
   import org.springframework.stereotype.Service;
   
   import com.analytics_test.model.FSAGGTInterface;
   import com.analytics_test.repository.FSAGGRepository;
   
   @Component
   @Service
   public class FSAGGService {
   
       private final FSAGGRepository fsaggRepository;
       
       @Autowired
       public FSAGGService(FSAGGRepository fsaggRepository) { this.fsaggRepository = fsaggRepository; }
       
       public List<FSAGGTInterface> getStuff() { return fsaggRepository.getStuff(); }
       
       public List<FSAGGTInterface> getActualsTotalGesamt(String jahr) { return fsaggRepository.getActualsTotalGesamt(jahr); }
   
   }
   

   CodePudding user response：

   Rewrite the service method getActualsTotalGesamt to confirm that the slow query is the issue rather than something else in spring boot.

   public List<FSAGGTInterface> getActualsTotalGesamt(String jahr) {
       Date start = new Date();
       List<FSAGGTInterface> retVal = fsaggRepository.getActualsTotalGesamt(jahr);
       System.out.println(“repo call duration ms: “   ((new Date()).getTime() - start.getTime()));
       return retVal;
        }
   

   CodePudding user response：

   set spring.jpa.show-sql=true and show us the generated request