3

I have a java application running on a Windows server. This application queries an Oracle 10 database through Hibernate. I have a query getting back a collection of 215 entities with 6 string fields each . It takes around 20s to get them back (stat gotten from hibernate stats)

I got back sql generated by hibernate and ran it through SQLPlus, got between 300-500ms run time.

I launched H2 console with ojdbc driver (the same used by my app) and connected to my db (from the same computer as my app run to avoid network problems), ran the sql I got from Hibernate several times, I got the same 300-500ms run time as from SQLPlus so I guess my problem comes from Hibernate but I don't know where to look anymore.

The database is on another server but I got <1ms ping to that server so I don't think network is involved.

My configuration :

javax.persistence.jdbc.driver=oracle.jdbc.driver.OracleDriver
javax.persistence.jdbc.url=jdbc:oracle:thin:@xxxxx
javax.persistence.jdbc.user=xxxxx
javax.persistence.jdbc.password=xxxxx
hibernate.ejb.naming_strategy=org.nuiton.jpa.hibernate.OracleCompliantImprovedNamingStrategy
hibernate.dialect=xxxx.MyDialect

hibernate.hbm2ddl.auto=update
hibernate.show_sql=false
hibernate.use_sql_comments=false
hibernate.format_sql=true
hibernate.ejb.naming_strategy=org.hibernate.cfg.ImprovedNamingStrategy

hibernate.c3p0.min_size=3
hibernate.c3p0.max_size=5
hibernate.c3p0.timeout=1800
hibernate.c3p0.max_statements=50

hibernate.generate_statistics = false

Naming Strategy : https://gitlab.nuiton.org/retired/nuiton-jpa/blob/develop/nuiton-jpa-hibernate/src/main/java/org/nuiton/jpa/hibernate/OracleCompliantImprovedNamingStrategy.java

Dialect :

public class MyDialect extends Oracle10gDialect {

    public MyDialect() {
        super();
        registerColumnType(Types.DOUBLE, "number");
    }

}

My entities look like (with getters and setters) :

public abstract class AbstractJpaRequestedArticle extends AbstractJpaEntity implements Serializable {

    private static final long serialVersionUID = 7293079560062973232L;

    public static final String PROPERTY_ID = "id";

    public static final String PROPERTY_QUANTITY = "quantity";

    public static final String PROPERTY_PRIORITY = "priority";

    public static final String PROPERTY_ARTICLE = "article";

    public static final String PROPERTY_REQUESTED_LIST = "requestedList";

    public static final String PROPERTY_DESTINATION_LOCATION = "destinationLocation";

    @Id
    protected String id;

    protected double quantity;

    protected String priority;

    @ManyToOne
    protected Article article;

    @OneToOne
    protected RequestedList requestedList;

    @ManyToOne
    protected Location destinationLocation;

    @PrePersist
    public void prePersist() {
        if (this.id == null) {
            this.id = new JpaEntityIdFactoryResolver().newId(this);
        }
    }

    @Override
    public String getId() {
        return id;
    }

AbstractJpaEntity only defines equals, hashcode and default toString

Jean Couteau
  • 31
  • 7
  • Could be some bad mapping or bad configuration (which I think it is). Would help to have your hibernate config – XtremeBaumer May 29 '19 at 08:55
  • Can you try setting the logging configurations for hibernate using Log4j. That will give a better picture of what is happening behind the scenes. Also it might be the case that the first query might be taking a lot of time than the subsequent queries – Chaitanya May 29 '19 at 08:56
  • Interesting, could you please share the configuration of hibernate? – Mustahsan May 29 '19 at 09:11
  • edited the question with hibernate configuration – Jean Couteau May 29 '19 at 09:14
  • @ChaitanyaWaikar which log level should I put on Hibernate ? – Jean Couteau May 29 '19 at 09:16
  • Please refer https://stackoverflow.com/a/6609980/7803797 – Chaitanya May 29 '19 at 09:18
  • 1
    try setting `hibernate.jdbc.fetch_size` to 100-500 to see if it improves. as I understand if query works well in oracle only issue could be fetching the result. refer http://makejavafaster.blogspot.com/2015/06/jdbc-fetch-size-performance.html – Amit Naik May 29 '19 at 09:31
  • It is important to check what query (or queries) hibernate is actually generating. Use the show_sql option in the configuration to print them, and then compare them with what you are expecting. – second May 29 '19 at 09:46
  • Dare I ask how big those strings are? Even 300-500 ms seems incredibly slow for 200 records when performed locally, sounds to me the ping is not so much the issue but maybe the throughput is. – john16384 May 29 '19 at 12:32
  • @john16384 the query is slow and I know it but it comes from requesting on views (with unions and joins) and I cannot do much on this side unfortunately – Jean Couteau May 29 '19 at 12:39
  • @AmitNaik Thanks for the pointer, it seems that could improve query time very much. I will try this and tell you the outcome. – Jean Couteau May 29 '19 at 12:40
  • How do your Entities look like? – Simon Martinelli May 29 '19 at 14:13
  • @AmitNaik that sounded promising, but tried it with no luck. fetch_size set to 500 and no significant change observed. – Jean Couteau Jun 03 '19 at 14:43
  • @SimonMartinelli just edited my question with entity exemple (only the abstract part as the entity itself just modify the toString) – Jean Couteau Jun 03 '19 at 14:48
  • What does this code : new JpaEntityIdFactoryResolver().newId(this); and does it the same with H2 and Oracle? – Simon Martinelli Jun 03 '19 at 14:54
  • @SimonMartinelli it is DB independent and only generates a new id, either a sequence : return entity.getClass().getSimpleName() + sequence; or a more a UUID : return entity.getClass().getName() + '_' + UUID.randomUUID().toString(); – Jean Couteau Jun 03 '19 at 15:01
  • @AmitNaik fetch size using hibernate parameter was not taken into account. I set the parameter directly on my query and that speed things up. Thank you very much (Now I get to speed up the SQL request and my views too but that's another story). – Jean Couteau Jun 12 '19 at 13:52

1 Answers1

0

Back on my question to provide an answer I long searched for.

As @AmitNaik pointed out, my problem was coming from the fetch size that was not set and so used the default one (10). I tried to put a different value in hibernate paramter with no luck. It appears that on my Hibernate version, that parameter is not taken into account in my entityManagerFactory (why ?). So I had to set the fetch size on my specific query and that solved my problem (I now am around 600ms in my test case). As @john16384 stated, I now have to investigate my SQL query but that is another story.

Jean Couteau
  • 31
  • 7