Hello there.
I'm using a query which returns 17 thousands records. (the one called AVCD_VCDAWB_FINDBY_PAIS_FECLIQUIDACION)
It takes about 30 seconds in order to finalize.
All the necesary indexes have been created.

is there something in the mapping that I could change in order to improve the query?

Thanks in advanced.


Hibernate version:
3.0.5

Mapping documents:

<!DOCTYPE hibernate-mapping PUBLIC
"-//Hibernate/Hibernate Mapping DTD 3.0//EN" "http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">
<hibernate-mapping>
<class name="com.iberia.cargo.cra.interfaces.avcd.Vcdawb" schema="VCDGEN" table="VCDAWB">

<composite-id class="com.iberia.cargo.cra.interfaces.avcd.VcdawbPK" name="idVcdawb">
<key-property column="AWB_COD_CIA" name="awbCodCia" type="java.lang.String"/>
<key-property column="AWB_NUM_AWB" name="awbNumAwb" type="java.lang.Integer"/>
<key-property column="AWB_FEC_EXP" name="awbFecExp" type="date"/>
</composite-id>

<property column="AWB_COD_PAIS" update="false" insert="false" name="awbCodPais" type="com.iberia.cargo.cra.util.comun.StringPersistente"/>
<property column="AWB_FEC_LIQ" update="false" insert="false" name="awbFecLiq">
<type name="com.iberia.cargo.cra.util.comun.DateNuloPersistente">
<param name="PARAM_FECHA_NULA">01/01/0001</param>
</type>
</property>
<property column="AWB_COD_AGT" update="false" insert="false" name="awbCodAgt" type="java.lang.String"/>
<property column="AWB_ORIGEN" update="false" insert="false" name="awbOrigen" type="java.lang.String"/>
<property column="AWB_DESTINO" update="false" insert="false" name="awbDestino" type="java.lang.String"/>
<property column="AWB_REGION" update="false" insert="false" name="awbRegion" type="java.lang.String"/>
<property column="AWB_CONTI" update="false" insert="false" name="awbConti" type="java.lang.String"/>
<property column="AWB_ZONA" update="false" insert="false" name="awbZona" type="java.lang.String"/>
<property column="AWB_DIST_IATA" update="false" insert="false" name="awbDistIata" type="java.lang.String"/>
<property column="AWB_FECHA_CREACION" update="false" insert="false" name="awbFechaCreacion" type="java.util.Date"/>
<property column="AWB_TIP_AWB" update="false" insert="false" name="awbTipAwb" type="java.lang.Character"/>
<property column="AWB_IND_VAT" update="false" insert="false" name="awbIndVat">
<type name="com.iberia.cargo.cra.util.comun.BooleanPersistente">
<param name="VALOR_BD_TRUE">Y</param>
<param name="VALOR_BD_FALSE">N</param>
<param name="TIPO_BD_COLUMNA">CHAR</param>
</type>
</property>
<property column="AWB_IND_VENTA" update="false" insert="false" name="awbIndVenta" type="java.lang.Character"/>
<property column="AWB_MONEDA" update="false" insert="false" name="awbMoneda" type="java.lang.String"/>
<property column="AWB_CAMBIO" update="false" insert="false" name="awbCambio" type="java.math.BigDecimal"/>
<property column="AWB_IND_PESO" update="false" insert="false" name="awbIndPeso" type="java.lang.Character"/>
<property column="AWB_PESO" update="false" insert="false" name="awbPeso" type="java.math.BigDecimal"/>
<property column="AWB_PESO_CARGABLE" update="false" insert="false" name="awbPesoCargable" type="java.math.BigDecimal"/>
<property column="AWB_CARG_PESO_PP" update="false" insert="false" name="awbCargPesoPp" type="java.math.BigDecimal"/>
<property column="AWB_CARG_VALOR_PP" update="false" insert="false" name="awbCargValorPp" type="java.math.BigDecimal"/>
<property column="AWB_CARG_FVR_CIA_PP" update="false" insert="false" name="awbCargFvrCiaPp" type="java.math.BigDecimal"/>
<property column="AWB_CARG_FVR_AGT_PP" update="false" insert="false" name="awbCargFvrAgtPp" type="java.math.BigDecimal"/>
<property column="AWB_CARG_PESO_CC" update="false" insert="false" name="awbCargPesoCc" type="java.math.BigDecimal"/>
<property column="AWB_CARG_VALOR_CC" update="false" insert="false" name="awbCargValorCc" type="java.math.BigDecimal"/>
<property column="AWB_CARG_FVR_CIA_CC" update="false" insert="false" name="awbCargFvrCiaCc" type="java.math.BigDecimal"/>
<property column="AWB_CARG_FVR_AGT_CC" update="false" insert="false" name="awbCargFvrAgtCc" type="java.math.BigDecimal"/>
<property column="AWB_PORC_COM" update="false" insert="false" name="awbPorcCom" type="java.math.BigDecimal"/>
<property column="AWB_IMP_COM" update="false" insert="false" name="awbImpCom" type="java.math.BigDecimal"/>
<property column="AWB_INCT_VENT" update="false" insert="false" name="awbInctVent" type="java.math.BigDecimal"/>
<property column="AWB_IMP_FVR_CIA" update="false" insert="false" name="awbImpFvrCia" type="java.math.BigDecimal"/>
<property column="AWB_IMP_FVR_AGT" update="false" insert="false" name="awbImpFvrAgt" type="java.math.BigDecimal"/>
<property column="AWB_COD_NATUR" update="false" insert="false" name="awbCodNatur" type="com.iberia.cargo.cra.util.comun.StringPersistente"/>
<property column="AWB_NATUR_ESPECIF" update="false" insert="false" name="awbNaturEspecif" type="com.iberia.cargo.cra.util.comun.StringPersistente"/>
<property column="AWB_CIF_CLIENTE" update="false" insert="false" name="awbCifCliente" type="com.iberia.cargo.cra.util.comun.StringPersistente"/>
<property column="AWB_COD_CONSI_MERC" update="false" insert="false" name="awbCodConsiMerc" type="java.lang.String"/>
<property column="AWB_TARIFA_APLIC" update="false" insert="false" name="awbTarifaAplic" type="java.lang.String"/>
<property column="AWB_TIP_TARIFA" update="false" insert="false" name="awbTipTarifa" type="java.lang.String"/>
<property column="AWB_IMP_DIF_VAL" update="false" insert="false" name="awbImpDifVal" type="java.math.BigDecimal"/>
<property column="AWB_IMP_NETO" update="false" insert="false" name="awbImpNeto" type="java.math.BigDecimal"/>

