iterate() or list(), which is efficient for performance

Madan_Prabhu · **Posted:** Fri Oct 17, 2008 4:11 am

Code:

public String[] checkSecurityCode(String code){
      String[] result = new String[8];
      BaseHibernateDAO dao=null;
      try {
      dao= new BaseHibernateDAO();
      session =dao.getSession();
      txt=session.beginTransaction();
      Iterator results =session.createQuery("from Users u where u.username='admin'").iterate();
          if(results.hasNext()) {
             Users u=(Users)results.next();
            String uid =u.getUserId().toString();
           
            MessageDigest md = MessageDigest.getInstance("MD5");
                md.update(code.getBytes());
                String encodedCode = new String(Base64.encode(md.digest()));
               
                String encodedDbSecurityCode = u.getSecurityCode();
                if(!encodedDbSecurityCode.equals(encodedCode)) {
                   result[0] = "<b>Sorry!</b> Incorrect security code.";
                }
                else if(encodedDbSecurityCode.equals(encodedCode)) {
                   result[0] = "Success";
                   result[1] = uid;
                   result[2] = u.getDashFlag();
                   result[3] = u.getUserGroup().toString();
                   result[4] = u.getUserRole();
                   long nextPasswordChange = u.getNextPasswordChange();
                   boolean changePwd = new Date().getTime() >= nextPasswordChange;
                   result[5] = Boolean.toString(changePwd);
                   result[6] = u.getHasMessage().toString();
                   result[7] = u.getLastLogin().toString();
                   new Update().setUserValidLogin(uid, session);
                }
         }
      }
      catch (Exception e) {
         System.out.println("Exception in Retrieve > checkSecurityCode: " + e.getMessage());
         result[0] = "Sorry! There was a problem while processing your request.";
      }
      finally{
         txt.commit();
         session.close();         
      }     
      return result;
   }   

But it's very slow when compared to native JDBC code.
Could anyone guide me to improve fetching performance and techniques to tuning MySQL DB for hibernate. How to write better hibernate code for insert, retrieve, update and delete operations for my project.

martin_groenhof · **Joined:** Mon Aug 16, 2010 12:32 pm **Posts:** 12

I have the same problem(viewtopic.php?f=1&t=1006449) and for large tables it breaks down completely,

I don't understand why in such a supposedly high performance framework it's so difficult to iterate over a 'small' 2Gb database ?

With native sql I do it in under 2 mins with hibernate it all breaks down and lasts for hours

I can't find any totorials or example of how to efficiently iterate over large tables, and with what I know I can't do it

yaragalla_murali · **Joined:** Tue Aug 17, 2010 3:29 pm **Posts:** 12

Iterate will be faster if the pojos are already cached in the session. if not list is the best. in general list is faster. If pojos are not previously cached it has to hit the database for so many times when you use iterate.

achyut · **Joined:** Thu Dec 11, 2008 8:18 am **Posts:** 35

when retrieving all the columns from database use list otherwise for specific column use iterator.
This will increase the performance.

kotapati.krishna · **Joined:** Tue Jul 20, 2010 1:53 am **Posts:** 18

off course List l=query.list(); ---->is best for all type of data structure objects. . .

if you want to perform the Single row operations -->save(),delete(),persist(),persistOrUpdate() etc methods are available in HB s/w.

sanne.grinovero · **Posted:** Thu Aug 19, 2010 4:55 pm

Quote:

I don't understand why in such a supposedly high performance framework it's so difficult to iterate over a 'small' 2Gb database ?

With native sql I do it in under 2 mins with hibernate it all breaks down and lasts for hours

It all depends on what you want to do with this 2GB data. If you need to process it within Java, you have to load each of them out of the database, if you have to do a bulk update, use bulk update statements and don't try to load the data.
The problem is that you're copying the 2GB data from the database into your JVM, you might want to avoid that, or if you have good reasons to do it then it will be slower than just processing it in the database.

You can still get decent performance using scrollable results with tuned batch sizes, but it won't be on par of a single SQL statement on the database, that would be comparing apples to pears.

martin_groenhof · **Joined:** Mon Aug 16, 2010 12:32 pm **Posts:** 12

I have partialy solved the problem, my table had a compund primary key like (bigint and varchar) the problem was the bigints where random ie. not 1,2,3,.. but 1,5,6,9,14 etc.

I have put in a new index of simple ints that are incremental and I am iterating over it it's much faster