0 Replies Latest reply on Nov 20, 2012 4:15 AM by Pablo Esteban Sánchez

    About 20 seconds just to call an stored procedure

    Pablo Esteban Sánchez Newbie

      Hello,

       

      I have the following calling to a PL/SQL stored procedure in orm.xml:

       

      <named-native-query name="WEP_EXAMENES_UXXI_SEAM" result-class="es.ual.webacademica.entity.Examenes">
                <query>{call UAL$UTL_WEBB.P_HOR_EXAMENES(?,:I_PLAN,:I_FINI,:I_FFIN)}</query>
                <hint name="org.hibernate.callable" value="true" />
                <hint name="org.hibernate.readOnly" value="true" />
      </named-native-query>
      

       

       

      When I call it in my bean BeanExamenes, the calling lasts about about 20 secs, while if I execute the procedure itself (with TOAD for example) it lasts about 2 seconds.

       

      The calling in BeanExamenes is like that:

      @Scope(ScopeType.CONVERSATION)
      @Name("beanExamenes")
      public class BeanExamenes {
           private List<Examenes> examenes;
           @In EntityManager entityManager;
           ...
      
           ...
      
           public List<Examenes> getExamenes() {
                if(examenes == null){
                     examenes = new ArrayList<Examenes>();
                     SimpleDateFormat formatoDeFecha = new SimpleDateFormat("dd/MM/yyyy");
                     Query query = getEntityManager().createNamedQuery("WEP_EXAMENES_UXXI_SEAM");
                     query.setParameter("I_PLAN",plan);
                     query.setParameter("I_FINI",formatoDeFecha.format(f_inicio));
                     query.setParameter("I_FFIN",formatoDeFecha.format(f_fin));
                     examenes = new ArrayList<Examenes>(query.getResultList());
                     log.info("Hola, estoy en el getExamenes");
                }
                ...
           }
      

       

      And the debug info I get in the console:

      09:05:26,702 INFO  [STDOUT] Hibernate: 
          select
              taluplaest0_.CODALF as CODALF8_0_,
              taluplaest0_.ANY_ANYACAETG as ANY2_8_0_,
              taluplaest0_.ANY_ANYACAFIN as ANY3_8_0_,
              taluplaest0_.ANY_ANYACAINI as ANY4_8_0_,
              taluplaest0_.CODCICLO as CODCICLO8_0_,
              taluplaest0_.CODOFIPLA as CODOFIPLA8_0_,
              taluplaest0_.CONVENIO as CONVENIO8_0_,
              taluplaest0_.CREEQU as CREEQU8_0_,
              taluplaest0_.CREMINPRJ as CREMINPRJ8_0_,
              taluplaest0_.DATPUB as DATPUB8_0_,
              taluplaest0_.DCTMAT as DCTMAT8_0_,
              taluplaest0_.DECRETO as DECRETO8_0_,
              taluplaest0_.DURACION as DURACION8_0_,
              taluplaest0_.EST_CODALF as EST14_8_0_,
              taluplaest0_.ESTMODU as ESTMODU8_0_,
              taluplaest0_.FLGAUTLC as FLGAUTLC8_0_,
              taluplaest0_.FLGAUTMAT as FLGAUTMAT8_0_,
              taluplaest0_.FLGCNJ as FLGCNJ8_0_,
              taluplaest0_.FLGESTUNI as FLGESTUNI8_0_,
              taluplaest0_.FLGMATQNTPEN as FLGMATQ20_8_0_,
              taluplaest0_.FLGNOR as FLGNOR8_0_,
              taluplaest0_.FLGPAR as FLGPAR8_0_,
              taluplaest0_.FLGPLANTILLA as FLGPLAN23_8_0_,
              taluplaest0_.FLGQNTPENAPL as FLGQNTP24_8_0_,
              taluplaest0_.FLGREB as FLGREB8_0_,
              taluplaest0_.FLGREN as FLGREN8_0_,
              taluplaest0_.FLGSET as FLGSET8_0_,
              taluplaest0_.FLGSML as FLGSML8_0_,
              taluplaest0_.FLGSOLOPRO as FLGSOLOPRO8_0_,
              taluplaest0_.FLGTAN as FLGTAN8_0_,
              taluplaest0_.FLGTEC as FLGTEC8_0_,
              taluplaest0_.FLGVALACA as FLGVALACA8_0_,
              taluplaest0_.FLGVEX as FLGVEX8_0_,
              taluplaest0_.GRA_CODNUM as GRA34_8_0_,
              taluplaest0_.GRA_FLGOFI as GRA35_8_0_,
              taluplaest0_.ISCED as ISCED8_0_,
              taluplaest0_.MOD_CODALF as MOD37_8_0_,
              taluplaest0_.NOLID1 as NOLID38_8_0_,
              taluplaest0_.NOLID2 as NOLID39_8_0_,
              taluplaest0_.NOLID3 as NOLID40_8_0_,
              taluplaest0_.NOMID1 as NOMID41_8_0_,
              taluplaest0_.NOMID2 as NOMID42_8_0_,
              taluplaest0_.NOMID3 as NOMID43_8_0_,
              taluplaest0_.NUMANYS as NUMANYS8_0_,
              taluplaest0_.NUMHOR as NUMHOR8_0_,
              taluplaest0_.NUMREF as NUMREF8_0_,
              taluplaest0_.NUMSEGEXP as NUMSEGEXP8_0_,
              taluplaest0_.PERMAT as PERMAT8_0_,
              taluplaest0_.STE_CODALF as STE49_8_0_,
              taluplaest0_.TCURS_CODALF as TCURS50_8_0_,
              taluplaest0_.TES_CODALF as TES51_8_0_,
              taluplaest0_.TIPFIN as TIPFIN8_0_,
              taluplaest0_.TIPPLA as TIPPLA8_0_ 
          from
              UXXIAC.TALU_PLAESTUDI taluplaest0_ 
          where
              taluplaest0_.CODALF=?
      09:05:26,966 INFO  [STDOUT] Hibernate: 
          {call UAL$UTL_WEBB.P_HOR_EXAMENES(?,?,?,?)}
      09:05:45,008 INFO  [BeanExamenes] Hola, estoy en el getExamenes
      

       

      Any ideas about the big difference cost in time beetwen calling the pl/sql directly and through hibernate?

       

      I'm using

      Oracle JDBC driver, version: 10.2.0.5.0.

      Hibernate 3.5

      Jboss seam: 2.2.2.Final

       

      Thanks in advance!

      Pablo