<bag name="lstVcdawbGas"
table="VCDAWB_GAS"
lazy="true"
cascade="none">
<key update="false">
<column name="AWB_GAS_COD_CIA" not-null="true"/>
<column name="AWB_GAS_NUM_AWB" not-null="true"/>
<column name="AWB_GAS_FEC_EXP" not-null="true"/>
</key>
<one-to-many class="com.iberia.cargo.cra.interfaces.avcd.VcdawbGas"/>
</bag>

<bag name="lstVcdawbImp"
table="VCDAWB_IMP"
lazy="true"
cascade="none">
<key update="false">
<column name="AWB_IMP_COD_CIA" not-null="true"/>
<column name="AWB_IMP_NUM_AWB" not-null="true"/>
<column name="AWB_IMP_FEC_EXP" not-null="true"/>
</key>
<one-to-many class="com.iberia.cargo.cra.interfaces.avcd.VcdawbImp"/>
</bag>


<bag name="lstVcdawbPag"
table="VCDAWB_PAG"
lazy="true"
cascade="none">
<key update="false">
<column name="AWB_PAG_COD_CIA" not-null="true"/>
<column name="AWB_PAG_NUM_AWB" not-null="true"/>
<column name="AWB_PAG_FEC_EXP" not-null="true"/>
</key>
<one-to-many class="com.iberia.cargo.cra.interfaces.avcd.VcdawbPag"/>
</bag>


<bag name="lstVcdawbTar"
table="VCDAWB_TAR"
lazy="true"
cascade="none">
<key update="false">
<column name="AWB_TAR_COD_CIA" not-null="true"/>
<column name="AWB_TAR_NUM_AWB" not-null="true"/>
<column name="AWB_TAR_FEC_EXP" not-null="true"/>
</key>
<one-to-many class="com.iberia.cargo.cra.interfaces.avcd.VcdawbTar"/>
</bag>


<many-to-one name="lnkVcdnat" insert="false" update="false" class="com.iberia.cargo.cra.interfaces.avcd.Vcdnat" lazy="true">
<column name="AWB_COD_NATUR"/>
<column name="AWB_NATUR_ESPECIF"/>
</many-to-one>

<!--
<many-to-one name="lnkVcdgenCli" insert="false" update="false" class="com.iberia.cargo.cra.interfaces.avcd.VcdgenCli" lazy="true">
<column name="AWB_CIF_CLIENTE"/>
<column name="AWB_COD_PAIS"/>
</many-to-one>
-->


