1

I have a complex SQL query that joins several tables and calls some function for some data calculation. Even, I've turned it into a View in order to build a comfortable JPA implementation to obtain the data. The base SQL query takes 2 seconds when it runs on the Database. However, it spends 80 seconds when it is performed from a Spring Boot application.

I did the test in several ways and I got the same result:

  • By building a native query object from JPA entityManager
    Query typedQuery = entityManager.createNativeQuery(sql, Result.class);
  • By creating a mapped entity to the View and querying using CriteriaSpecification
    CriteriaBuilder builder = entityManager.getCriteriaBuilder();
    CriteriaQuery<ViewResult> query = builder.createQuery(ViewResult.class);
    Root<ViewResult> r = query.from(ViewResult.class);

    Predicate predicate = builder.conjunction();
    ...

    query.where(predicate);
    TypedQuery<ViewResult> typedQuery = entityManager.createQuery(query);
    return typedQuery.getResultList();
  • By using JDBC and Resulset implementation (iterating the resultset for building the desired list)

Is there a technique to achieve the results with a time close to that which is delayed when running in a database manager?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Leandro Roura Sixto
  • 672
  • 7
  • 21
  • 3
    you can use a stored procedure to perform complex database tasks. Stored procedures are pre-compiled. It reduces the execution time. – pratik patel Jan 27 '20 at 09:09
  • How many records are returned and what do you do with the result in the Java app? – Simon Martinelli Jan 27 '20 at 09:21
  • 1
    For this case it returns 3600 records. For no more 200 elements, request takes no more 2 seconds, – Leandro Roura Sixto Jan 27 '20 at 09:38
  • For starters you are comparing apples and oranges. Plain SQL execution isn't the same as JPA as that does JPQL -> SQL -> Create objects. It does a lot more. So comparing all that (for all 3 of your solutions) isn't a fair comparison. So without knowing what you exactly do (which datasource being used etc) it is impossible to give you a satisfying answer. – M. Deinum Jan 27 '20 at 09:57
  • What is the database you are using? What is the network latency from application to database? What is the [fetch size](https://stackoverflow.com/questions/1318354/what-does-statement-setfetchsizensize-method-really-do-in-sql-server-jdbc-driv)? – Karol Dowbecki Jan 27 '20 at 10:08
  • 1
    Database is Oracle. Database is locate in local network. fect-size is JPA default – Leandro Roura Sixto Jan 27 '20 at 11:04
  • 1
    I'd use SQL trace with [tkprof](https://www.orafaq.com/wiki/TKProf) to see where the database is spending time running the query revived from the application. – Karol Dowbecki Jan 27 '20 at 16:07
  • I traced with Spring-jpa too. According printed data in terminal query execution spent 3 seconds. However returning a list of Entity objects took 80 seconds – Leandro Roura Sixto Jan 27 '20 at 16:14
  • When I wrote an alternative for JDBC Resulset, I noticed that what it takes too long is the process of writing resulset into Entity list – Leandro Roura Sixto Jan 27 '20 at 16:16

0 Answers0