Hi, I am having a pretty weird situation here.

I am using Hibernate 3.1.3 with Sybase 12.

When I run a query returned by the "show_sql = true" parameter on a Sybase app like SQLAdvantage, the results are immediate, and it returns me 46 lines, however, through Hibernate, when I use a Session.createQuery(hql).list() it takes an extremely long time.

It's a somewhat big query and it has some left joins mixed with with inner joins, but it shouldn't be a problem. It takes something like 2 seconds to return me the results on SQLAdv and 5 minutes on Hibernate.

When I set the log level to debug I can see that the query executes pretty fast on hibernate, but it stalls on some point after a few results are processed. After further debugging, I saw that it stalls on ther result row #43, when fetching the 8th column on the line row[i] = queryReturnTypes[i].nullSafeGet( rs, scalarColumns[i], session, null ); on the QueryLoaderClass.

I don't know this happens

Here goes my .hbm.xml

<?xml version="1.0" encoding="UTF-8"?>
<!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="hibernate.connection.driver_class">com.sybase.jdbc2.jdbc.SybDriver</property>
<property name="hibernate.connection.password">123456</property>
<property name="hibernate.connection.url">jdbc:sybase:Tds:desenv:5000/CORPORATIVO</property>
<property name="hibernate.connection.username">marcelom</property>
<property name="hibernate.dialect">org.hibernate.dialect.SybaseDialect</property>
<!--<property name="hibernate.connection.autocommit">true</property>-->
<property name="hibernate.cache.provider_class">org.hibernate.cache.EhCacheProvider</property>
<property name="show_sql">true</property>

<mapping resource="br/gov/capes/webqualis/persistencia/hibernate/GAreaSubarea.hbm.xml" />
<mapping resource="br/gov/capes/webqualis/persistencia/hibernate/GAreasAvaliacao.hbm.xml" />
<mapping resource="br/gov/capes/webqualis/persistencia/hibernate/GComposicaoAreasAvaliacao.hbm.xml" />
<mapping resource="br/gov/capes/webqualis/persistencia/hibernate/GTipoIdentRegistrado.hbm.xml" />
<mapping resource="br/gov/capes/webqualis/persistencia/hibernate/WqlAreaAvaliacaoRVeiculo.hbm.xml" />
<mapping resource="br/gov/capes/webqualis/persistencia/hibernate/WqlAreaEventoRTipoVeiculo.hbm.xml" />
<mapping resource="br/gov/capes/webqualis/persistencia/hibernate/WqlBaseIndexada.hbm.xml" />
<mapping resource="br/gov/capes/webqualis/persistencia/hibernate/WqlEventoClassificacao.hbm.xml" />
<mapping resource="br/gov/capes/webqualis/persistencia/hibernate/WqlTipoBaseIndexada.hbm.xml" />
<mapping resource="br/gov/capes/webqualis/persistencia/hibernate/WqlTipoVeiculoPublicacao.hbm.xml" />
<mapping resource="br/gov/capes/webqualis/persistencia/hibernate/WqlVeiculoPublicacao.hbm.xml" />
<mapping resource="br/gov/capes/webqualis/persistencia/hibernate/WqlVeiculoRIdentRegistrado.hbm.xml" />
<mapping resource="br/gov/capes/webqualis/persistencia/hibernate/WqlEdicoesAnais.hbm.xml" />
<mapping resource="br/gov/capes/webqualis/persistencia/hibernate/PessEndereco.hbm.xml" />
<mapping resource="br/gov/capes/webqualis/persistencia/hibernate/PessEnderecoEmail.hbm.xml" />
<mapping resource="br/gov/capes/webqualis/persistencia/hibernate/PessPapel.hbm.xml" />
<mapping resource="br/gov/capes/webqualis/persistencia/hibernate/PessPapelConsultor.hbm.xml" />
<mapping resource="br/gov/capes/webqualis/persistencia/hibernate/PessPessoa.hbm.xml" />
<mapping resource="br/gov/capes/webqualis/persistencia/hibernate/PessPessoaFisica.hbm.xml" />
<mapping resource="br/gov/capes/webqualis/persistencia/hibernate/PessRelacionamento.hbm.xml" />
<mapping resource="br/gov/capes/webqualis/persistencia/hibernate/PessRepresentanteArea.hbm.xml" />
<mapping resource="br/gov/capes/webqualis/persistencia/hibernate/PessTipoPapel.hbm.xml" />
<mapping resource="br/gov/capes/webqualis/persistencia/hibernate/PessTipoRelacionamento.hbm.xml" />
<mapping resource="br/gov/capes/webqualis/persistencia/hibernate/PessConsultor.hbm.xml" />
<mapping resource="br/gov/capes/webqualis/persistencia/hibernate/PessIdentificadorRegistrado.hbm.xml" />
<mapping resource="br/gov/capes/webqualis/persistencia/hibernate/PessUsuario.hbm.xml" />
<mapping resource="br/gov/capes/webqualis/persistencia/hibernate/WqlAreaAvalRVeicAlter.hbm.xml" />
<mapping resource="br/gov/capes/webqualis/persistencia/hibernate/WqlAreaAvalRVeiculoRetip.hbm.xml" />
<mapping resource="br/gov/capes/webqualis/persistencia/hibernate/WqlClassificacaoRTipoVeic.hbm.xml" />
<mapping resource="br/gov/capes/webqualis/persistencia/hibernate/WqlClassificacaoVeiculo.hbm.xml" />
<mapping resource="br/gov/capes/webqualis/persistencia/hibernate/WqlEventoRRelacionamento.hbm.xml" />
<mapping resource="br/gov/capes/webqualis/persistencia/hibernate/WqlVeiculoTituloAlternativo.hbm.xml" />