</class>

<query name = "AVCD_VCDAWB_FINDBY_CIA_NUMAWB_FECEXPEDICION">
<![CDATA[ from Vcdawb v where
v.idVcdawb.awbNumAwb = :numeroAWB and
v.idVcdawb.awbCodCia = :cia and
v.idVcdawb.awbFecExp = :fecExpedicion
]]>
</query>

<query name = "AVCD_VCDAWB_FINDBY_PAIS_FECLIQUIDACION">
<![CDATA[ from Vcdawb v where
v.awbCodPais = :codigoPaisISO and
v.awbFecLiq = :fecLiquidacion
]]>
</query>
</hibernate-mapping>


Code between sessionFactory.openSession() and session.close():

/** Calling to AVCD_VCDAWB_FINDBY_PAIS_FECLIQUIDACION query */
l = DAOVcdawb.findByFechaLiquidacionPais(
getFecha(),
getCodigoPaisISO());


Full stack trace of any exception that occurs:


Name and version of the database you are using:
Oracle 8.1.7

The generated SQL (show_sql=true):

select vcdawb0_.AWB_COD_CIA as AWB1_, vcdawb0_.AWB_NUM_AWB as AWB2_, vcdawb0_.AWB_FEC_EXP as AWB3_, vcdawb0_.AWB_COD_PAIS as AWB4_34_, vcdawb0_.AWB_FEC_LIQ as AWB5_34_, vcdawb0_.AWB_COD_AGT as AWB6_34_, vcdawb0_.AWB_ORIGEN as AWB7_34_, vcdawb0_.AWB_DESTINO as AWB8_34_, vcdawb0_.AWB_REGION as AWB9_34_, vcdawb0_.AWB_CONTI as AWB10_34_, vcdawb0_.AWB_ZONA as AWB11_34_, vcdawb0_.AWB_DIST_IATA as AWB12_34_, vcdawb0_.AWB_FECHA_CREACION as AWB13_34_, vcdawb0_.AWB_TIP_AWB as AWB14_34_, vcdawb0_.AWB_IND_VAT as AWB15_34_, vcdawb0_.AWB_IND_VENTA as AWB16_34_, vcdawb0_.AWB_MONEDA as AWB17_34_, vcdawb0_.AWB_CAMBIO as AWB18_34_, vcdawb0_.AWB_IND_PESO as AWB19_34_, vcdawb0_.AWB_PESO as AWB20_34_, vcdawb0_.AWB_PESO_CARGABLE as AWB21_34_, vcdawb0_.AWB_CARG_PESO_PP as AWB22_34_, vcdawb0_.AWB_CARG_VALOR_PP as AWB23_34_, vcdawb0_.AWB_CARG_FVR_CIA_PP as AWB24_34_, vcdawb0_.AWB_CARG_FVR_AGT_PP as AWB25_34_, vcdawb0_.AWB_CARG_PESO_CC as AWB26_34_, vcdawb0_.AWB_CARG_VALOR_CC as AWB27_34_, vcdawb0_.AWB_CARG_FVR_CIA_CC as AWB28_34_, vcdawb0_.AWB_CARG_FVR_AGT_CC as AWB29_34_, vcdawb0_.AWB_PORC_COM as AWB30_34_, vcdawb0_.AWB_IMP_COM as AWB31_34_, vcdawb0_.AWB_INCT_VENT as AWB32_34_, vcdawb0_.AWB_IMP_FVR_CIA as AWB33_34_, vcdawb0_.AWB_IMP_FVR_AGT as AWB34_34_, vcdawb0_.AWB_COD_NATUR as AWB35_34_, vcdawb0_.AWB_NATUR_ESPECIF as AWB36_34_, vcdawb0_.AWB_CIF_CLIENTE as AWB37_34_, vcdawb0_.AWB_COD_CONSI_MERC as AWB38_34_, vcdawb0_.AWB_TARIFA_APLIC as AWB39_34_, vcdawb0_.AWB_TIP_TARIFA as AWB40_34_, vcdawb0_.AWB_IMP_DIF_VAL as AWB41_34_, vcdawb0_.AWB_IMP_NETO as AWB42_34_ from VCDGEN.VCDAWB vcdawb0_ where vcdawb0_.AWB_COD_PAIS=? and vcdawb0_.AWB_FEC_LIQ=?

Debug level Hibernate log excerpt:

How long does the physical sql query take? I would think loading 17, 000 rows is a bit of an extreme case isn't it?

You might want to turn off SQL tracing. That may have a large performance boost to your query, especially if a lot of SQL is being executed.

