paging

[oak-tree]

you did some nice work of comparing. but for paging. i think it does not fully reflect a real state. why? because i think in most of the paging scenarios a count query will occur as well to find out how many pages are in the table. my question is: how much impact this count query will have

[radcortez]

Hi @oak-tree, thank you for your feedback!

Yes, you are right. The count query needs to be executed for the paginated scenario. I can probably run the numbers again, but I believe the count query impact is minimal.

The idea was also to show the big differences between each approach.

[oak-tree]

np :+1: it is interesting how much impact count query has (in jpa ).

i'm looking for ways to optimize a project(web) i'm work on which uses jpa(hibernate provider). we use a lot of typesafe jpa queries as well. do you happen to know if there is a difference in performance between those two ? do you happen to know how much 2nd cache layer will help to interface performances?

[radcortez]

As long as you follow the same guidelines to improve performance (selecting only needed columns, excluding unneeded relationships, etc), using type safe jpa queries should not be any different.

2nd lvl cache can improve performance, but it's usually only useful for slow changing data. Since you're using Hibernate, you can access information about caching stuff. Have a look into the Statistics object: https://docs.jboss.org/hibernate/orm/4.0/manual/en-US/html/performance.html

[oak-tree]

thanks for the link ! i have few questions maybe you can help:

1. if for example the entity has n properties. select n-1 or n-2 will make any difference ?
2. i notice that access relation field from inside the entity causing multiple select statements

for example :

   @Entity
   Public Class Report {
      @CreatedBy
      @ManyToOne(optional = true)
      @JoinColumn(name="CREATOR_ID", referencedColumnName = "ID")
      private User createdBy;

      public String  getCreatorName(){
             return createdBy.getFirstName() + " " + createdBy.getLastName();
      }
  }

it seems that fetching this kind of code will result in multiply select statements. Moreover, even if the relation is @OneToMany(FetchType.Lazy) . there is no option to do fetch join because ( as i understand) the getter force JPA to fetch each item by it self.

have you experienced this kind of behavior ?

[radcortez]

1. Depends on the fields that you're not selecting. If they are big fields they are going to have a difference, but nothing like trying it out and measure it.
2. You can always write something like this at the query level.

entityManager.createQuery("select r from Report r left join fetch r.createdBy").getResultList();

This should load everything you need in a single query.