<!--
<class-cache class="br.gov.capes.webqualis.persistencia.dto.GAreaSubarea" usage="read-only"/>
<class-cache class="br.gov.capes.webqualis.persistencia.dto.GAreasAvaliacao" usage="read-only"/>
<class-cache class="br.gov.capes.webqualis.persistencia.dto.GComposicaoAreasAvaliacao" usage="read-only"/>
-->
</session-factory>
</hibernate-configuration>

And here goes my query as it is on the java file. PeriodicoTO is a POJO, with no references to other classes

"select new br.gov.capes.webqualis.to.PeriodicoTO("
+ " vep.idVeiculoPublicacao,"
+ " tpv.idTipoVeiculoPublicacao,"
+ " aav.gAreasAvaliacao.identificador,"
+ " aav.gAreasAvaliacao.nomeArea,"
+ " aav.circulacao,"
+ " aav.nivel,"
+ " vep.tituloPadronizado,"
+ " vid.identificadorRegistrado,"
+ " bas.classificacao,"
+ " vep.origem)"
+ " from WqlAreaAvaliacaoRVeiculo aav"
+ " join aav.wqlVeiculoPublicacao vep"
+ " join vep.wqlTipoVeiculoPublicacao tpv"
+ " left join vep.wqlBaseIndexadas bas"
+ " with bas.periodoClassificacao = :periodoClassificacao"
+ " and bas.wqlTipoBaseIndexada.idTipobaseIndexada = :idTipobaseIndexada"
+ " left join vep.wqlVeiculoRIdentRegistrados vid"
+ " with vid.gTipoIdentRegistrado.idTipoIdentificador = :idTipoIdentificador,"
+ " WqlAreaEventoRTipoVeiculo aetv"
+ " where aetv.gAreasAvaliacao.identificador = aav.gAreasAvaliacao.identificador"
+ " and aetv.wqlEventoClassificacao.idEvento = aav.wqlEventoClassificacao.idEvento"
+ " and tpv.idTipoVeiculoPublicacao = :idTipoVeiculoPublicacao"
+ " and aetv.situacao = :situacao";

I've tried something new today, instead of using the set... for the parameters i used fixed ones, and just called

list = getSession.createQuery(hql).list();

and the query run extremely fast. I'm just getting more and more confused...

I'm posting the code for the parameters:

WqlBaseIndexadaDAO baseDao = WqlBaseIndexadaDAOImpl.getInstance();
String periodo = baseDao.findByTipoBaseMaxPeriodo(WqlTipoBaseIndexada.JCR);

Query query = getSession()
.createQuery(hql)
.setParameter(WqlTipoBaseIndexada.PROP_ID_TIPOBASE_INDEXADA,
WqlTipoBaseIndexada.JCR, Hibernate.INTEGER)
.setParameter(GTipoIdentRegistrado.PROP_ID_TIPO_IDENTIFICADOR,
GTipoIdentRegistrado.ISSN, Hibernate.INTEGER)
.setParameter(WqlTipoVeiculoPublicacao.PROP_ID_TIPO_VEICULO_PUBLICACAO,
tipoVeiculo, Hibernate.INTEGER)
.setParameter(WqlVeiculoRIdentRegistrado.PROP_IDENTIFICADOR_REGISTRADO,
identificador, Hibernate.STRING)
.setParameter(WqlAreaEventoRTipoVeiculo.PROP_SITUACAO,
status, Hibernate.STRING)
.setParameter(WqlBaseIndexada.PROP_PERIODO_CLASSIFICACAO,
periodo, Hibernate.STRING);