-- chris --

Forget that. I reread your post and it's a single SQL statement that's being generated.

How long does it take Java to open a regular ResultSet based on that query and iterate over the records?

I'm executing the query with TOAD and it takes 20 miliseconds.

(1) are you sure TOAD is Java
(2) use ScrollableResults and clear(), as described in the reference documentation!

Hello gavin.

I said how much time the query takes using toad because above VampBoy asked me "How long does the physical sql query take?"

I can't use ScrollableResult because I need the whole result at one time.

It's possible other ways to improve the query?
Ttunning the hibernate.cfg.xml, for example....

Here is my hibernate.cfg.xml

<!DOCTYPE hibernate-configuration PUBLIC "-//Hibernate/Hibernate Configuration DTD 3.0//EN"
"http://hibernate.sourceforge.net/hibernate-configuration-3.0.dtd">

<hibernate-configuration>
<session-factory>

<!-- <property name="connection.url">jdbc:oracle:thin:@172.22.2.32:1529:IBSISCAR</property>-->
<!-- <property name="connection.username">VCDCRA</property>-->
<!-- <property name="connection.password">VCDCRA</property>-->
<property name="connection.url">jdbc:oracle:thin:@172.22.1.4:1529:IBSISCAR</property>
<property name="connection.username">CRA</property>
<property name="connection.password">CRA</property>
<property name="c3p0.min_size">5</property>
<property name="c3p0.max_size">20</property>
<property name="c3p0.timeout">1800</property>
<property name="c3p0.max_statements">50</property>
<property name="connection.driver_class">oracle.jdbc.driver.OracleDriver</property>
<property name="dialect">org.hibernate.dialect.OracleDialect</property>
<property name="show_sql">false</property>
<property name="use_identifer_rollback">true</property>
<!-- <property name="default_batch_fetch_size">8</property>-->
<!-- <property name="jdbc.fetch_size">20</property>-->
<property name="cache.use_second_level_cache">false</property>


<!-- Mapeos de las clases de avcd -->
<mapping resource="com/iberia/cargo/cra/interfaces/avcd/Vcdpai.hbm.xml"/>
<mapping resource="com/iberia/cargo/cra/interfaces/avcd/Vcdciu.hbm.xml"/>
<mapping resource="com/iberia/cargo/cra/interfaces/avcd/Vcdawb.hbm.xml"/>
<mapping resource="com/iberia/cargo/cra/interfaces/avcd/VcdawbGas.hbm.xml"/>
<mapping resource="com/iberia/cargo/cra/interfaces/avcd/VcdawbImp.hbm.xml"/>
<mapping resource="com/iberia/cargo/cra/interfaces/avcd/VcdawbPag.hbm.xml"/>
<mapping resource="com/iberia/cargo/cra/interfaces/avcd/VcdawbTar.hbm.xml"/>
<mapping resource="com/iberia/cargo/cra/interfaces/avcd/VcdgenCca.hbm.xml"/>
<mapping resource="com/iberia/cargo/cra/interfaces/avcd/VcdgenDcm.hbm.xml"/>
<mapping resource="com/iberia/cargo/cra/interfaces/avcd/VcdgenCli.hbm.xml"/>
<mapping resource="com/iberia/cargo/cra/interfaces/avcd/VcdgenManTotal.hbm.xml"/>
<mapping resource="com/iberia/cargo/cra/interfaces/avcd/Vcdnat.hbm.xml"/>
<!-- <mapping resource="com/iberia/cargo/cra/interfaces/avcd/Vcdstk.hbm.xml"/>-->
<!-- <mapping resource="com/iberia/cargo/cra/interfaces/avcd/Vcdtac.hbm.xml"/>-->
<mapping resource="com/iberia/cargo/cra/interfaces/avcd/VcdgenAgt.hbm.xml"/>

<!-- Mapeo de los eventos -->
<mapping resource="com/iberia/cargo/cra/interfaces/avcd/Evento.hbm.xml"/>

<!-- Otros mapeos -->
<mapping resource="com/iberia/cargo/cra/general/dao/avcd/AVCD.hbm.xml"/>



</session-factory>
</hibernate-configuration>

try "select new ..." stuff, it must be faster with large result.

BTW try to increase heap limit for JVM (-Xmx256M)

Toad shows the first rows fetched by the database. A table with a billion rows will return in probably the same amount of time.

The reason I asked you to do the query in Java is because I want you to gauge how much time it takes for the entire data set to be (1) materialized by the database, and (2) sent across the network to your app.

Don't try to tune Hibernate until you determine the limits of JDBC itself.