ibatis-user-java mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From rodrigo castillo <jrcasti...@lanchile.cl>
Subject Re: bad perfomance ibatis between native JDBC and PLSQL
Date Thu, 07 Apr 2005 02:27:15 GMT
Hi Clinton,


Thank you very much by your information, the environment is:

application server: weblogic 8.1
transaction manager: JNDI WL8.1
EJB class with three DAO's, IBATIS (Framework DAO, PLSQLDAO, JDBCDAO) 


I thought that Ibatis had the same perfomance that native JDBC, butI believe 
that there is something bad in the source developed

Here is the source test:

-----------------------------------------------------------------
JDBC Native:
-----------------------------------------------------------------

            call.append("select * from book b, vlbk v ");
            call.append(" where b.prbk_seq_cdg = v.prbk_seq_cdg ");
            call.append("  and v.lnar_cdg_iata=?");
            call.append("  and v.vlos_cdg=?");
            call.append("  and v.vlos_fch=to_date(?,'dd/mm/yyyy') ");
            call.append("  and v.vlbk_origen=?" );
            call.append("  and v.vlbk_destino =?" );

            CallableStatement cstmt = conn.prepareCall(call.toString());
            closer.add(cstmt);


            setParameter(cstmt, index++, flight.getIataCode()); // 1
            setParameter(cstmt, index++, flight.getFlightNumber().longValue
()); // 2
            setParameter(cstmt, index++, new SimpleDate(flight.getFlightDate
())); // 3
            setParameter(cstmt, index++, flight.getFlightOrigin()); // 4
            setParameter(cstmt, index++, flight.getFlightDestination()); // 5

            ResultSet rs = cstmt.executeQuery();    // Ejecuta el PL.

            BookingDBO booking = null;
            closer.add(rs);

            for (boolean first = true; rs.next();) {


-----------------------------------------------------------------
PL/SQL:
-----------------------------------------------------------------

             call.append("{ call cbkg_testing.getbookingbyflight
(?,?,?,?,?,?)");
             CallableStatement cstmt = conn.prepareCall(call.toString());
             closer.add(cstmt);
             setParameter(cstmt, index++, flight.getIataCode()); // 1
             setParameter(cstmt, index++, flight.getFlightNumber().longValue
()); // 2
             setParameter(cstmt, index++, new SimpleDate(flight.getFlightDate
())); // 3
             setParameter(cstmt, index++, flight.getFlightOrigin()); // 4
             setParameter(cstmt, index++, flight.getFlightDestination()); // 5
             cstmt.registerOutParameter(index, AbstractDAO.getCursorType());
             cstmt.execute();    // Ejecuta el PL.

             // Obtiene cursor con los registros.
             BookingDBO booking = null;
             ResultSet rs = getResultSet(cstmt, index);
             
              PLSQL

  PROCEDURE getBookingByFlight(
     flightCode                        IN vlbk.lnar_cdg_iata%type,
     flightNumber                      IN vlbk.vlos_cdg%type,
     flightDate                        VARCHAR2,
     origin                            VARCHAR2,
     destination                       VARCHAR2,
     io_cursor                         OUT  t_cursor
  ) IS
      -- Declara variables y cursores!...
      excErrorDatosIn                  EXCEPTION;
  BEGIN

          OPEN io_cursor FOR
          select * from book b, vlbk v
           where b.prbk_seq_cdg = v.prbk_seq_cdg
            and v.lnar_cdg_iata=flightCode
            and v.vlos_cdg=flightNumber
            and v.vlos_fch=to_date(flightDate,'dd/mm/yyyy')
            and v.vlbk_origen=origin
            and v.vlbk_destino =destination; 

-----------------------------------------------------------------
IBATIS with framework DAO:
-----------------------------------------------------------------

SQL-MAP.xml


  <typeAlias alias="flight" 
type="com.lan.cargo.laboratorio.persistence.sqlembedded.dbo.FlightDBO"/>
  <typeAlias alias="booking" 
type="com.lan.cargo.laboratorio.persistence.sqlembedded.dbo.BookingDBO"/>

  <resultMap id="bookingResult" class="booking">
       <result property="bookingCode"              column="prbk_seq_cdg"/>
       <result property="companyCode" column="book_empr_cdg" />
       <result property="airlineCode"  column="lnar_cdg_iata" />
       <result property="status"  column="esbk_cdg_estd" />
       <result property="bookingType"  column="tpbk_cdg_booking" />
       <result property="documentType"  column="tdoc_cdg" />
       <result property="user"  column="book_usuario" />
       <result property="userOffice"  column="book_ofcn_usuario" />
       <result property="documentOrigin"  column="book_origen_doc" />
       <result property="documentDestination"  column="book_destino_doc" />
       <result property="flightOrigin"  column="book_origen_vuelo" />
       <result property="flightDestination"  column="book_destino_vuelo" />
       <result property="creationDate" column="book_fecha_gen" />
       <result property="flightDate" column="book_fecha_embarque" />
       <result property="documentPrefix" column="book_pref_doc" />
       <result property="documentNumber" column="book_num_doc" />
       <result property="agentCode" column="book_clhl_cdg" />
       <result property="agentBranchCode" column="book_clhl_scrs" />
       <result property="agentContactName"  column="book_contacto_agente" />
       <result property="agentContactPhone"  column="book_fono_contacto" />
       <result property="shipperCode" column="book_cdg_shipper" />
       <result property="shipperBranchCode" column="book_scrs_shipper" />
       <result property="shipperName"  column="book_nmb_shipper" />
       <result property="consigneeCode" column="book_cdg_consignee" />
       <result property="consigneeName"  column="book_nmb_consignee" />
       <result property="consigneeBranchCode" column="book_scrs_consignee" />
       <result property="averageRate" column="book_tarifa" />
       <result property="remarks"  column="book_remarks" />
       <result property="totalWeight" column="book_gross" />
       <result property="chargeableWeight" column="book_chargeable" />
       <result property="totalVolume" column="book_volumen_total" />
       <result property="totalPieces" column="book_total_piezas" />
       <result property="agentName"  column="book_nmb_agente" />
       <result property="agentAcronym"  column="book_acronimo_agente" />
       <result property="shipperAcronym"  column="book_acronimo_shipper" />
       <result property="consigneeAcronym"  column="book_acronimo_consignee" />
       <result property="contractId"  column="cnto_id" />
       <result property="contractRateType"  
column="book_contrato_allocation" />
   </resultMap>

   <cacheModel id="flight-cache" type ="LRU" readOnly="false" 
serialize="false" >
	<flushInterval hours="24"/>
	<flushOnExecute statement="findBookingByFlight"/>
   </cacheModel>

   <!--***************************MAPPED 
STATEMENTS**************************************-->
   <select id="findBookingByFlight" resultMap="bookingResult" 
cacheModel="flight-cache" parameterClass="flight" >
          select * from book b, vlbk v
           where b.prbk_seq_cdg = v.prbk_seq_cdg
            and v.lnar_cdg_iata=#iataCode#
            and v.vlos_cdg=#flightNumber#
            and v.vlos_fch=#flightDate#
            and v.vlbk_origen=#flightOrigin#
            and v.vlbk_destino =#flightDestination#
   </select>

*******************   sql-map-config.xml ***********************************

    <properties resource="sqlembedded/sql-map-config.properties" />
    <settings
	cacheModelsEnabled="true"
	enhancementEnabled="true"
	lazyLoadingEnabled="true"
        useStatementNamespaces="false"
	maxRequests="512"
	maxSessions="128"
	maxTransactions="32"

     />


    <transactionManager type="JDBC" >
    <!-- dataSource type="JNDI">
           <property name="DataSource" value="bookingds"/>
    </dataSource -->
	 <dataSource type="SIMPLE">
		<property name="JDBC.Driver" value="${driver}"/>
		<property name="JDBC.ConnectionURL" value="${url}"/>
		<property name="JDBC.Username" value="${username}"/>
		<property name="JDBC.Password" value="${password}"/>
   	 </dataSource>
   </transactionManager >


    <sqlMap resource="sqlembedded/bookingcollections.xml" />







Mime
View raw message