The problem is that I have a many-to-one with not-found=ignore. When I do
a masive query, I get a an extra select for each row. if I take out the not-found attribute, then I don't get the extra query.

The extra queries are very significative since we are going to process thousands of rows.
The offending queries are from SptMontoSaldo

Need help with Hibernate? Read this first:
http://www.hibernate.org/ForumMailingli ... AskForHelp
read it
Hibernate version: 3.1


Mapping documents:

SptPension.hbm.xml
<class
name="mx.gob.imss.cia.spes.integracion.hbm.SptPension"
table="SPT_PENSION"
lazy="true"
>
......
<many-to-one
name="sptMontoSaldo"
class="mx.gob.imss.cia.spes.integracion.hbm.SptMontoSaldo"
update="false"
insert="false"
not-found="ignore"
>
<column name="ID_NSS" />
<column name="ID_ANTECEDENTE" />
</many-to-one>
....
SptMontoSaldo.hbm.xml
<class
name="mx.gob.imss.cia.spes.integracion.hbm.SptMontoSaldo"
table="SPT_MONTO_SALDO"
lazy="true"
>
.....
<one-to-one
name="sptPension"
class="mx.gob.imss.cia.spes.integracion.hbm.SptPension"
property-ref="sptMontoSaldo"
/>


Code between sessionFactory.openSession() and session.close():
Session s = sessionFactory.openSession();
Transaction tx = null;
long time = System.currentTimeMillis();
try {
// tx = s.beginTransaction();
Criteria c = s.createCriteria(SptPension.class);
ScrollableResults pensiones = c.scroll(ScrollMode.FORWARD_ONLY);

int cont = 0;
while(pensiones.next()) {
log.debug("obten pension siguiente");
SptPension pension = (SptPension)pensiones.get(0);
}
// tx.commit();
}
catch (Exception e) {
if (tx != null) tx.rollback();
throw e;
}
finally {
s.close();
}

Full stack trace of any exception that occurs:
No exception
Name and version of the database you are using:
Oracle
The generated SQL (show_sql=true):

Debug level Hibernate log excerpt:

