0

I am using Spring Data on top of Hibernate. I have an abstract class A and some implementations, two of which are associated to a large number of records in the database, let's say 200k. I am using a TABLE_PER_CLASS inheritance strategy. Class A has relationship with another class C having two double numbers, d1 and d2. Now, I want to execute a query like findByCD1BetweenAndCD2Between(double minD1, double maxD1, double minD2, double maxD2):

@Repository
public interface ARepository extends
    JpaRepository<A, Long>,
    JpaSpecificationExecutor<A> {

      findByCD1BetweenAndCD2Between(double minD1, double maxD1, double minD2, double maxD2):
}

My class C is embedded and an index is defined:

@Entity
@Configurable
@Embeddable
@Table(indexes = { @Index(columnList = "d1,d2") })
public class C {
    private double d1;
    private double d2:
}

Class A embeds it:

@Entity
@Inheritance(strategy = InheritanceType.TABLE_PER_CLASS)
@Configurable
public class C {
    @Embedded
    private Coordinates coordinates;
/*...*/
}

When I do this, the query really takes several minutes. Executing the query directly in SQL does not take that long, as it is a matter of seconds. The number of records matching the condition is in the order of 100. How can I optimize this situation in order to reduce the computation to a few seconds?

Manu
  • 4,019
  • 8
  • 50
  • 94
  • Edit to post the "EXPLAIN" of the sql request spring-data emits (1. logs requests, 2. run `EXPLAIN` on it), my wild guess is you're missing an index on the columns used for the between –  Oct 28 '14 at 06:51
  • @RC. How do I do that? – Manu Oct 28 '14 at 07:00
  • And that is slow because? If there are a lot of records that also means a lot of instances of objects ahve to be made. In general the time it takes to create the objects is larger than the execution time of the query, especially if the objects have non-lazy relations. – M. Deinum Oct 28 '14 at 07:05
  • @M.Deinum I added some information to the post. – Manu Oct 28 '14 at 07:06
  • That still doesn't tell anything, it greatly varies if there is 1, 10 or 10000000 objects to create. Also your objects are `@Configurable` meaning that they are dependency injected with Spring, which also takes time. So again I doubt the problem is the query but the amount of objects that need to be created and what those objects do (i.e. `@Configurable`). – M. Deinum Oct 28 '14 at 07:08
  • @Manu: http://dev.mysql.com/doc/refman/5.0/en/using-explain.html and http://stackoverflow.com/a/6428598/180100 –  Oct 28 '14 at 08:10
  • @M.Deinum Added information on the result set, which in the order of 100. – Manu Oct 28 '14 at 08:58
  • 100 rows can result in 100 sub selects or even more depending on your object model, as mentioned you are also using `@Configurable` and, assuming you have set it up correctly, each bean is checked and injected by Spring. 100 rows can result in thousands of objects. You need to profile your application to pinpoint the problem use a profiler for that. Just trying to fix things isn't going to work, or you must be lucky... – M. Deinum Oct 28 '14 at 10:06

0 Answers0