List list = query.list();

BTW, I also tried to use the setInteger and setInteger methods, with no success.

Here's part of the debug trace. I've change the driver for jTDS and the lag is now "only" one minute

#webqualis# DEBUG [Loader ] result set row: 16 15:18:16,415 [main]
#webqualis# DEBUG [Loader ] result row: 15:18:16,415 [main]
#webqualis# DEBUG [IntegerType] returning '72' as column: col_0_0_ 15:18:16,415 [main]
#webqualis# DEBUG [IntegerType] returning '1' as column: col_1_0_ 15:18:16,415 [main]
#webqualis# DEBUG [ShortType ] returning '6' as column: col_2_0_ 15:18:16,415 [main]
#webqualis# DEBUG [StringType] returning 'CIÊNCIAS BIOLÓGICAS I ' as column: col_3_0_ 15:18:16,415 [main]
#webqualis# DEBUG [StringType] returning 'N' as column: col_4_0_ 15:18:16,415 [main]
#webqualis# DEBUG [StringType] returning 'B' as column: col_5_0_ 15:18:16,415 [main]
#webqualis# DEBUG [StringType] returning 'Acta Amazonica' as column: col_6_0_ 15:18:16,415 [main]
#webqualis# DEBUG [StringType] returning '0044-5967' as column: col_7_0_ 15:18:16,415 [main]
#webqualis# DEBUG [StringType] returning null as column: col_8_0_ 15:18:16,415 [main]
#webqualis# DEBUG [StringType] returning 'azul' as column: col_9_0_ 15:18:16,415 [main]
#webqualis# DEBUG [Loader ] result set row: 17 15:18:16,415 [main]
#webqualis# DEBUG [Loader ] result row: 15:18:16,415 [main]
#webqualis# DEBUG [IntegerType] returning '72' as column: col_0_0_ 15:18:16,415 [main]
#webqualis# DEBUG [IntegerType] returning '1' as column: col_1_0_ 15:18:16,415 [main]
#webqualis# DEBUG [ShortType ] returning '6' as column: col_2_0_ 15:18:16,415 [main]
#webqualis# DEBUG [StringType] returning 'CIÊNCIAS BIOLÓGICAS I ' as column: col_3_0_ 15:18:16,415 [main]
#webqualis# DEBUG [StringType] returning 'N' as column: col_4_0_ 15:18:16,415 [main]
#webqualis# DEBUG [StringType] returning 'B' as column: col_5_0_ 15:18:16,415 [main]
#webqualis# DEBUG [StringType] returning 'Acta Amazonica' as column: col_6_0_ 15:18:16,415 [main]
#webqualis# DEBUG [StringType] returning '0044-5967' as column: col_7_0_ 15:18:16,415 [main]
#webqualis# DEBUG [StringType] returning null as column: col_8_0_ 15:18:16,415 [main]
#webqualis# DEBUG [StringType] returning 'azul' as column: col_9_0_ 15:18:16,415 [main]
************************************************************************
here is where the problem happens, can you see the 1 minute plus lag that occurred? 15:18:16 -> 15:19:31
***********************************************************************
#webqualis# DEBUG [Loader ] result set row: 18 15:19:31,749 [main]
#webqualis# DEBUG [Loader ] result row: 15:19:31,749 [main]
#webqualis# DEBUG [IntegerType] returning '72' as column: col_0_0_ 15:19:31,749 [main]
#webqualis# DEBUG [IntegerType] returning '1' as column: col_1_0_ 15:19:31,749 [main]
#webqualis# DEBUG [ShortType ] returning '6' as column: col_2_0_ 15:19:31,749 [main]

We have been struggling with this issue and a co-worker of mine came up with a work-around. Instead of using Query.list() use Query.scroll() to get back a ScrollableResultSet. In our testing, the slowness was in the generation of the List after the query finished. The query was super fast and the List was built quite slowly. Using .scroll() sped this up considerably.

We are using Hibernate 3.1

Well, good to know that there's at least one workaround, but we're avoiding now to use query and are mostly using Criteria .

Thanl you very much