2006-03-23 16:22:58,671 [main] DEBUG org.hibernate.event.def.DefaultLoadEventListener - loading entity: [mx.gob.imss.cia.spes.integracion.hbm.SptMontoSaldo#component[idNss,idAntecedente]{idNss=1234000019 , idAntecedente=01}]
2006-03-23 16:22:58,671 [main] DEBUG org.hibernate.event.def.DefaultLoadEventListener - loading entity: [mx.gob.imss.cia.spes.integracion.hbm.SptMontoSaldo#component[idNss,idAntecedente]{idNss=1234000019 , idAntecedente=01}]
2006-03-23 16:22:58,671 [main] DEBUG org.hibernate.event.def.DefaultLoadEventListener - attempting to resolve: [mx.gob.imss.cia.spes.integracion.hbm.SptMontoSaldo#component[idNss,idAntecedente]{idNss=1234000019 , idAntecedente=01}]
2006-03-23 16:22:58,671 [main] DEBUG org.hibernate.event.def.DefaultLoadEventListener - attempting to resolve: [mx.gob.imss.cia.spes.integracion.hbm.SptMontoSaldo#component[idNss,idAntecedente]{idNss=1234000019 , idAntecedente=01}]
2006-03-23 16:22:58,671 [main] DEBUG org.hibernate.event.def.DefaultLoadEventListener - object not resolved in any cache: [mx.gob.imss.cia.spes.integracion.hbm.SptMontoSaldo#component[idNss,idAntecedente]{idNss=1234000019 , idAntecedente=01}]
2006-03-23 16:22:58,671 [main] DEBUG org.hibernate.event.def.DefaultLoadEventListener - object not resolved in any cache: [mx.gob.imss.cia.spes.integracion.hbm.SptMontoSaldo#component[idNss,idAntecedente]{idNss=1234000019 , idAntecedente=01}]
2006-03-23 16:22:58,671 [main] DEBUG org.hibernate.persister.entity.AbstractEntityPersister - Fetching entity: [mx.gob.imss.cia.spes.integracion.hbm.SptMontoSaldo#component[idNss,idAntecedente]{idNss=1234000019 , idAntecedente=01}]
2006-03-23 16:22:58,671 [main] DEBUG org.hibernate.persister.entity.AbstractEntityPersister - Fetching entity: [mx.gob.imss.cia.spes.integracion.hbm.SptMontoSaldo#component[idNss,idAntecedente]{idNss=1234000019 , idAntecedente=01}]
2006-03-23 16:22:58,671 [main] DEBUG org.hibernate.loader.Loader - loading entity: [mx.gob.imss.cia.spes.integracion.hbm.SptMontoSaldo#component[idNss,idAntecedente]{idNss=1234000019 , idAntecedente=01}]
2006-03-23 16:22:58,671 [main] DEBUG org.hibernate.loader.Loader - loading entity: [mx.gob.imss.cia.spes.integracion.hbm.SptMontoSaldo#component[idNss,idAntecedente]{idNss=1234000019 , idAntecedente=01}]
2006-03-23 16:22:58,671 [main] DEBUG org.hibernate.jdbc.AbstractBatcher - about to open PreparedStatement (open PreparedStatements: 1, globally: 1)
2006-03-23 16:22:58,671 [main] DEBUG org.hibernate.jdbc.AbstractBatcher - about to open PreparedStatement (open PreparedStatements: 1, globally: 1)
2006-03-23 16:22:58,671 [main] DEBUG org.hibernate.SQL - select sptmontosa0_.ID_NSS as ID1_125_1_, sptmontosa0_.ID_ANTECEDENTE as ID2_125_1_, sptmontosa0_.IMP_MONTO_CONST_RENTA_VIT as IMP3_125_1_, sptmontosa0_.IMP_MONTO_CONST_SEGURO_SOBREVI as IMP4_125_1_, sptmontosa0_.IMP_DIF_MONTO_CONSTITUTIVO as IMP5_125_1_, sptmontosa0_.IMP_SALDO_AFORE_RCV as IMP6_125_1_, sptmontosa0_.IMP_SALDO_VIVIENDA_INFONAVIT as IMP7_125_1_, sptmontosa0_.IMP_SUMA_ASEGURADA as IMP8_125_1_, sptmontosa0_.IMP_RESERVA_MATEMATICA_BI as IMP9_125_1_, sptmontosa0_.IMP_RSVA_MAT_SEGUNDAS_NUPCIAS as IMP10_125_1_, sptmontosa0_.IMP_DEVOL_PRORROGAS_NO_PAGADAS as IMP11_125_1_, sptmontosa0_.IMP_TOTAL_PAGOS_INDEBIDOS as IMP12_125_1_, sptmontosa0_.IMP_REFORMA_LEY as IMP13_125_1_, sptmontosa0_.IMP_FAVOR_IMSS as IMP14_125_1_, sptmontosa0_.POR_IMSS as POR15_125_1_, sptmontosa0_.POR_GOBIERNO_FEDERAL as POR16_125_1_, sptmontosa0_.FEC_DEVOLUCION as FEC17_125_1_, sptmontosa0_.FEC_CARGA as FEC18_125_1_, sptmontosa0_.IMP_DEVOL_AGUINALDO_PRORROGA as IMP19_125_1_, sptmontosa0_.IMP_MC_CAMBIO_ASEGURADORA as IMP20_125_1_, sptmontosa0_.IMP_FAVOR_ASEGURADORA as IMP21_125_1_, sptmontosa0_.IMP_GOBIERNO_FEDERAL as IMP22_125_1_, sptmontosa0_.IND_INCUMPLIMIENTO_TIEMPO as IND23_125_1_, sptmontosa0_.ID_ESTADO_FONDO_ESPECIAL as ID24_125_1_, sptpension1_.ID_ANTECEDENTE as ID1_134_0_, sptpension1_.ID_NSS as ID2_134_0_, sptpension1_.IMP_SALARIO_DIARIO as IMP3_134_0_, sptpension1_.FEC_INICIO_PENSION as FEC4_134_0_, sptpension1_.FEC_INICIO_PAGO as FEC5_134_0_, sptpension1_.FEC_VENCIMIENTO_PENSION as FEC6_134_0_, sptpension1_.FEC_INICIO_AJUSTE as FEC7_134_0_, sptpension1_.FEC_ALTA_PENSION as FEC8_134_0_, sptpension1_.NUM_SEMANAS_RECONOCIDA as NUM9_134_0_, sptpension1_.NUM_SEMANAS_RECONOCIDA_EXT as NUM10_134_0_, sptpension1_.NUM_SEMANAS_COTIZADAS_31121990 as NUM11_134_0_, sptpension1_.FEC_BAJA_PENSION as FEC12_134_0_, sptpension1_.IND_INVALIDEZ_FICTICIA as IND13_134_0_, sptpension1_.CVE_TIPO_GARANTIA as CVE14_134_0_, sptpension1_.FEC_REFORMA_LEY as FEC15_134_0_, sptpension1_.FEC_MODIFICACION as FEC16_134_0_, sptpension1_.POR_VALUACION as POR17_134_0_, sptpension1_.IMP_CUANTIA_MENSUAL_IPT_REEVAL as IMP18_134_0_, sptpension1_.IMP_PENSION_REEVALUACION as IMP19_134_0_, sptpension1_.CVE_PERIODO_NOMINA as CVE20_134_0_, sptpension1_.IND_FINIQUITO as IND21_134_0_, sptpension1_.CVE_USUARIO as CVE22_134_0_, sptpension1_.ID_SOLICITUD_PENSION as ID23_134_0_, sptpension1_.ID_REGIMEN as ID24_134_0_, sptpension1_.ID_CERTIFICADO_DERECHOS as ID25_134_0_, sptpension1_.ID_MODALIDAD as ID26_134_0_, sptpension1_.ID_ESQUEMA as ID27_134_0_, sptpension1_.ID_INCIDENCIA as ID28_134_0_, sptpension1_.ID_ART_BASE_NEGATIVA_CONS as ID29_134_0_, sptpension1_.NUM_FOLIO_DICTAMEN as NUM30_134_0_, sptpension1_.ID_TIPO_JUICIO as ID31_134_0_, sptpension1_.ID_RAMA as ID32_134_0_, sptpension1_.ID_REFORMA_LEY as ID33_134_0_, sptpension1_.ID_TIPO_MOVIMIENTO as ID34_134_0_, sptpension1_.ID_TIPO_PENSION as ID35_134_0_, sptpension1_.CVERIESGOTRABAJO as CVERIES36_134_0_, sptpension1_.ID_ANTECEDENTE_CAMBIO_RAMA_PE as ID37_134_0_, sptpension1_.ID_NSS_CAMBIO_RAMA_PE as ID38_134_0_, sptpension1_.ID_ANTECEDENTE_DERIVADA as ID39_134_0_, sptpension1_.ID_NSS_DERIVADA as ID40_134_0_, sptpension1_.ID_TIPO_REEVALUACION as ID41_134_0_, sptpension1_.ID_DICTAMEN_LAUDO as ID42_134_0_, sptpension1_.ID_FORMA_PAGO_PENSION as ID43_134_0_ from SPT_MONTO_SALDO sptmontosa0_, SPT_PENSION sptpension1_ where sptmontosa0_.ID_NSS=sptpension1_.ID_NSS(+) and sptmontosa0_.ID_ANTECEDENTE=sptpension1_.ID_ANTECEDENTE(+) and sptmontosa0_.ID_NSS=? and sptmontosa0_.ID_ANTECEDENTE=?

Hello Friend:

I had the same problem than your, i was asking to Hibernate Forum, some expert say me:

Use "inner join" at hibernate:

select p.someAttribute, s.somAttribute

s.someattribute, p.someattribute
from sptMontoSaldo s inner join s.sptoPension as p



That is very efficent, you can try it.

Bueno, creo que hablas castellano, te explico en castellano mejor:

Ocupe un HQL tal que te retorne los datos necesarios es mucho mas eficiente que te retorne un Objeto entero, lo que reduce la cantidad de acceso a base de datos.

Ocupe la propiedad inner join de HQL, y te solucionara el problema.

Atte.

Gracias. Efectivamente si hablo español. Espero que no te importe si mantengo la conversación en Ingles por si otras personas pueden ayudarme.

I am using a Criteria and I already tried setting a fetch mode to eagerly bring the row. I don't need it but I figured out that is was better to bringing it at the begining than attempting to fetch it once for every row.

P.D Sorry for the bad subject line, I submitted the message before it was ready.