Do not need to fetch all data on the early list

2013-05-19
2013-08-14
  • Sahba Changizi

    Sahba Changizi - 2013-05-19

    Hi, I need to show a list of 2000000 users in my application which using OpenXava, but the problem is that when dealing with such a huge list of users, it takes a long time for the grid to be rendered. It seems that application will fetch all 2 millions of records with the first query. how can I solve this problem and make benefit of database pagination or change it in a way not to show any records at first. but application user can filter a result set from columns and work with that.

     
  • Javier Paniza

    Javier Paniza - 2013-05-21

    Hi Sahba,

    OpenXava only loads the first 50 records, and it loads the rest of the data on demand, in 50 records chunks. Moreover, OpenXava is used in application with millions of records with a very good performance. Therefore, your performance problem is in other place.

    I can help you to solve your performance issue. My first advice it's to try your module just with plain properties, with no references and with no calculated properties in the list. Does it perform well in this case?


    Help others in this forum as I help you.
    Need more help? Get OpenXava professional support.

     
  • Sahba Changizi

    Sahba Changizi - 2013-05-22

    No, in face my entity class is just simple and single field.
    @Entity
    @Table(name = "USER")
    public class User {

    @Id
    @Column(name = "USER_ID")
    private Long id;
    
    @Column(name = "NATIONAL_CODE", length = 40)
    private Long nationalCode;
    

    ... //Getter and setters
    }

    when I type in the class name at the front of module name it takes about 3 minutes to fetch the primary list. which I think is not a reasonable time. All the time which the list is being displayed tomcat log is stucked on:

    FINE: [JPATabProvider.nextBlock] Executing SELECT e.id, e.id, e.nationalCode from User e with arguments

    And related to the project, I have cloned a fresh build from OpenXavaTemplate and started developing my own model. How can I improve the database performance?

    PS: I'm using DB2 as my database engine.

     
  • Javier Paniza

    Javier Paniza - 2013-05-24

    Hi Sahba,

    try the next experiment. Edit the JPAPersistenceProvider class, and change

    public ITabProvider createTabProvider() {
        return new JPATabProvider();                
    }
    

    by

    public ITabProvider createTabProvider() {
        return new JDBCTabProvider();               
    }
    

    Does it improve the performance significantly?


    Help others in this forum as I help you.
    Need more help? Get OpenXava professional support.

     
  • Sahba Changizi

    Sahba Changizi - 2013-05-25

    The Performance has been significantly improved now, thanks for your help

     
  • Javier Paniza

    Javier Paniza - 2013-08-14

    Hi Sahba,

    I have tested the performance against a 600,000 rows table with JPATabProvider and the response time (as Firebugs says) is around 600 ms, for querying and drawing the corresponding html, including the count. I tried with JDBCTabProvider and the performance is just the same. I tested using an AS/400 as database.

    Therefore, the performance of JPATabProvider with huge tables is very good.

    Your problem could be related by this Hibernate bug:
    https://hibernate.atlassian.net/browse/HHH-2176

    But this is a Hibernate bug, not an OpenXava one. When we'll upgrade the Hibernate version this bug will be solved. In the meantime you can use JDBCTabProvider.


    Help others in this forum as I help you.
    Need more help? Get OpenXava professional support.

     

Log in to post a comment.