a question about performance and speed

mnrz · **Joined:** Sat Oct 08, 2005 2:13 am **Posts:** 47

Hibernate version: 3.2

Mapping documents: Annotation

Name and version of the database you are using:MySql

I put some code to populate a list from database using hibernate criteria. the table has about five hundred thousand records. I just write some codes to select from database and display them on console.

also, I did the above using jdbc and ResultSet.

in comparison, the ResultSet is very very faster than hibernate. I test it to index selected records with Lucene and amazingly, with ResultSet it done very faster.

I want to know, is this normal? or I made bad configuration for Hibernate? if my configuration for hibernate are not tuned would you tell me which properties should be changed?

thank you very much in advance.

emmanuel · **Posted:** Wed Jan 10, 2007 10:10 am

your questions are too vague, can you rephrase them with concrete minimal code and figures?

mnrz · **Joined:** Sat Oct 08, 2005 2:13 am **Posts:** 47

I have an entity mapped to a tabale. I have two processes, first I want to display the data in a grid. second, I want to index the data by Lucene.

using Query's hibernate, it works very slow.
because the records are more than 500 thousand, indexing process (fetching records and then index them) takes a long time.

but using Jdbc Statement and ResultSet, it works very very fast, and the duration time is almost half of the hibernate fetching.

here is the configuration of Hibernate:

Code:

      AnnotationConfiguration conf = new AnnotationConfiguration();
      conf.addPackage("com.test.entity");
      conf.setPrecedence("class");
      conf.addAnnotatedClass(FileFormation.class);
      
      conf.setProperty(Environment.HBM2DDL_AUTO, "none");
      conf.setProperty(Environment.DIALECT,"org.hibernate.dialect.SQLServerDialect")
      .setProperty(Environment.DRIVER,"com.microsoft.jdbc.sqlserver.SQLServerDriver")
      .setProperty(Environment.URL,"jdbc:microsoft:sqlserver://192.168.0.60;DatabaseName=his1_shd;SelectMethod=cursor")
      .setProperty(Environment.STATEMENT_BATCH_SIZE,"1000")
      .setProperty(Environment.USER,"sa")
      .setProperty(Environment.PASS,"sa")
      .setProperty(Environment.AUTOCOMMIT,"true")
      .setProperty(Environment.SHOW_SQL,"true");

      factory = conf.buildSessionFactory();
      session = factory.openSession();

here is the code to use the hibernate:

Code:

      Indexer indexer = new Indexer(true);
      try {
         indexer.setup("h://index");
      }catch (Exception e) {
         e.printStackTrace();
      }
      Session s = HibernateUtil.getSession();
      s.beginTransaction();
      
      Integer count = (Integer) s.createQuery("select count(*) from FileFormation").uniqueResult();
      Integer pageSize = 1000;
      Integer pageCount = (count.intValue() / pageSize);
      int pageNumber = 0;
      Integer firstId;
      Integer lastId;

      Query q = s.createQuery("from FileFormation fileFormation where fileFormation.idFileFormation != null order by fileFormation.idFileFormation");
      q.setMaxResults(pageSize);
      List list = q.list();
      firstId = ((FileFormation)list.get(0)).getIdFileFormation();
      lastId = ((FileFormation)list.get(list.size()-1)).getIdFileFormation();
      System.out.println("From ID:"+firstId+" TO "+lastId);
      System.out.println("Max:"+Runtime.getRuntime().maxMemory()/1048576+" free:"+Runtime.getRuntime().freeMemory()/1048576);
      for(int i=0;i <pageCount ;++i) {
         
//         q.setFirstResult(pageSize * pageNumber);
         for(Object o : list) {
            FileFormation ff = (FileFormation) o;
            //System.out.println(ff.getFirstName());
            //System.out.print(ff.getIdFileFormation()+"-");
//            if(true)
//               continue;
            try {
               indexer.addFieldAsId("idFileFormation",String.valueOf(ff.getIdFileFormation()));
               indexer.addFieldAsId("hospitalFileId",String.valueOf(ff.getHospitalFileId()));
               indexer.addField("firstName",(ff.getFirstName()));
               indexer.addField("lastName",(ff.getLastName()));
               indexer.addField("fatherName",(ff.getFatherName()));
               indexer.addField("sex",(ff.getSex()));
               indexer.addField("birthDate",ff.getBirthDate());
               indexer.addField("birthCity",(ff.getBirthCity()));
               indexer.addDocumentAndProceed();
            }catch (Exception e) {
               e.printStackTrace();
            }
               
            //System.out.print(((FileFormation)o).getIdFileFormation());
            //System.out.print(":::");
         }
         try {
            indexer.buildIndexAndReconstruct();
         }catch(Exception x) {
            x.printStackTrace();
         }
         System.out.println();
         System.out.println("-------------------------");
         
         q = null;
         q = s.createQuery("from FileFormation fileFormation where fileFormation.idFileFormation != null and " +
               " fileFormation.idFileFormation > "+lastId+" order by fileFormation.idFileFormation");
         q.setMaxResults(pageSize);
         
         list.clear();
         list = null;
         list = q.list();
         if(list.size() > 0) {
            firstId = ((FileFormation)list.get(0)).getIdFileFormation();
            lastId = ((FileFormation)list.get(list.size()-1)).getIdFileFormation();
            System.out.println("From ID:"+firstId+" TO "+lastId);
         }else {
            System.out.println("No more records !!!");
            break;
         }
         s.clear();
         s.flush();
         System.out.flush();
         pageNumber++;
      }
      try {
         indexer.finalizeIndex();
         }catch (Exception e) {
            e.printStackTrace();
         }

      
      s.close();
      

as you can see I used some codes to support paging, although it throws a "java heap space" exception after fetching about 60,000 records. but fetching these 60000 records are very slow.

so I decided to use Jdbc ResultSet and "Statment". it never throws exception and works faster than Hibernate.

the question is, Is there any configuration that I didnt apply to hibernate which causing Hibernate work fast like java.sql.ResultSet?

Thank you emmanuel for your reply.

emmanuel · **Posted:** Fri Jan 12, 2007 7:54 pm

There are many issues, to name few:
- do not use autocommit, it can slow down the process
- use query.scroll() FORWARD_ONLY rather than .list() you won't get OOME and you won't need pagescrolling
- try and reduce the loop size, this will optimize the flush()

About Lucene, I would recommend you to use Hibernate Search. I made a nice integration and all indexing is batched to optimize the lucene index access.