-->
These old forums are deprecated now and set to read-only. We are waiting for you on our new forums!
More modern, Discourse-based and with GitHub/Google/Twitter authentication built-in.

All times are UTC - 5 hours [ DST ]



Forum locked This topic is locked, you cannot edit posts or make further replies.  [ 3 posts ] 
Author Message
 Post subject: Smart retrieving of large resultset (100,000+ records)
PostPosted: Mon Jul 25, 2005 4:48 am 
Regular
Regular

Joined: Mon Jun 13, 2005 12:21 pm
Posts: 61
Location: Hannover
Hi,

I've got an application which handles some table with quite a lot of records (1,000,000+). I've got an user interface where the user can make queries on this table which could return quite a lot of results.

The results are displayed in a pageable behaviour, so I actually need the first few records (i.e. 10) and the other objects could be initialized lazy (when the user clicks the paging buttonAs far as I know Hibernate can init assoziations lazy, but can it also lazy initialize objects of one result set?

If not, are there any smart solutions to avoid the loading of 100,000+ objects for a simple query?

Thx
Joerg

Hibernate version:3.05


Top
 Profile  
 
 Post subject:
PostPosted: Mon Jul 25, 2005 5:06 am 
Expert
Expert

Joined: Thu Dec 04, 2003 12:36 pm
Posts: 275
Location: Bielefeld, Germany
You can of course restrict the number of rows the query should return.
Code:
Query q = sess.createQuery(...);
q.setFirstResult(x);
q.setMaxResults(y);

It's quite easy to implement a pagination solution:
http://www.hibernate.org/hib_docs/v3/re ... pagination

This might be helpful as well:
http://www.hibernate.org/314.html

Best regards
Sven


Top
 Profile  
 
 Post subject:
PostPosted: Tue Jul 26, 2005 4:49 am 
Regular
Regular

Joined: Mon Jun 13, 2005 12:21 pm
Posts: 61
Location: Hannover
Hi Sven,

thx for you tips. They didn't solve my problems but provided some pretty good starting points. I guess for now I've found a pretty smart solution, by proxying query.list().

Cause it might be interesting for someone else, I'll post some code snippets here. The solution is ok for now but has some unsolved problems:
    I fear there is no way finding out, if a db driver supports a scrollable resultset
    There is no generic way to apply a count on a HQL query without retrieving all objects. Select count(*) approaches require a session and doesn't seem to work with all my test cases.
    Quite a lot of performance work could be done, if scrollable resultset are supported by the db driver.

I'm thinking of modifying the mapping documents somehow, so a user can configure, if a query on a class should return a traditional or a LazyList. Don't know if such features would be interesting to anybody, just let me know.

Joerg
Code:
public class LazyInitList implements List {
    public static final int ALL = Integer.MAX_VALUE;
    private final static Log log = LogFactory.getLog(LazyInitList.class);
    private static final int UNKWOWN = -1;
    private static final int NO_SUPPORTED = 0;
    private static final int SUPPORTED = 1;
    private int resultSetScrollable = UNKWOWN;
    private int pageSize = 20;
    private Query query = null;
    private Criteria criteria = null;
    private List resultList = null;
    private int loadedSize=0;
    private int totalSize=-1;

    public LazyInitList(final Query query) {
        super();
        this.query = query;
    }

    public LazyInitList(final Criteria criteria) {
        super();
        this.criteria = criteria;
    }   
   
    public LazyInitList(final Query query, final int defaultFetchCount) {
        super();
        this.query = query;
        this.pageSize = defaultFetchCount;
    }

    public LazyInitList(final Criteria criteria, final int defaultFetchCount) {
        super();
        this.criteria = criteria;
        this.pageSize = defaultFetchCount;
    } 
   
   
    private List initList(final int pos) {
        if (resultList==null || (pos>loadedSize)) {
            if (query!=null) {
                if (loadedSize<ALL) {
                    loadedSize = loadedSize<pageSize ? pageSize : Math.max(loadedSize*2,pos+pageSize);
                }
                if (pos==ALL) {
                    loadedSize = ALL;
                }
                query.setMaxResults(loadedSize);
                resultList = query.list();
            }
            if (criteria!=null) {
                if (loadedSize<ALL) {
                    loadedSize = loadedSize<pageSize ? pageSize : Math.max(loadedSize*2,pos+pageSize);
                }
                if (pos==ALL) {
                    loadedSize = ALL;
                }
                criteria.setMaxResults(loadedSize);
                resultList = criteria.list();
            }
        }
        return resultList;
    }
   
    /**
     * Return total collection size by using scrollable resultsets
     * with fallback to HQL or Criteria query
     */
    private int getTotalSize() {
        if (totalSize==-1) {
            if (loadedSize==ALL) {
                // If the list is already loaded don't make a separat query
                totalSize=  resultList.size();
            } else {
                if (resultSetScrollable!=NO_SUPPORTED) {
                    // As far as I know there is no way checking for scrollable resultsset
                    // so this is an try and error approach chechkign for scrollable resultsets
                   try {
                      ScrollableResults scrollable = criteria==null ? query.scroll(ScrollMode.SCROLL_SENSITIVE) : criteria.scroll(ScrollMode.SCROLL_SENSITIVE);
                    scrollable.afterLast();
                    scrollable.previous();
                    totalSize = scrollable.getRowNumber()+1;
                    resultSetScrollable = SUPPORTED;
                   } catch (Exception e) {
                       log.warn("ScrollableResults seems not be supported from the database.");
                       resultSetScrollable = NO_SUPPORTED;
                       getTotalSizeByQuery();
                   }
                } else {
                    // return size with separate query
                    getTotalSizeByQuery();
                }
            }
        }
        return totalSize;
    }
   
    /**
     * Return total collection size with a separate
     * HQL or Crieteria query
     */
    private void getTotalSizeByQuery() {
        if (criteria!=null) {
            criteria.setProjection(Projections.rowCount());
            totalSize = ((Integer)(criteria.uniqueResult())).intValue();
            criteria.setProjection(null);
            criteria.setResultTransformer(Criteria.ROOT_ENTITY);
        }
        if (query!=null) {
            initList(ALL);
            totalSize = resultList.size();
        }
    }

    public int getLoadedSize() {
        return loadedSize;
    }
   
    public void add(int index, Object element) {
        initList(index);
        resultList.add(index, element);
    }
    public boolean add(Object o) {
        initList(size());
        return resultList.add(o);
    }
   [...}
}


      Top
       Profile  
       
      Display posts from previous:  Sort by  
      Forum locked This topic is locked, you cannot edit posts or make further replies.  [ 3 posts ] 

      All times are UTC - 5 hours [ DST ]


      You cannot post new topics in this forum
      You cannot reply to topics in this forum
      You cannot edit your posts in this forum
      You cannot delete your posts in this forum

      Search for:
      cron
      © Copyright 2014, Red Hat Inc. All rights reserved. JBoss and Hibernate are registered trademarks and servicemarks of Red Hat, Inc.