Hibernate Community • View topic - Need help with left join fetch not behaving as I expect

View unanswered posts | View active topics

Board index » Hibernate & Java Persistence » Hibernate Users

All times are UTC - 5 hours [ DST ]

Need help with left join fetch not behaving as I expect

Page 1 of 1

[ 10 posts ]

Previous topic | Next topic

Author

Message

olddave

Post subject: Need help with left join fetch not behaving as I expect

Posted: Thu Jan 29, 2009 7:25 am

Newbie

Joined: Wed Jan 28, 2009 12:00 pm
Posts: 12

Hi,

I have read chapter 19 of the user guide, lots of posts found via google, but maybe I have misunderstood something as I do not get the behaviour I expect. My hbm.xml is generated via Teneo and I set to use the PROXY, so all appropriate 'lazy' are set to true or proxy dependent on association type. I also set max_fetch_depth=0.

The application is only one that will be built on this model and in this case it is largely read only and many of the entities will always be empty. My aim is to only fetch 1to1 and manyto1 elements when I control it through the use of inner join fetch or left join fetch. So it does not needlessly fetch associated entities through an association that I know will always be empty. I need to control absolutely what it fetches through associations.

Hibernate version:

3.2

Mapping documents:

Just a part of them, 400 entities in the full document. Just an example of each type of association to an entity that is being fetched via select rather than the left join fetch as the HQL would suggest it should.

Code:

   <class name="com.stpenable.gensec.impl.InstrumentDomainImpl" entity-name="InstrumentDomain" abstract="false" lazy="true" table="`instrumentdomain`" proxy="com.stpenable.gensec.InstrumentDomain">
      <meta attribute="eclassName">InstrumentDomain</meta>
      <meta attribute="epackage">http://www.stpenable.com/gensec</meta>
      <id name="id" type="int" unsaved-value="-1">
         <column not-null="true" unique="false" name="`id`"/>
         <generator class="identity"/>
      </id>
...
      <one-to-one name="issueData" entity-name="IssueData" cascade="merge,persist,save-update,lock,refresh" lazy="proxy"/>
      <one-to-one name="underlyingCount" entity-name="UnderlyingCount" cascade="merge,persist,save-update,lock,refresh" lazy="proxy" property-ref="instrumentDomain"/>
      <one-to-one name="lastCAE" entity-name="LastCAE" cascade="merge,persist,save-update,lock,refresh" lazy="proxy"/>
      <one-to-one name="debtIssueData" entity-name="DebtIssueData" cascade="merge,persist,save-update,lock,refresh" lazy="proxy"/>
      <many-to-one name="issuerClass" entity-name="IssuerClass" lazy="proxy" foreign-key="instrumentdomain_issuerclass" insert="true" update="true" not-null="false">
         <column not-null="false" unique="false" name="`issuerclass`"/>
      </many-to-one>
   </class>
   <class name="com.stpenable.gensec.impl.IssueDataImpl" entity-name="IssueData" abstract="false" lazy="true" table="`issuedata`" proxy="com.stpenable.gensec.IssueData">
      <meta attribute="eclassName">IssueData</meta>
      <meta attribute="epackage">http://www.stpenable.com/gensec</meta>
      <id name="id" type="int" unsaved-value="-1">
         <column not-null="true" unique="false" name="`id`"/>
         <generator class="identity"/>
      </id>
...
      <one-to-one name="instrumentDomain" entity-name="InstrumentDomain" foreign-key="issuedata_instrumentdomain" cascade="all" lazy="proxy" constrained="true"/>
...
   </class>
   <class name="com.stpenable.gensec.impl.DenominationImpl" entity-name="Denomination" abstract="false" lazy="true" table="`denomination`" proxy="com.stpenable.gensec.Denomination">
      <meta attribute="eclassName">Denomination</meta>
      <meta attribute="epackage">http://www.stpenable.com/gensec</meta>
      <id name="id" type="int" unsaved-value="-1">
         <column not-null="true" unique="false" name="`id`"/>
         <generator class="identity"/>
      </id>
...
      <many-to-one name="instrumentDomain" entity-name="InstrumentDomain" lazy="proxy" foreign-key="denomination_instrmentdomain" insert="true" update="true" not-null="false">
         <column not-null="false" unique="false" name="`instrumentdomain`"/>
      </many-to-one>
        </class>

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

Code:

      Session session = HibernateUtil.getCurrentSession();
      Transaction tx = session.beginTransaction();

      try
      {
         // construct search
         String queryString = "select inst from InstrumentDomain inst" +
               " left join fetch inst.instrumentIdentifier ident" +
               " left join fetch ident.schemeInfo info" +
               " left join fetch inst.instrumentType as itype" +
               " left join fetch inst.denomination denom" +
               " left join fetch inst.industryIdentifier indid" +
               " left join fetch inst.lastCAE lastCAE" +
               " left join fetch inst.issuerClass issr" +
               " left join fetch inst.issueData issd" +
               " left join fetch inst.debtIssueData dissd" +
               " left join fetch inst.underlyingInstrument undly" +
               " left join fetch issr.location loc" +
               " where itype.id in (46,48,49,150,151,185,432,438,439,443,473)" +
               " and (inst.name like ? or inst.shortName like ? or ( ident.code like ? and info.id in (5,10,14,15,16)))";
   
         // build query
         Query query = session.createQuery(queryString);
         int arg = 0;
         if(search.indexOf("%")==-1)
            search += "%";
         query.setString(arg++, search);
         query.setString(arg++, search);
         query.setString(arg++, search);
         query.setCacheable(true);
         query.setReadOnly(true);

         // then create EquityResultRows with the constituent objects
         try {
            List<InstrumentDomain> results = query.list();
            if (results != null)
            {
               for (InstrumentDomain instrument : results)
               {
                  // check type
                  InstrumentType type = instrument.getInstrumentType();
                  if (type == null || !EQUITIES_SET.contains(type.getId()))
                  {
                     //System.out.println("type=" + type.getId() + " not an equity");
                     nonEquities.add(instrument);
                     continue;
                  }
   
                  // degenerate case
                  EList<MarketIdentifier> mids = instrument.getInstrumentDomainMarketIdentifier();
                  if (mids == null || mids.size() == 0)
                  {
                     // no market ids
                     result.addEquityResultRow(new EquityResultRow(instrument, null));
                     continue;
                  }
   
                  // for each market id
                  for (MarketIdentifier marketId : mids)
                     result.addEquityResultRow(new EquityResultRow(instrument, marketId));
               }      
            }
         } catch (ClassCastException cce) {
            Log.error("Hibernate cast error: ", cce);
         }

         // commit transaction
         tx.commit();
      }
      catch (RuntimeException e)
      {
         Log.error("hibernate error: ", e);

         tx.rollback();
         throw e;
      }

Name and version of the database you are using:
MySQL 5.1.22-rc

The generated SQL (show_sql=true):

lots of these
Hibernate: select issuedata0_.`id` as id1_181_0_, issuedata0_.issuanceSubclass as issuance2_181_0_, issuedata0_.indexDomain as indexDom3_181_0_, issuedata0_.interestRateClass as interest4_181_0_, issuedata0_.foreignExchangeDomain as foreignE5_181_0_, issuedata0_.industrySpecificClass as industry6_181_0_, issuedata0_.economicClass as economic7_181_0_, issuedata0_.demographicClass as demograp8_181_0_, issuedata0_.`issuemarkettype` as issuemar9_181_0_, issuedata0_.`issuerref` as issuerref10_181_0_, issuedata0_.`liquidationstatustype` as liquida11_181_0_, issuedata0_.`offertype` as offertype12_181_0_, issuedata0_.`programtype` as program13_181_0_, issuedata0_.`votingrightstype` as votingr14_181_0_, issuedata0_.`accrualbasistype` as accrual15_181_0_, issuedata0_.`accrualconventiontype` as accrual16_181_0_, issuedata0_.`indicatorstype` as indicat17_181_0_, issuedata0_.`modificationtype` as modific18_181_0_, issuedata0_.`agreedrestriction` as agreedr19_181_0_, issuedata0_.`allowedindebtedness` as allowed20_181_0_, issuedata0_.`announcementdate` as announc21_181_0_, issuedata0_.`auctiondate` as auction22_181_0_, issuedata0_.`closingdate` as closing23_181_0_, issuedata0_.`createdate` as createdate24_181_0_, issuedata0_.`defaultclause` as default25_181_0_, issuedata0_.`facevalue` as facevalue26_181_0_, issuedata0_.`issuedate` as issuedate27_181_0_, issuedata0_.`marginable` as marginable28_181_0_, issuedata0_.`marginablemaxlimit` as margina29_181_0_, issuedata0_.`marginableminlimit` as margina30_181_0_, issuedata0_.`modificationdatetime` as modific31_181_0_, issuedata0_.`modifieddate` as modifie32_181_0_, issuedata0_.`negativepledge` as negativ33_181_0_, issuedata0_.`refrainfromacts` as refrain34_181_0_, issuedata0_.`settlementdate` as settlem35_181_0_, issuedata0_.`underlyingcount` as underly36_181_0_, issuedata0_.`useofproceeds` as useofpr37_181_0_, issuedata0_.`validity` as validity38_181_0_ from `issuedata` issuedata0_ where issuedata0_.`id`=?
Hibernate: select underlying0_.`id` as id1_329_0_, underlying0_.`calculationtype` as calculat2_329_0_, underlying0_.`indicatorstype` as indicato3_329_0_, underlying0_.`marketcenter` as marketce4_329_0_, underlying0_.`quantitydescriptiontype` as quantity5_329_0_, underlying0_.`quantitytype` as quantity6_329_0_, underlying0_.`valuationref` as valuatio7_329_0_, underlying0_.`valuationtype` as valuatio8_329_0_, underlying0_.`unittype` as unittype9_329_0_, underlying0_.instrumentDomain as instrum10_329_0_, underlying0_.`datetime` as datetime11_329_0_, underlying0_.`previousdatetime` as previou12_329_0_, underlying0_.`rank` as rank13_329_0_, underlying0_.`underlyingcount` as underly14_329_0_, underlying0_.`unitsize` as unitsize15_329_0_ from `underlyingcount` underlying0_ where underlying0_.instrumentDomain=?
Hibernate: select lastcae0_.`id` as id1_196_0_, lastcae0_.`caetype` as caetype2_196_0_, lastcae0_.`datetime` as datetime3_196_0_ from `lastcae` lastcae0_ where lastcae0_.`id`=?
Hibernate: select debtissued0_.`id` as id1_92_0_, debtissued0_.convertible as converti2_92_0_, debtissued0_.`debtindicatorstype` as debtindi3_92_0_, debtissued0_.`striptype` as striptype4_92_0_, debtissued0_.`strippbleinstrmentidentifier` as strippbl5_92_0_, debtissued0_.`coupontype` as coupontype6_92_0_, debtissued0_.`createdate` as createdate7_92_0_, debtissued0_.`modifieddate` as modified8_92_0_, debtissued0_.`overallotment` as overallo9_92_0_, debtissued0_.`strippable` as strippable10_92_0_, debtissued0_.`subscriptionduration` as subscri11_92_0_, debtissued0_.`subscriptionend` as subscri12_92_0_, debtissued0_.`subscriptionminval` as subscri13_92_0_, debtissued0_.`subscriptionstart` as subscri14_92_0_ from `debtissuedata` debtissued0_ where debtissued0_.`id`=?
Hibernate: select instrument0_.`id` as id1_171_0_, instrument0_.`full` as full2_171_0_, instrument0_.`scheme` as scheme3_171_0_, instrument0_.`short` as short4_171_0_ from `instrumenttype` instrument0_ where instrument0_.`id`=?

followed by lots of these

Hibernate: select instrument0_.`instrumentdomain` as instrume1_1_, instrument0_.`marketidentifier` as marketid2_1_, marketiden1_.`id` as id1_207_0_, marketiden1_.`previouscode` as previous2_207_0_, marketiden1_.`marketstatustype` as marketst3_207_0_, marketiden1_.`markettype` as markettype4_207_0_, marketiden1_.`marketconditions` as marketco5_207_0_, marketiden1_.`code` as code6_207_0_, marketiden1_.`duration` as duration7_207_0_, marketiden1_.`end` as end8_207_0_, marketiden1_.`name` as name9_207_0_, marketiden1_.`scheme` as scheme10_207_0_, marketiden1_.`start` as start11_207_0_ from `instrmentdminmrketidentifier` instrument0_ left outer join `marketidentifier` marketiden1_ on instrument0_.`marketidentifier`=marketiden1_.`id` where instrument0_.`instrumentdomain`=?
Hibernate: select denominati0_.`instrumentdomain` as instrum11_1_, denominati0_.`id` as id1_1_, denominati0_.`id` as id1_101_0_, denominati0_.`calculationtype` as calculat2_101_0_, denominati0_.`indicatorstype` as indicato3_101_0_, denominati0_.`marketcenter` as marketce4_101_0_, denominati0_.`quantitydescirptiontype` as quantity5_101_0_, denominati0_.`quantitytype` as quantity6_101_0_, denominati0_.`valuationref` as valuatio7_101_0_, denominati0_.`valuationtype` as valuatio8_101_0_, denominati0_.`crossrate` as crossrate9_101_0_, denominati0_.`currency` as currency10_101_0_, denominati0_.`instrumentdomain` as instrum11_101_0_, denominati0_.`datetime` as datetime12_101_0_, denominati0_.`denomination` as denomin13_101_0_, denominati0_.`increment` as increment14_101_0_, denominati0_.`minval` as minval15_101_0_, denominati0_.`size` as size16_101_0_, denominati0_.`debtissuedata` as debtiss17_101_0_ from `denomination` denominati0_ where denominati0_.`instrumentdomain`=?
Hibernate: select instrument0_.`instrumentdomain` as instrum12_1_, instrument0_.`id` as id1_1_, instrument0_.`id` as id1_168_0_, instrument0_.`name` as name2_168_0_, instrument0_.`instrumentstatustype` as instrume3_168_0_, instrument0_.`scopetype` as scopetype4_168_0_, instrument0_.`tranche` as tranche5_168_0_, instrument0_.`marketstatustype` as marketst6_168_0_, instrument0_.`markettype` as markettype7_168_0_, instrument0_.`segmenttype` as segmentt8_168_0_, instrument0_.`schemeinfo` as schemeinfo9_168_0_, instrument0_.`country` as country10_168_0_, instrument0_.`currency` as currency11_168_0_, instrument0_.`instrumentdomain` as instrum12_168_0_, instrument0_.`background` as background13_168_0_, instrument0_.`code` as code14_168_0_, instrument0_.`comment` as comment15_168_0_, instrument0_.`createdate` as createdate16_168_0_, instrument0_.`end` as end17_168_0_, instrument0_.`marketconditions` as marketc18_168_0_, instrument0_.`modifieddate` as modifie19_168_0_, instrument0_.`segmentidentifiercode` as segment20_168_0_, instrument0_.`start` as start21_168_0_, instrument0_.`issuancesubclass` as issuanc22_168_0_, instrument0_.`corporatechangesubclass` as corpora23_168_0_ from `instrumentidentifier` instrument0_ where instrument0_.`instrumentdomain`=?
Hibernate: select industryid0_.`instrumentdomain` as instrume8_1_, industryid0_.`id` as id1_1_, industryid0_.`id` as id1_162_0_, industryid0_.`industryidentifiertype` as industry2_162_0_, industryid0_.`code` as code3_162_0_, industryid0_.`end` as end4_162_0_, industryid0_.`name` as name5_162_0_, industryid0_.`start` as start6_162_0_, industryid0_.`validity` as validity7_162_0_, industryid0_.`instrumentdomain` as instrume8_162_0_ from `industryidentifier` industryid0_ where industryid0_.`instrumentdomain`=?
Hibernate: select location0_.`issuerclass` as issuerc19_1_, location0_.`id` as id1_1_, location0_.`id` as id1_200_0_, location0_.`locationtype` as location2_200_0_, location0_.`country` as country3_200_0_, location0_.`address` as address4_200_0_, location0_.`city` as city5_200_0_, location0_.`end` as end6_200_0_, location0_.`faxnumber` as faxnumber7_200_0_, location0_.`municipality` as municipa8_200_0_, location0_.`postalcode` as postalcode9_200_0_, location0_.`postalcodescheme` as postalc10_200_0_, location0_.`regionname` as regionname11_200_0_, location0_.`start` as start12_200_0_, location0_.`stateorprovincename` as stateor13_200_0_, location0_.`telephonenumber` as telepho14_200_0_, location0_.`websiteurl` as websiteurl15_200_0_, location0_.`issuedata` as issuedata16_200_0_, location0_.`businessclass` as busines17_200_0_, location0_.`counterpartyclass` as counter18_200_0_, location0_.`issuerclass` as issuerc19_200_0_ from `location` location0_ where location0_.`issuerclass`=?
Hibernate: select locationty0_.`id` as id1_201_0_, locationty0_.`full` as full2_201_0_, locationty0_.`scheme` as scheme3_201_0_, locationty0_.`short` as short4_201_0_ from `locationtype` locationty0_ where locationty0_.`id`=?
Hibernate: select country0_.`id` as id1_77_0_, country0_.`country` as country2_77_0_, country0_.`scheme` as scheme3_77_0_ from `country` country0_ where country0_.`id`=?

I know the 'how' is default select fetching, but being set to lazy=true means it should not be fetched until accessed, many of those being fetched are not accessed. This 'how' behaviour should be overridden by my HQL anyway and I think the max_fetch_depth is also overriden by th eHQL. But I have tried it set to 1 , max_fetch_depth=1 and it still does all the extra selects. The 'when' is all set to lazy.

So the question is, why is it doing the n+1 select when I have explicitly told it not to fetch using max_fetch_depth=0 and explicitly told it to use a left join fetch ( for some I know, not all in the SQL )? At the moment the application is unusable due to all these selects.

Thx.

David

Top

olddave

Post subject: A different tactic

Posted: Fri Jan 30, 2009 9:59 am

Newbie

Joined: Wed Jan 28, 2009 12:00 pm
Posts: 12

Hi,

Since I need to get an acceptable level of performance or abandon Hibernate I tried something else. For all of the 'extra' selects I was getting I annotated the xsd Teneo uses to generate my hbm.xml so that I used fetch="join" for those tables. My reasoning being if I cannot prevent Hibernate fetching these unneeded entities ( despite using lazy=true or lazy="proxy") maybe I could change the 'how' to include them in a left outer join.

This had mixed results. For one query it included an entity (currency) that has an association with 3 tables in the query, it included joins to 2 of them and ignore the 3rd! So this then cause a n+1 set of selects for that missed table.

Here is the log

Code:

Hibernate: select equitypric0_.`id` as id1_125_0_, debtissued3_.`id` as id1_92_1_, underlying8_.`id` as id1_329_2_, currency7_.`id` as id1_83_3_, instrument4_.`id` as id1_168_4_, currency6_.`id` as id1_83_5_, issuedata2_.`id` as id1_181_6_, denominati5_.`id` as id1_101_7_, issuerclas9_.`id` as id1_191_8_, instrument1_.`id` as id1_165_9_, equitypric0_.`instrumentdomain` as instrume2_125_0_, equitypric0_.`altclose` as altclose3_125_0_, equitypric0_.`altclosegrade` as altclose4_125_0_, equitypric0_.`altopen` as altopen5_125_0_, equitypric0_.`altopengrade` as altopeng6_125_0_, equitypric0_.`ask` as ask7_125_0_, equitypric0_.`askgrade` as askgrade8_125_0_, equitypric0_.`asksize` as asksize9_125_0_, equitypric0_.`bid` as bid10_125_0_, equitypric0_.`bidgrade` as bidgrade11_125_0_, equitypric0_.`bidsize` as bidsize12_125_0_, equitypric0_.`blocktrades` as blocktr13_125_0_, equitypric0_.`blocktradesgrade` as blocktr14_125_0_, equitypric0_.`blockvolume` as blockvo15_125_0_, equitypric0_.`blockvolumegrade` as blockvo16_125_0_, equitypric0_.`capitalgain` as capital17_125_0_, equitypric0_.`capitalgaingrade` as capital18_125_0_, equitypric0_.`close` as close19_125_0_, equitypric0_.`closegrade` as closegrade20_125_0_, equitypric0_.`deliveredtime` as deliver21_125_0_, equitypric0_.`high` as high22_125_0_, equitypric0_.`highgrade` as highgrade23_125_0_, equitypric0_.`low` as low24_125_0_, equitypric0_.`lowgrade` as lowgrade25_125_0_, equitypric0_.`mid` as mid26_125_0_, equitypric0_.`midgrade` as midgrade27_125_0_, equitypric0_.`mscigrossdailytrloc` as mscigro28_125_0_, equitypric0_.`mscigrossdailytrusd` as mscigro29_125_0_, equitypric0_.`mscinetdailytrloc` as mscinet30_125_0_, equitypric0_.`mscinetdailytrusd` as mscinet31_125_0_, equitypric0_.`offer` as offer32_125_0_, equitypric0_.`offergrade` as offergrade33_125_0_, equitypric0_.`officialhigh` as officia34_125_0_, equitypric0_.`officialhighgrade` as officia35_125_0_, equitypric0_.`officiallow` as officia36_125_0_, equitypric0_.`officiallowgrade` as officia37_125_0_, equitypric0_.`open` as open38_125_0_, equitypric0_.`opengrade` as opengrade39_125_0_, equitypric0_.`previousclosedate` as previou40_125_0_, equitypric0_.`pricemovement` as pricemo41_125_0_, equitypric0_.`pricemovementgrade` as pricemo42_125_0_, equitypric0_.`sevendayyield` as sevenda43_125_0_, equitypric0_.`sevendayyieldgrade` as sevenda44_125_0_, equitypric0_.`snaptime` as snaptime45_125_0_, equitypric0_.`splitfactor` as splitfa46_125_0_, equitypric0_.`totalreturnlastclose1day` as totalre47_125_0_, equitypric0_.`tradetime` as tradetime48_125_0_, equitypric0_.`tradingdate` as trading49_125_0_, equitypric0_.`turnover` as turnover50_125_0_, equitypric0_.`volume` as volume51_125_0_, equitypric0_.`volumegrade` as volumeg52_125_0_, equitypric0_.`vwap` as vwap53_125_0_, equitypric0_.`vwapgrade` as vwapgrade54_125_0_, debtissued3_.convertible as converti2_92_1_, debtissued3_.`debtindicatorstype` as debtindi3_92_1_, debtissued3_.`striptype` as striptype4_92_1_, debtissued3_.`strippbleinstrmentidentifier` as strippbl5_92_1_, debtissued3_.`coupontype` as coupontype6_92_1_, debtissued3_.`createdate` as createdate7_92_1_, debtissued3_.`modifieddate` as modified8_92_1_, debtissued3_.`overallotment` as overallo9_92_1_, debtissued3_.`strippable` as strippable10_92_1_, debtissued3_.`subscriptionduration` as subscri11_92_1_, debtissued3_.`subscriptionend` as subscri12_92_1_, debtissued3_.`subscriptionminval` as subscri13_92_1_, debtissued3_.`subscriptionstart` as subscri14_92_1_, underlying8_.`calculationtype` as calculat2_329_2_, underlying8_.`indicatorstype` as indicato3_329_2_, underlying8_.`marketcenter` as marketce4_329_2_, underlying8_.`quantitydescriptiontype` as quantity5_329_2_, underlying8_.`quantitytype` as quantity6_329_2_, underlying8_.`valuationref` as valuatio7_329_2_, underlying8_.`valuationtype` as valuatio8_329_2_, underlying8_.`unittype` as unittype9_329_2_, underlying8_.instrumentDomain as instrum10_329_2_, underlying8_.`datetime` as datetime11_329_2_, underlying8_.`previousdatetime` as previou12_329_2_, underlying8_.`rank` as rank13_329_2_, underlying8_.`underlyingcount` as underly14_329_2_, underlying8_.`unitsize` as unitsize15_329_2_, currency7_.`full` as full2_83_3_, currency7_.`scheme` as scheme3_83_3_, currency7_.`short` as short4_83_3_, instrument4_.`name` as name2_168_4_, instrument4_.`instrumentstatustype` as instrume3_168_4_, instrument4_.`scopetype` as scopetype4_168_4_, instrument4_.`tranche` as tranche5_168_4_, instrument4_.`marketstatustype` as marketst6_168_4_, instrument4_.`markettype` as markettype7_168_4_, instrument4_.`segmenttype` as segmentt8_168_4_, instrument4_.`schemeinfo` as schemeinfo9_168_4_, instrument4_.`country` as country10_168_4_, instrument4_.`currency` as currency11_168_4_, instrument4_.`instrumentdomain` as instrum12_168_4_, instrument4_.`background` as background13_168_4_, instrument4_.`code` as code14_168_4_, instrument4_.`comment` as comment15_168_4_, instrument4_.`createdate` as createdate16_168_4_, instrument4_.`end` as end17_168_4_, instrument4_.`marketconditions` as marketc18_168_4_, instrument4_.`modifieddate` as modifie19_168_4_, instrument4_.`segmentidentifiercode` as segment20_168_4_, instrument4_.`start` as start21_168_4_, instrument4_.`issuancesubclass` as issuanc22_168_4_, instrument4_.`corporatechangesubclass` as corpora23_168_4_, currency6_.`full` as full2_83_5_, currency6_.`scheme` as scheme3_83_5_, currency6_.`short` as short4_83_5_, issuedata2_.issuanceSubclass as issuance2_181_6_, issuedata2_.indexDomain as indexDom3_181_6_, issuedata2_.interestRateClass as interest4_181_6_, issuedata2_.foreignExchangeDomain as foreignE5_181_6_, issuedata2_.industrySpecificClass as industry6_181_6_, issuedata2_.economicClass as economic7_181_6_, issuedata2_.demographicClass as demograp8_181_6_, issuedata2_.`issuemarkettype` as issuemar9_181_6_, issuedata2_.`issuerref` as issuerref10_181_6_, issuedata2_.`liquidationstatustype` as liquida11_181_6_, issuedata2_.`offertype` as offertype12_181_6_, issuedata2_.`programtype` as program13_181_6_, issuedata2_.`votingrightstype` as votingr14_181_6_, issuedata2_.`accrualbasistype` as accrual15_181_6_, issuedata2_.`accrualconventiontype` as accrual16_181_6_, issuedata2_.`indicatorstype` as indicat17_181_6_, issuedata2_.`modificationtype` as modific18_181_6_, issuedata2_.`agreedrestriction` as agreedr19_181_6_, issuedata2_.`allowedindebtedness` as allowed20_181_6_, issuedata2_.`announcementdate` as announc21_181_6_, issuedata2_.`auctiondate` as auction22_181_6_, issuedata2_.`closingdate` as closing23_181_6_, issuedata2_.`createdate` as createdate24_181_6_, issuedata2_.`defaultclause` as default25_181_6_, issuedata2_.`facevalue` as facevalue26_181_6_, issuedata2_.`issuedate` as issuedate27_181_6_, issuedata2_.`marginable` as marginable28_181_6_, issuedata2_.`marginablemaxlimit` as margina29_181_6_, issuedata2_.`marginableminlimit` as margina30_181_6_, issuedata2_.`modificationdatetime` as modific31_181_6_, issuedata2_.`modifieddate` as modifie32_181_6_, issuedata2_.`negativepledge` as negativ33_181_6_, issuedata2_.`refrainfromacts` as refrain34_181_6_, issuedata2_.`settlementdate` as settlem35_181_6_, issuedata2_.`underlyingcount` as underly36_181_6_, issuedata2_.`useofproceeds` as useofpr37_181_6_, issuedata2_.`validity` as validity38_181_6_, denominati5_.`calculationtype` as calculat2_101_7_, denominati5_.`indicatorstype` as indicato3_101_7_, denominati5_.`marketcenter` as marketce4_101_7_, denominati5_.`quantitydescirptiontype` as quantity5_101_7_, denominati5_.`quantitytype` as quantity6_101_7_, denominati5_.`valuationref` as valuatio7_101_7_, denominati5_.`valuationtype` as valuatio8_101_7_, denominati5_.`crossrate` as crossrate9_101_7_, denominati5_.`currency` as currency10_101_7_, denominati5_.`instrumentdomain` as instrum11_101_7_, denominati5_.`datetime` as datetime12_101_7_, denominati5_.`denomination` as denomin13_101_7_, denominati5_.`increment` as increment14_101_7_, denominati5_.`minval` as minval15_101_7_, denominati5_.`size` as size16_101_7_, denominati5_.`debtissuedata` as debtiss17_101_7_, issuerclas9_.`entitystatustype` as entityst2_191_8_, issuerclas9_.`replacemententity` as replacem3_191_8_, issuerclas9_.`replacementtype` as replacem4_191_8_, issuerclas9_.`comment` as comment5_191_8_, issuerclas9_.`createdate` as createdate6_191_8_, issuerclas9_.`creationdate` as creation7_191_8_, issuerclas9_.`duration` as duration8_191_8_, issuerclas9_.`end` as end9_191_8_, issuerclas9_.`incorpdate` as incorpdate10_191_8_, issuerclas9_.`modifieddate` as modifie11_191_8_, issuerclas9_.`name` as name12_191_8_, issuerclas9_.`parentid` as parentid13_191_8_, issuerclas9_.`reviewdate` as reviewdate14_191_8_, issuerclas9_.`shortname` as shortname15_191_8_, issuerclas9_.`start` as start16_191_8_, issuerclas9_.`ultimateparentid` as ultimat17_191_8_, instrument1_.`instrumenttype` as instrume2_165_9_, instrument1_.`instrumentformtype` as instrume3_165_9_, instrument1_.`tradingrestrictionstype` as tradingr4_165_9_, instrument1_.`amountoutstanding` as amountou5_165_9_, instrument1_.`amountoutstandingdate` as amountou6_165_9_, instrument1_.`benchmarkstrategy` as benchmar7_165_9_, instrument1_.`contractvaluemultiplier` as contract8_165_9_, instrument1_.`createdate` as createdate9_165_9_, instrument1_.`defaultclause` as default10_165_9_, instrument1_.`firstamount` as firstam11_165_9_, instrument1_.`firstdate` as firstdate12_165_9_, instrument1_.`firstdealingdate` as firstde13_165_9_, instrument1_.`firsteventvalue` as firstev14_165_9_, instrument1_.`firstsettlementdate` as firstse15_165_9_, instrument1_.`lastamount` as lastamount16_165_9_, instrument1_.`lastdate` as lastdate17_165_9_, instrument1_.`lasteventvalue` as lasteve18_165_9_, instrument1_.`marketderivedinfperiddration` as marketd19_165_9_, instrument1_.`marketderivedinfoperiodend` as marketd20_165_9_, instrument1_.`marketderivedinfoperiodstart` as marketd21_165_9_, instrument1_.`modifieddate` as modifie22_165_9_, instrument1_.`name` as name23_165_9_, instrument1_.`nextamount` as nextamount24_165_9_, instrument1_.`nexteventvalue` as nexteve25_165_9_, instrument1_.`numberholders` as numberh26_165_9_, instrument1_.`numberholdersdatetime` as numberh27_165_9_, instrument1_.`nmberholderspreviousdatetime` as nmberho28_165_9_, instrument1_.`objective` as objective29_165_9_, instrument1_.`penultimateamount` as penulti30_165_9_, instrument1_.`penultimatedate` as penulti31_165_9_, instrument1_.`principalpaymentend` as princip32_165_9_, instrument1_.`principalpaymentstart` as princip33_165_9_, instrument1_.`purchasingminimums` as purchas34_165_9_, instrument1_.`qualifiedplans` as qualifi35_165_9_, instrument1_.`recurrable` as recurrable36_165_9_, instrument1_.`shortname` as shortname37_165_9_, instrument1_.`instrumentdomain` as instrum38_165_9_, instrument1_.`issuerclass` as issuerc39_165_9_, denominati5_.`instrumentdomain` as instrum11___, denominati5_.`id` as id1___ from `equityprice` equitypric0_ inner join `instrumentdomain` instrument1_ on equitypric0_.`instrumentdomain`=instrument1_.`id` left outer join `issuedata` issuedata2_ on instrument1_.`id`=issuedata2_.`id` left outer join `debtissuedata` debtissued3_ on instrument1_.`id`=debtissued3_.`id` left outer join `instrumentidentifier` instrument4_ on instrument1_.`id`=instrument4_.`instrumentdomain` left outer join `denomination` denominati5_ on instrument1_.`id`=denominati5_.`instrumentdomain` left outer join `currency` currency6_ on instrument4_.`currency`=currency6_.`id` left outer join `currency` currency7_ on denominati5_.`currency`=currency7_.`id` left outer join `underlyingcount` underlying8_ on instrument1_.`id`=underlying8_.instrumentDomain left outer join `issuerclass` issuerclas9_ on instrument1_.`issuerclass`=issuerclas9_.`id` where (instrument1_.`id` in(? , ? , ? , ?))and(equitypric0_.`tradingdate`>=? )and(equitypric0_.`tradingdate`<=? ) order by  instrument1_.`id` , equitypric0_.`tradingdate`
domain id: 63
domain id: 63
domain id: 63
domain id: 63
domain id: 63
domain id: 63
domain id: 63
domain id: 63
...
domain id: 64
Hibernate: select instrument0_.`instrumentdomain` as instrum12_2_, instrument0_.`id` as id1_2_, instrument0_.`id` as id1_168_1_, instrument0_.`name` as name2_168_1_, instrument0_.`instrumentstatustype` as instrume3_168_1_, instrument0_.`scopetype` as scopetype4_168_1_, instrument0_.`tranche` as tranche5_168_1_, instrument0_.`marketstatustype` as marketst6_168_1_, instrument0_.`markettype` as markettype7_168_1_, instrument0_.`segmenttype` as segmentt8_168_1_, instrument0_.`schemeinfo` as schemeinfo9_168_1_, instrument0_.`country` as country10_168_1_, instrument0_.`currency` as currency11_168_1_, instrument0_.`instrumentdomain` as instrum12_168_1_, instrument0_.`background` as background13_168_1_, instrument0_.`code` as code14_168_1_, instrument0_.`comment` as comment15_168_1_, instrument0_.`createdate` as createdate16_168_1_, instrument0_.`end` as end17_168_1_, instrument0_.`marketconditions` as marketc18_168_1_, instrument0_.`modifieddate` as modifie19_168_1_, instrument0_.`segmentidentifiercode` as segment20_168_1_, instrument0_.`start` as start21_168_1_, instrument0_.`issuancesubclass` as issuanc22_168_1_, instrument0_.`corporatechangesubclass` as corpora23_168_1_, currency1_.`id` as id1_83_0_, currency1_.`full` as full2_83_0_, currency1_.`scheme` as scheme3_83_0_, currency1_.`short` as short4_83_0_ from `instrumentidentifier` instrument0_ left outer join `currency` currency1_ on instrument0_.`currency`=currency1_.`id` where instrument0_.`instrumentdomain`=?
domain id: 64
...
repeat for each equityPrice

Here is the query, you can clearly see that it include the instrumentidentifier to currency join, which for some reason Hibernate seems to be ignoring.

Code:

         queryString.append("select prc from EquityPrice prc inner join fetch prc.instrumentDomain ind " +
               "left join fetch ind.issueData issd left join fetch ind.debtIssueData dissd left join fetch " +
               "ind.instrumentIdentifier insid left join fetch ind.denomination denom left join fetch " +
               "insid.currency cur1 left join fetch denom.currency cur2 left join fetch " +
               "ind.underlyingCount left join fetch ind.instrumentType instyp left join fetch ind.issuerClass");
         if (instrumentDomainId != null && instrumentDomainId.length > 0)
         {
            int length = instrumentDomainId.length;
            if (length == 1)
               queryString.append(separator + "ind.id = ?");
            else
            {
               queryString.append(separator + "ind.id in (");
               for (int i=0; i < length; ++i)
               {
                  queryString.append("?");
                  if (i < length-1)
                     queryString.append(",");
               }
               queryString.append(")");
            }
            separator = " and ";
         }
         queryString.append(separator + "prc.tradingDate >= ?");
         separator = " and ";
         queryString.append(separator + "prc.tradingDate <= ?");

         Calendar cal = Calendar.getInstance();
         if(end==null)
            end = new Date();

         if(start==null) {
            cal.setTimeInMillis(end.getTime()-1728000000L);
            start = cal.getTime();
         }

         queryString.append(" order by ind.id, prc.tradingDate");

Any guidance?

Teneo also supports JPOX and their Fetch Groups look like an explicit way of controlling what gets loaded and when. Hibernates looks like a possibly more flexible way of managing the same 'how' and 'when' problem, if only I could get it to work as described in chapter 19.

Thx.

David

Top

olddave

Post subject: More detail

Posted: Fri Jan 30, 2009 10:19 am

Newbie

Joined: Wed Jan 28, 2009 12:00 pm
Posts: 12

Might help if you can see the hbm.xml for the ignored left join fetch

Code:

   <class name="com.stpenable.gensec.impl.InstrumentIdentifierImpl" entity-name="InstrumentIdentifier" abstract="false" lazy="true" table="`instrumentidentifier`" proxy="com.stpenable.gensec.InstrumentIdentifier">
      <meta attribute="eclassName">InstrumentIdentifier</meta>
      <meta attribute="epackage">http://www.stpenable.com/gensec</meta>
      <id name="id" type="int" unsaved-value="-1">
         <column not-null="true" unique="false" name="`id`"/>
         <generator class="identity"/>
      </id>
...
      <many-to-one name="currency" entity-name="Currency" fetch="join" lazy="proxy" foreign-key="instrmentidentifier_currency" insert="true" update="true" not-null="false">
         <column not-null="false" unique="false" name="`currency`"/>
      </many-to-one>
      <many-to-one name="instrumentDomain" entity-name="InstrumentDomain" fetch="join" lazy="proxy" foreign-key="instrmntidntfir_instrmntdmin" insert="true" update="true" not-null="false">
         <column not-null="false" unique="false" name="`instrumentdomain`"/>
      </many-to-one>
      <bag name="instrumentIdentifierMarketIdentifier" lazy="true" table="`instrmntidntifirmrktidntifir`">
         <key update="true">
            <column name="`instrumentidentifier`" not-null="true" unique="false"/>
         </key>
         <many-to-many entity-name="MarketIdentifier" unique="false" foreign-key="instrmntdntfr_nstrmntdntfrmr">
            <column name="`marketidentifier`" not-null="true" unique="false"/>
         </many-to-many>
      </bag>
...

Thx.

David

Top

olddave

Post subject: Too many problems

Posted: Fri Jan 30, 2009 11:31 am

Newbie

Joined: Wed Jan 28, 2009 12:00 pm
Posts: 12

Here is another query not behaving as the documentation suggests.

I added the left join fetch one by one to try to cover all tables in the m*n+1 selects I was getting. Apparently there is a limit, I must have missed this in the documentation.

Here is the log

Code:

2009-01-3015:12:17.406 GMT+0000 277766 [btpool0-3 - /dwr/dwr/call/plaincall/SearchEquity.searchEquity.dwr] DEBUG org.hibernate.hql.classic.QueryTranslatorImpl  - HQL: select inst from InstrumentDomain as inst left join fetch inst.instrumentIdentifier ident left join fetch ident.schemeInfo info left join fetch inst.instrumentType as itype left join fetch inst.denomination denom left join fetch denom.currency as currency left join fetch inst.industryIdentifier industry left join fetch inst.lastCAE lastCAE left join fetch inst.issuerClass issr left join fetch inst.issueData issd left join fetch inst.debtIssueData dissd left join fetch inst.underlyingInstrument undly left join fetch inst.underlyingCount undlycount left join fetch issr.location loc left join fetch issd.portfolioDomain pdom left join fetch issd.marketStatsClass mrkts left join fetch issd.underlyingCount ucnt left join fetch issd.issueAmount issamt left join fetch loc.country cntry left join fetch loc.locationType loctyp where itype.id in (46,48,49,150,151,185,432,438,439,443,473) and inst.name like ?
2009-01-3015:12:17.421 GMT+0000 277781 [btpool0-3 - /dwr/dwr/call/plaincall/SearchEquity.searchEquity.dwr] DEBUG org.hibernate.hql.classic.QueryTranslatorImpl  - SQL: select instrument0_.`id` as id1_165_0_, denominati4_.`id` as id1_101_1_, schemeinfo2_.`id` as id1_290_2_, location13_.`id` as id1_200_3_, lastcae7_.`id` as id1_196_4_, portfoliod14_.`id` as id1_245_5_, instrument3_.`id` as id1_171_6_, country17_.`id` as id1_77_7_, issuerclas8_.`id` as id1_191_8_, debtissued10_.`id` as id1_92_9_, underlying11_.`id` as id1_165_10_, underlying12_.`id` as id1_329_11_, instrument1_.`id` as id1_168_12_, issueamoun16_.`id` as id1_180_13_, industryid6_.`id` as id1_162_14_, currency5_.`id` as id1_83_15_, issuedata9_.`id` as id1_181_16_, locationty18_.`id` as id1_201_17_, marketstat15_.`id` as id1_208_18_, instrument0_.`instrumenttype` as instrume2_165_0_, instrument0_.`instrumentformtype` as instrume3_165_0_, instrument0_.`tradingrestrictionstype` as tradingr4_165_0_, instrument0_.`amountoutstanding` as amountou5_165_0_, instrument0_.`amountoutstandingdate` as amountou6_165_0_, instrument0_.`benchmarkstrategy` as benchmar7_165_0_, instrument0_.`contractvaluemultiplier` as contract8_165_0_, instrument0_.`createdate` as createdate9_165_0_, instrument0_.`defaultclause` as default10_165_0_, instrument0_.`firstamount` as firstam11_165_0_, instrument0_.`firstdate` as firstdate12_165_0_, instrument0_.`firstdealingdate` as firstde13_165_0_, instrument0_.`firsteventvalue` as firstev14_165_0_, instrument0_.`firstsettlementdate` as firstse15_165_0_, instrument0_.`lastamount` as lastamount16_165_0_, instrument0_.`lastdate` as lastdate17_165_0_, instrument0_.`lasteventvalue` as lasteve18_165_0_, instrument0_.`marketderivedinfperiddration` as marketd19_165_0_, instrument0_.`marketderivedinfoperiodend` as marketd20_165_0_, instrument0_.`marketderivedinfoperiodstart` as marketd21_165_0_, instrument0_.`modifieddate` as modifie22_165_0_, instrument0_.`name` as name23_165_0_, instrument0_.`nextamount` as nextamount24_165_0_, instrument0_.`nexteventvalue` as nexteve25_165_0_, instrument0_.`numberholders` as numberh26_165_0_, instrument0_.`numberholdersdatetime` as numberh27_165_0_, instrument0_.`nmberholderspreviousdatetime` as nmberho28_165_0_, instrument0_.`objective` as objective29_165_0_, instrument0_.`penultimateamount` as penulti30_165_0_, instrument0_.`penultimatedate` as penulti31_165_0_, instrument0_.`principalpaymentend` as princip32_165_0_, instrument0_.`principalpaymentstart` as princip33_165_0_, instrument0_.`purchasingminimums` as purchas34_165_0_, instrument0_.`qualifiedplans` as qualifi35_165_0_, instrument0_.`recurrable` as recurrable36_165_0_, instrument0_.`shortname` as shortname37_165_0_, instrument0_.`instrumentdomain` as instrum38_165_0_, instrument0_.`issuerclass` as issuerc39_165_0_, denominati4_.`calculationtype` as calculat2_101_1_, denominati4_.`indicatorstype` as indicato3_101_1_, denominati4_.`marketcenter` as marketce4_101_1_, denominati4_.`quantitydescirptiontype` as quantity5_101_1_, denominati4_.`quantitytype` as quantity6_101_1_, denominati4_.`valuationref` as valuatio7_101_1_, denominati4_.`valuationtype` as valuatio8_101_1_, denominati4_.`crossrate` as crossrate9_101_1_, denominati4_.`currency` as currency10_101_1_, denominati4_.`instrumentdomain` as instrum11_101_1_, denominati4_.`datetime` as datetime12_101_1_, denominati4_.`denomination` as denomin13_101_1_, denominati4_.`increment` as increment14_101_1_, denominati4_.`minval` as minval15_101_1_, denominati4_.`size` as size16_101_1_, denominati4_.`debtissuedata` as debtiss17_101_1_, schemeinfo2_.`displayname` as displayn2_290_2_, schemeinfo2_.`longname` as longname3_290_2_, schemeinfo2_.`scheme` as scheme4_290_2_, schemeinfo2_.`shortname` as shortname5_290_2_, location13_.`locationtype` as location2_200_3_, location13_.`country` as country3_200_3_, location13_.`address` as address4_200_3_, location13_.`city` as city5_200_3_, location13_.`end` as end6_200_3_, location13_.`faxnumber` as faxnumber7_200_3_, location13_.`municipality` as municipa8_200_3_, location13_.`postalcode` as postalcode9_200_3_, location13_.`postalcodescheme` as postalc10_200_3_, location13_.`regionname` as regionname11_200_3_, location13_.`start` as start12_200_3_, location13_.`stateorprovincename` as stateor13_200_3_, location13_.`telephonenumber` as telepho14_200_3_, location13_.`websiteurl` as websiteurl15_200_3_, location13_.`issuedata` as issuedata16_200_3_, location13_.`businessclass` as busines17_200_3_, location13_.`counterpartyclass` as counter18_200_3_, location13_.`issuerclass` as issuerc19_200_3_, lastcae7_.`caetype` as caetype2_196_4_, lastcae7_.`datetime` as datetime3_196_4_, portfoliod14_.`industryidentifier` as industry2_245_5_, portfoliod14_.`underlying` as underlying3_245_5_, portfoliod14_.`underlyingcount` as underlyi4_245_5_, portfoliod14_.issueData as issueData245_5_, instrument3_.`full` as full2_171_6_, instrument3_.`scheme` as scheme3_171_6_, instrument3_.`short` as short4_171_6_, country17_.`country` as country2_77_7_, country17_.`scheme` as scheme3_77_7_, issuerclas8_.`entitystatustype` as entityst2_191_8_, issuerclas8_.`replacemententity` as replacem3_191_8_, issuerclas8_.`replacementtype` as replacem4_191_8_, issuerclas8_.`comment` as comment5_191_8_, issuerclas8_.`createdate` as createdate6_191_8_, issuerclas8_.`creationdate` as creation7_191_8_, issuerclas8_.`duration` as duration8_191_8_, issuerclas8_.`end` as end9_191_8_, issuerclas8_.`incorpdate` as incorpdate10_191_8_, issuerclas8_.`modifieddate` as modifie11_191_8_, issuerclas8_.`name` as name12_191_8_, issuerclas8_.`parentid` as parentid13_191_8_, issuerclas8_.`reviewdate` as reviewdate14_191_8_, issuerclas8_.`shortname` as shortname15_191_8_, issuerclas8_.`start` as start16_191_8_, issuerclas8_.`ultimateparentid` as ultimat17_191_8_, debtissued10_.convertible as converti2_92_9_, debtissued10_.`debtindicatorstype` as debtindi3_92_9_, debtissued10_.`striptype` as striptype4_92_9_, debtissued10_.`strippbleinstrmentidentifier` as strippbl5_92_9_, debtissued10_.`coupontype` as coupontype6_92_9_, debtissued10_.`createdate` as createdate7_92_9_, debtissued10_.`modifieddate` as modified8_92_9_, debtissued10_.`overallotment` as overallo9_92_9_, debtissued10_.`strippable` as strippable10_92_9_, debtissued10_.`subscriptionduration` as subscri11_92_9_, debtissued10_.`subscriptionend` as subscri12_92_9_, debtissued10_.`subscriptionminval` as subscri13_92_9_, debtissued10_.`subscriptionstart` as subscri14_92_9_, underlying11_.`instrumenttype` as instrume2_165_10_, underlying11_.`instrumentformtype` as instrume3_165_10_, underlying11_.`tradingrestrictionstype` as tradingr4_165_10_, underlying11_.`amountoutstanding` as amountou5_165_10_, underlying11_.`amountoutstandingdate` as amountou6_165_10_, underlying11_.`benchmarkstrategy` as benchmar7_165_10_, underlying11_.`contractvaluemultiplier` as contract8_165_10_, underlying11_.`createdate` as createdate9_165_10_, underlying11_.`defaultclause` as default10_165_10_, underlying11_.`firstamount` as firstam11_165_10_, underlying11_.`firstdate` as firstdate12_165_10_, underlying11_.`firstdealingdate` as firstde13_165_10_, underlying11_.`firsteventvalue` as firstev14_165_10_, underlying11_.`firstsettlementdate` as firstse15_165_10_, underlying11_.`lastamount` as lastamount16_165_10_, underlying11_.`lastdate` as lastdate17_165_10_, underlying11_.`lasteventvalue` as lasteve18_165_10_, underlying11_.`marketderivedinfperiddration` as marketd19_165_10_, underlying11_.`marketderivedinfoperiodend` as marketd20_165_10_, underlying11_.`marketderivedinfoperiodstart` as marketd21_165_10_, underlying11_.`modifieddate` as modifie22_165_10_, underlying11_.`name` as name23_165_10_, underlying11_.`nextamount` as nextamount24_165_10_, underlying11_.`nexteventvalue` as nexteve25_165_10_, underlying11_.`numberholders` as numberh26_165_10_, underlying11_.`numberholdersdatetime` as numberh27_165_10_, underlying11_.`nmberholderspreviousdatetime` as nmberho28_165_10_, underlying11_.`objective` as objective29_165_10_, underlying11_.`penultimateamount` as penulti30_165_10_, underlying11_.`penultimatedate` as penulti31_165_10_, underlying11_.`principalpaymentend` as princip32_165_10_, underlying11_.`principalpaymentstart` as princip33_165_10_, underlying11_.`purchasingminimums` as purchas34_165_10_, underlying11_.`qualifiedplans` as qualifi35_165_10_, underlying11_.`recurrable` as recurrable36_165_10_, underlying11_.`shortname` as shortname37_165_10_, underlying11_.`instrumentdomain` as instrum38_165_10_, underlying11_.`issuerclass` as issuerc39_165_10_, underlying12_.`calculationtype` as calculat2_329_11_, underlying12_.`indicatorstype` as indicato3_329_11_, underlying12_.`marketcenter` as marketce4_329_11_, underlying12_.`quantitydescriptiontype` as quantity5_329_11_, underlying12_.`quantitytype` as quantity6_329_11_, underlying12_.`valuationref` as valuatio7_329_11_, underlying12_.`valuationtype` as valuatio8_329_11_, underlying12_.`unittype` as unittype9_329_11_, underlying12_.instrumentDomain as instrum10_329_11_, underlying12_.`datetime` as datetime11_329_11_, underlying12_.`previousdatetime` as previou12_329_11_, underlying12_.`rank` as rank13_329_11_, underlying12_.`underlyingcount` as underly14_329_11_, underlying12_.`unitsize` as unitsize15_329_11_, instrument1_.`name` as name2_168_12_, instrument1_.`instrumentstatustype` as instrume3_168_12_, instrument1_.`scopetype` as scopetype4_168_12_, instrument1_.`tranche` as tranche5_168_12_, instrument1_.`marketstatustype` as marketst6_168_12_, instrument1_.`markettype` as markettype7_168_12_, instrument1_.`segmenttype` as segmentt8_168_12_, instrument1_.`schemeinfo` as schemeinfo9_168_12_, instrument1_.`country` as country10_168_12_, instrument1_.`currency` as currency11_168_12_, instrument1_.`instrumentdomain` as instrum12_168_12_, instrument1_.`background` as background13_168_12_, instrument1_.`code` as code14_168_12_, instrument1_.`comment` as comment15_168_12_, instrument1_.`createdate` as createdate16_168_12_, instrument1_.`end` as end17_168_12_, instrument1_.`marketconditions` as marketc18_168_12_, instrument1_.`modifieddate` as modifie19_168_12_, instrument1_.`segmentidentifiercode` as segment20_168_12_, instrument1_.`start` as start21_168_12_, instrument1_.`issuancesubclass` as issuanc22_168_12_, instrument1_.`corporatechangesubclass` as corpora23_168_12_, issueamoun16_.`calculationtype` as calculat2_180_13_, issueamoun16_.`indicatorstype` as indicato3_180_13_, issueamoun16_.`marketcenter` as marketce4_180_13_, issueamoun16_.`quantitydescriptiontype` as quantity5_180_13_, issueamoun16_.`quantitytype` as quantity6_180_13_, issueamoun16_.`valuationtype` as valuatio7_180_13_, issueamoun16_.`currency` as currency8_180_13_, issueamoun16_.`debtindicatorstype` as debtindi9_180_13_, issueamoun16_.`crossrate` as crossrate10_180_13_, issueamoun16_.`crossratemultiplier` as crossra11_180_13_, issueamoun16_.`datetime` as datetime12_180_13_, issueamoun16_.`issueamount` as issueam13_180_13_, issueamoun16_.`multiplier` as multiplier14_180_13_, issueamoun16_.`previousdatetime` as previou15_180_13_, issueamoun16_.`rank` as rank16_180_13_, issueamoun16_.`size` as size17_180_13_, industryid6_.`industryidentifiertype` as industry2_162_14_, industryid6_.`code` as code3_162_14_, industryid6_.`end` as end4_162_14_, industryid6_.`name` as name5_162_14_, industryid6_.`start` as start6_162_14_, industryid6_.`validity` as validity7_162_14_, industryid6_.`instrumentdomain` as instrume8_162_14_, currency5_.`full` as full2_83_15_, currency5_.`scheme` as scheme3_83_15_, currency5_.`short` as short4_83_15_, issuedata9_.issuanceSubclass as issuance2_181_16_, issuedata9_.indexDomain as indexDom3_181_16_, issuedata9_.interestRateClass as interest4_181_16_, issuedata9_.foreignExchangeDomain as foreignE5_181_16_, issuedata9_.industrySpecificClass as industry6_181_16_, issuedata9_.economicClass as economic7_181_16_, issuedata9_.demographicClass as demograp8_181_16_, issuedata9_.`issuemarkettype` as issuemar9_181_16_, issuedata9_.`issuerref` as issuerref10_181_16_, issuedata9_.`liquidationstatustype` as liquida11_181_16_, issuedata9_.`offertype` as offertype12_181_16_, issuedata9_.`programtype` as program13_181_16_, issuedata9_.`votingrightstype` as votingr14_181_16_, issuedata9_.`accrualbasistype` as accrual15_181_16_, issuedata9_.`accrualconventiontype` as accrual16_181_16_, issuedata9_.`indicatorstype` as indicat17_181_16_, issuedata9_.`modificationtype` as modific18_181_16_, issuedata9_.`agreedrestriction` as agreedr19_181_16_, issuedata9_.`allowedindebtedness` as allowed20_181_16_, issuedata9_.`announcementdate` as announc21_181_16_, issuedata9_.`auctiondate` as auction22_181_16_, issuedata9_.`closingdate` as closing23_181_16_, issuedata9_.`createdate` as createdate24_181_16_, issuedata9_.`defaultclause` as default25_181_16_, issuedata9_.`facevalue` as facevalue26_181_16_, issuedata9_.`issuedate` as issuedate27_181_16_, issuedata9_.`marginable` as marginable28_181_16_, issuedata9_.`marginablemaxlimit` as margina29_181_16_, issuedata9_.`marginableminlimit` as margina30_181_16_, issuedata9_.`modificationdatetime` as modific31_181_16_, issuedata9_.`modifieddate` as modifie32_181_16_, issuedata9_.`negativepledge` as negativ33_181_16_, issuedata9_.`refrainfromacts` as refrain34_181_16_, issuedata9_.`settlementdate` as settlem35_181_16_, issuedata9_.`underlyingcount` as underly36_181_16_, issuedata9_.`useofproceeds` as useofpr37_181_16_, issuedata9_.`validity` as validity38_181_16_, locationty18_.`full` as full2_201_17_, locationty18_.`scheme` as scheme3_201_17_, locationty18_.`short` as short4_201_17_, marketstat15_.`industryidentifier` as industry2_208_18_, marketstat15_.`instrumentidentifier` as instrume3_208_18_, marketstat15_.issueData as issueData208_18_, location13_.`issuerclass` as issuerc19___, location13_.`id` as id1___ from `instrumentdomain` instrument0_ left outer join `instrumentidentifier` instrument1_ on instrument0_.`id`=instrument1_.`instrumentdomain` left outer join `schemeinfo` schemeinfo2_ on instrument1_.`schemeinfo`=schemeinfo2_.`id` left outer join `instrumenttype` instrument3_ on instrument0_.`instrumenttype`=instrument3_.`id` left outer join `denomination` denominati4_ on instrument0_.`id`=denominati4_.`instrumentdomain` left outer join `currency` currency5_ on denominati4_.`currency`=currency5_.`id` left outer join `industryidentifier` industryid6_ on instrument0_.`id`=industryid6_.`instrumentdomain` left outer join `lastcae` lastcae7_ on instrument0_.`id`=lastcae7_.`id` left outer join `issuerclass` issuerclas8_ on instrument0_.`issuerclass`=issuerclas8_.`id` left outer join `issuedata` issuedata9_ on instrument0_.`id`=issuedata9_.`id` left outer join `debtissuedata` debtissued10_ on instrument0_.`id`=debtissued10_.`id` left outer join `instrumentdomain` underlying11_ on instrument0_.`id`=underlying11_.`instrumentdomain` left outer join `underlyingcount` underlying12_ on instrument0_.`id`=underlying12_.instrumentDomain left outer join `location` location13_ on issuerclas8_.`id`=location13_.`issuerclass` left outer join `portfoliodomain` portfoliod14_ on issuedata9_.`id`=portfoliod14_.issueData left outer join `marketstatsclass` marketstat15_ on issuedata9_.`id`=marketstat15_.issueData left outer join `issueamount` issueamoun16_ on issuedata9_.`id`=issueamoun16_.`id` left outer join `country` country17_ on location13_.`country`=country17_.`id` left outer join `locationtype` locationty18_ on location13_.`locationtype`=locationty18_.`id` where (instrument3_.`id` in(46 , 48 , 49 , 150 , 151 , 185 , 432 , 438 , 439 , 443 , 473))and(instrument0_.`name` like ? )
2009-01-3015:12:17.437 GMT+0000 277797 [btpool0-3 - /dwr/dwr/call/plaincall/SearchEquity.searchEquity.dwr] DEBUG org.hibernate.jdbc.AbstractBatcher  - about to open PreparedStatement (open PreparedStatements: 0, globally: 0)
2009-01-3015:12:17.437 GMT+0000 277797 [btpool0-3 - /dwr/dwr/call/plaincall/SearchEquity.searchEquity.dwr] DEBUG org.hibernate.SQL  - select instrument0_.`id` as id1_165_0_, denominati4_.`id` as id1_101_1_, schemeinfo2_.`id` as id1_290_2_, location13_.`id` as id1_200_3_, lastcae7_.`id` as id1_196_4_, portfoliod14_.`id` as id1_245_5_, instrument3_.`id` as id1_171_6_, country17_.`id` as id1_77_7_, issuerclas8_.`id` as id1_191_8_, debtissued10_.`id` as id1_92_9_, underlying11_.`id` as id1_165_10_, underlying12_.`id` as id1_329_11_, instrument1_.`id` as id1_168_12_, issueamoun16_.`id` as id1_180_13_, industryid6_.`id` as id1_162_14_, currency5_.`id` as id1_83_15_, issuedata9_.`id` as id1_181_16_, locationty18_.`id` as id1_201_17_, marketstat15_.`id` as id1_208_18_, instrument0_.`instrumenttype` as instrume2_165_0_, instrument0_.`instrumentformtype` as instrume3_165_0_, instrument0_.`tradingrestrictionstype` as tradingr4_165_0_, instrument0_.`amountoutstanding` as amountou5_165_0_, instrument0_.`amountoutstandingdate` as amountou6_165_0_, instrument0_.`benchmarkstrategy` as benchmar7_165_0_, instrument0_.`contractvaluemultiplier` as contract8_165_0_, instrument0_.`createdate` as createdate9_165_0_, instrument0_.`defaultclause` as default10_165_0_, instrument0_.`firstamount` as firstam11_165_0_, instrument0_.`firstdate` as firstdate12_165_0_, instrument0_.`firstdealingdate` as firstde13_165_0_, instrument0_.`firsteventvalue` as firstev14_165_0_, instrument0_.`firstsettlementdate` as firstse15_165_0_, instrument0_.`lastamount` as lastamount16_165_0_, instrument0_.`lastdate` as lastdate17_165_0_, instrument0_.`lasteventvalue` as lasteve18_165_0_, instrument0_.`marketderivedinfperiddration` as marketd19_165_0_, instrument0_.`marketderivedinfoperiodend` as marketd20_165_0_, instrument0_.`marketderivedinfoperiodstart` as marketd21_165_0_, instrument0_.`modifieddate` as modifie22_165_0_, instrument0_.`name` as name23_165_0_, instrument0_.`nextamount` as nextamount24_165_0_, instrument0_.`nexteventvalue` as nexteve25_165_0_, instrument0_.`numberholders` as numberh26_165_0_, instrument0_.`numberholdersdatetime` as numberh27_165_0_, instrument0_.`nmberholderspreviousdatetime` as nmberho28_165_0_, instrument0_.`objective` as objective29_165_0_, instrument0_.`penultimateamount` as penulti30_165_0_, instrument0_.`penultimatedate` as penulti31_165_0_, instrument0_.`principalpaymentend` as princip32_165_0_, instrument0_.`principalpaymentstart` as princip33_165_0_, instrument0_.`purchasingminimums` as purchas34_165_0_, instrument0_.`qualifiedplans` as qualifi35_165_0_, instrument0_.`recurrable` as recurrable36_165_0_, instrument0_.`shortname` as shortname37_165_0_, instrument0_.`instrumentdomain` as instrum38_165_0_, instrument0_.`issuerclass` as issuerc39_165_0_, denominati4_.`calculationtype` as calculat2_101_1_, denominati4_.`indicatorstype` as indicato3_101_1_, denominati4_.`marketcenter` as marketce4_101_1_, denominati4_.`quantitydescirptiontype` as quantity5_101_1_, denominati4_.`quantitytype` as quantity6_101_1_, denominati4_.`valuationref` as valuatio7_101_1_, denominati4_.`valuationtype` as valuatio8_101_1_, denominati4_.`crossrate` as crossrate9_101_1_, denominati4_.`currency` as currency10_101_1_, denominati4_.`instrumentdomain` as instrum11_101_1_, denominati4_.`datetime` as datetime12_101_1_, denominati4_.`denomination` as denomin13_101_1_, denominati4_.`increment` as increment14_101_1_, denominati4_.`minval` as minval15_101_1_, denominati4_.`size` as size16_101_1_, denominati4_.`debtissuedata` as debtiss17_101_1_, schemeinfo2_.`displayname` as displayn2_290_2_, schemeinfo2_.`longname` as longname3_290_2_, schemeinfo2_.`scheme` as scheme4_290_2_, schemeinfo2_.`shortname` as shortname5_290_2_, location13_.`locationtype` as location2_200_3_, location13_.`country` as country3_200_3_, location13_.`address` as address4_200_3_, location13_.`city` as city5_200_3_, location13_.`end` as end6_200_3_, location13_.`faxnumber` as faxnumber7_200_3_, location13_.`municipality` as municipa8_200_3_, location13_.`postalcode` as postalcode9_200_3_, location13_.`postalcodescheme` as postalc10_200_3_, location13_.`regionname` as regionname11_200_3_, location13_.`start` as start12_200_3_, location13_.`stateorprovincename` as stateor13_200_3_, location13_.`telephonenumber` as telepho14_200_3_, location13_.`websiteurl` as websiteurl15_200_3_, location13_.`issuedata` as issuedata16_200_3_, location13_.`businessclass` as busines17_200_3_, location13_.`counterpartyclass` as counter18_200_3_, location13_.`issuerclass` as issuerc19_200_3_, lastcae7_.`caetype` as caetype2_196_4_, lastcae7_.`datetime` as datetime3_196_4_, portfoliod14_.`industryidentifier` as industry2_245_5_, portfoliod14_.`underlying` as underlying3_245_5_, portfoliod14_.`underlyingcount` as underlyi4_245_5_, portfoliod14_.issueData as issueData245_5_, instrument3_.`full` as full2_171_6_, instrument3_.`scheme` as scheme3_171_6_, instrument3_.`short` as short4_171_6_, country17_.`country` as country2_77_7_, country17_.`scheme` as scheme3_77_7_, issuerclas8_.`entitystatustype` as entityst2_191_8_, issuerclas8_.`replacemententity` as replacem3_191_8_, issuerclas8_.`replacementtype` as replacem4_191_8_, issuerclas8_.`comment` as comment5_191_8_, issuerclas8_.`createdate` as createdate6_191_8_, issuerclas8_.`creationdate` as creation7_191_8_, issuerclas8_.`duration` as duration8_191_8_, issuerclas8_.`end` as end9_191_8_, issuerclas8_.`incorpdate` as incorpdate10_191_8_, issuerclas8_.`modifieddate` as modifie11_191_8_, issuerclas8_.`name` as name12_191_8_, issuerclas8_.`parentid` as parentid13_191_8_, issuerclas8_.`reviewdate` as reviewdate14_191_8_, issuerclas8_.`shortname` as shortname15_191_8_, issuerclas8_.`start` as start16_191_8_, issuerclas8_.`ultimateparentid` as ultimat17_191_8_, debtissued10_.convertible as converti2_92_9_, debtissued10_.`debtindicatorstype` as debtindi3_92_9_, debtissued10_.`striptype` as striptype4_92_9_, debtissued10_.`strippbleinstrmentidentifier` as strippbl5_92_9_, debtissued10_.`coupontype` as coupontype6_92_9_, debtissued10_.`createdate` as createdate7_92_9_, debtissued10_.`modifieddate` as modified8_92_9_, debtissued10_.`overallotment` as overallo9_92_9_, debtissued10_.`strippable` as strippable10_92_9_, debtissued10_.`subscriptionduration` as subscri11_92_9_, debtissued10_.`subscriptionend` as subscri12_92_9_, debtissued10_.`subscriptionminval` as subscri13_92_9_, debtissued10_.`subscriptionstart` as subscri14_92_9_, underlying11_.`instrumenttype` as instrume2_165_10_, underlying11_.`instrumentformtype` as instrume3_165_10_, underlying11_.`tradingrestrictionstype` as tradingr4_165_10_, underlying11_.`amountoutstanding` as amountou5_165_10_, underlying11_.`amountoutstandingdate` as amountou6_165_10_, underlying11_.`benchmarkstrategy` as benchmar7_165_10_, underlying11_.`contractvaluemultiplier` as contract8_165_10_, underlying11_.`createdate` as createdate9_165_10_, underlying11_.`defaultclause` as default10_165_10_, underlying11_.`firstamount` as firstam11_165_10_, underlying11_.`firstdate` as firstdate12_165_10_, underlying11_.`firstdealingdate` as firstde13_165_10_, underlying11_.`firsteventvalue` as firstev14_165_10_, underlying11_.`firstsettlementdate` as firstse15_165_10_, underlying11_.`lastamount` as lastamount16_165_10_, underlying11_.`lastdate` as lastdate17_165_10_, underlying11_.`lasteventvalue` as lasteve18_165_10_, underlying11_.`marketderivedinfperiddration` as marketd19_165_10_, underlying11_.`marketderivedinfoperiodend` as marketd20_165_10_, underlying11_.`marketderivedinfoperiodstart` as marketd21_165_10_, underlying11_.`modifieddate` as modifie22_165_10_, underlying11_.`name` as name23_165_10_, underlying11_.`nextamount` as nextamount24_165_10_, underlying11_.`nexteventvalue` as nexteve25_165_10_, underlying11_.`numberholders` as numberh26_165_10_, underlying11_.`numberholdersdatetime` as numberh27_165_10_, underlying11_.`nmberholderspreviousdatetime` as nmberho28_165_10_, underlying11_.`objective` as objective29_165_10_, underlying11_.`penultimateamount` as penulti30_165_10_, underlying11_.`penultimatedate` as penulti31_165_10_, underlying11_.`principalpaymentend` as princip32_165_10_, underlying11_.`principalpaymentstart` as princip33_165_10_, underlying11_.`purchasingminimums` as purchas34_165_10_, underlying11_.`qualifiedplans` as qualifi35_165_10_, underlying11_.`recurrable` as recurrable36_165_10_, underlying11_.`shortname` as shortname37_165_10_, underlying11_.`instrumentdomain` as instrum38_165_10_, underlying11_.`issuerclass` as issuerc39_165_10_, underlying12_.`calculationtype` as calculat2_329_11_, underlying12_.`indicatorstype` as indicato3_329_11_, underlying12_.`marketcenter` as marketce4_329_11_, underlying12_.`quantitydescriptiontype` as quantity5_329_11_, underlying12_.`quantitytype` as quantity6_329_11_, underlying12_.`valuationref` as valuatio7_329_11_, underlying12_.`valuationtype` as valuatio8_329_11_, underlying12_.`unittype` as unittype9_329_11_, underlying12_.instrumentDomain as instrum10_329_11_, underlying12_.`datetime` as datetime11_329_11_, underlying12_.`previousdatetime` as previou12_329_11_, underlying12_.`rank` as rank13_329_11_, underlying12_.`underlyingcount` as underly14_329_11_, underlying12_.`unitsize` as unitsize15_329_11_, instrument1_.`name` as name2_168_12_, instrument1_.`instrumentstatustype` as instrume3_168_12_, instrument1_.`scopetype` as scopetype4_168_12_, instrument1_.`tranche` as tranche5_168_12_, instrument1_.`marketstatustype` as marketst6_168_12_, instrument1_.`markettype` as markettype7_168_12_, instrument1_.`segmenttype` as segmentt8_168_12_, instrument1_.`schemeinfo` as schemeinfo9_168_12_, instrument1_.`country` as country10_168_12_, instrument1_.`currency` as currency11_168_12_, instrument1_.`instrumentdomain` as instrum12_168_12_, instrument1_.`background` as background13_168_12_, instrument1_.`code` as code14_168_12_, instrument1_.`comment` as comment15_168_12_, instrument1_.`createdate` as createdate16_168_12_, instrument1_.`end` as end17_168_12_, instrument1_.`marketconditions` as marketc18_168_12_, instrument1_.`modifieddate` as modifie19_168_12_, instrument1_.`segmentidentifiercode` as segment20_168_12_, instrument1_.`start` as start21_168_12_, instrument1_.`issuancesubclass` as issuanc22_168_12_, instrument1_.`corporatechangesubclass` as corpora23_168_12_, issueamoun16_.`calculationtype` as calculat2_180_13_, issueamoun16_.`indicatorstype` as indicato3_180_13_, issueamoun16_.`marketcenter` as marketce4_180_13_, issueamoun16_.`quantitydescriptiontype` as quantity5_180_13_, issueamoun16_.`quantitytype` as quantity6_180_13_, issueamoun16_.`valuationtype` as valuatio7_180_13_, issueamoun16_.`currency` as currency8_180_13_, issueamoun16_.`debtindicatorstype` as debtindi9_180_13_, issueamoun16_.`crossrate` as crossrate10_180_13_, issueamoun16_.`crossratemultiplier` as crossra11_180_13_, issueamoun16_.`datetime` as datetime12_180_13_, issueamoun16_.`issueamount` as issueam13_180_13_, issueamoun16_.`multiplier` as multiplier14_180_13_, issueamoun16_.`previousdatetime` as previou15_180_13_, issueamoun16_.`rank` as rank16_180_13_, issueamoun16_.`size` as size17_180_13_, industryid6_.`industryidentifiertype` as industry2_162_14_, industryid6_.`code` as code3_162_14_, industryid6_.`end` as end4_162_14_, industryid6_.`name` as name5_162_14_, industryid6_.`start` as start6_162_14_, industryid6_.`validity` as validity7_162_14_, industryid6_.`instrumentdomain` as instrume8_162_14_, currency5_.`full` as full2_83_15_, currency5_.`scheme` as scheme3_83_15_, currency5_.`short` as short4_83_15_, issuedata9_.issuanceSubclass as issuance2_181_16_, issuedata9_.indexDomain as indexDom3_181_16_, issuedata9_.interestRateClass as interest4_181_16_, issuedata9_.foreignExchangeDomain as foreignE5_181_16_, issuedata9_.industrySpecificClass as industry6_181_16_, issuedata9_.economicClass as economic7_181_16_, issuedata9_.demographicClass as demograp8_181_16_, issuedata9_.`issuemarkettype` as issuemar9_181_16_, issuedata9_.`issuerref` as issuerref10_181_16_, issuedata9_.`liquidationstatustype` as liquida11_181_16_, issuedata9_.`offertype` as offertype12_181_16_, issuedata9_.`programtype` as program13_181_16_, issuedata9_.`votingrightstype` as votingr14_181_16_, issuedata9_.`accrualbasistype` as accrual15_181_16_, issuedata9_.`accrualconventiontype` as accrual16_181_16_, issuedata9_.`indicatorstype` as indicat17_181_16_, issuedata9_.`modificationtype` as modific18_181_16_, issuedata9_.`agreedrestriction` as agreedr19_181_16_, issuedata9_.`allowedindebtedness` as allowed20_181_16_, issuedata9_.`announcementdate` as announc21_181_16_, issuedata9_.`auctiondate` as auction22_181_16_, issuedata9_.`closingdate` as closing23_181_16_, issuedata9_.`createdate` as createdate24_181_16_, issuedata9_.`defaultclause` as default25_181_16_, issuedata9_.`facevalue` as facevalue26_181_16_, issuedata9_.`issuedate` as issuedate27_181_16_, issuedata9_.`marginable` as marginable28_181_16_, issuedata9_.`marginablemaxlimit` as margina29_181_16_, issuedata9_.`marginableminlimit` as margina30_181_16_, issuedata9_.`modificationdatetime` as modific31_181_16_, issuedata9_.`modifieddate` as modifie32_181_16_, issuedata9_.`negativepledge` as negativ33_181_16_, issuedata9_.`refrainfromacts` as refrain34_181_16_, issuedata9_.`settlementdate` as settlem35_181_16_, issuedata9_.`underlyingcount` as underly36_181_16_, issuedata9_.`useofproceeds` as useofpr37_181_16_, issuedata9_.`validity` as validity38_181_16_, locationty18_.`full` as full2_201_17_, locationty18_.`scheme` as scheme3_201_17_, locationty18_.`short` as short4_201_17_, marketstat15_.`industryidentifier` as industry2_208_18_, marketstat15_.`instrumentidentifier` as instrume3_208_18_, marketstat15_.issueData as issueData208_18_, location13_.`issuerclass` as issuerc19___, location13_.`id` as id1___ from `instrumentdomain` instrument0_ left outer join `instrumentidentifier` instrument1_ on instrument0_.`id`=instrument1_.`instrumentdomain` left outer join `schemeinfo` schemeinfo2_ on instrument1_.`schemeinfo`=schemeinfo2_.`id` left outer join `instrumenttype` instrument3_ on instrument0_.`instrumenttype`=instrument3_.`id` left outer join `denomination` denominati4_ on instrument0_.`id`=denominati4_.`instrumentdomain` left outer join `currency` currency5_ on denominati4_.`currency`=currency5_.`id` left outer join `industryidentifier` industryid6_ on instrument0_.`id`=industryid6_.`instrumentdomain` left outer join `lastcae` lastcae7_ on instrument0_.`id`=lastcae7_.`id` left outer join `issuerclass` issuerclas8_ on instrument0_.`issuerclass`=issuerclas8_.`id` left outer join `issuedata` issuedata9_ on instrument0_.`id`=issuedata9_.`id` left outer join `debtissuedata` debtissued10_ on instrument0_.`id`=debtissued10_.`id` left outer join `instrumentdomain` underlying11_ on instrument0_.`id`=underlying11_.`instrumentdomain` left outer join `underlyingcount` underlying12_ on instrument0_.`id`=underlying12_.instrumentDomain left outer join `location` location13_ on issuerclas8_.`id`=location13_.`issuerclass` left outer join `portfoliodomain` portfoliod14_ on issuedata9_.`id`=portfoliod14_.issueData left outer join `marketstatsclass` marketstat15_ on issuedata9_.`id`=marketstat15_.issueData left outer join `issueamount` issueamoun16_ on issuedata9_.`id`=issueamoun16_.`id` left outer join `country` country17_ on location13_.`country`=country17_.`id` left outer join `locationtype` locationty18_ on location13_.`locationtype`=locationty18_.`id` where (instrument3_.`id` in(46 , 48 , 49 , 150 , 151 , 185 , 432 , 438 , 439 , 443 , 473))and(instrument0_.`name` like ? )
2009-01-3015:12:17.609 GMT+0000 277969 [btpool0-3 - /dwr/dwr/call/plaincall/SearchEquity.searchEquity.dwr] DEBUG org.hibernate.jdbc.AbstractBatcher  - about to open ResultSet (open ResultSets: 0, globally: 0)
2009-01-3015:12:17.687 GMT+0000 278047 [btpool0-3 - /dwr/dwr/call/plaincall/SearchEquity.searchEquity.dwr] DEBUG org.hibernate.loader.Loader  - result row: EntityKey[InstrumentDomain#594], null, null, null, null, null, EntityKey[InstrumentType#46], null, null, null, null, null, EntityKey[InstrumentIdentifier#1239], null, null, null, EntityKey[IssueData#594], null, null
2009-01-3015:12:17.687 GMT+0000 278047 [btpool0-3 - /dwr/dwr/call/plaincall/SearchEquity.searchEquity.dwr] DEBUG org.eclipse.emf.teneo.hibernate.mapping.identifier.IdentifierCacheHandler  - Setting id: 594 for object com.stpenable.gensec.impl.InstrumentDomainImpl in idcache 
2009-01-3015:12:17.687 GMT+0000 278047 [btpool0-3 - /dwr/dwr/call/plaincall/SearchEquity.searchEquity.dwr] DEBUG org.eclipse.emf.teneo.hibernate.mapping.property.EAttributePropertyHandler  - Expecting int as instance class but it is: java.lang.Integer
2009-01-3015:12:17.687 GMT+0000 278047 [btpool0-3 - /dwr/dwr/call/plaincall/SearchEquity.searchEquity.dwr] DEBUG org.eclipse.emf.teneo.hibernate.mapping.identifier.IdentifierCacheHandler  - Setting id: 46 for object com.stpenable.gensec.impl.InstrumentTypeImpl in idcache 
2009-01-3015:12:17.687 GMT+0000 278047 [btpool0-3 - /dwr/dwr/call/plaincall/SearchEquity.searchEquity.dwr] DEBUG org.eclipse.emf.teneo.hibernate.mapping.property.EAttributePropertyHandler  - Expecting int as instance class but it is: java.lang.Integer
2009-01-3015:12:17.687 GMT+0000 278047 [btpool0-3 - /dwr/dwr/call/plaincall/SearchEquity.searchEquity.dwr] DEBUG org.eclipse.emf.teneo.hibernate.mapping.identifier.IdentifierCacheHandler  - Setting id: 1239 for object com.stpenable.gensec.impl.InstrumentIdentifierImpl in idcache 
2009-01-3015:12:17.687 GMT+0000 278047 [btpool0-3 - /dwr/dwr/call/plaincall/SearchEquity.searchEquity.dwr] DEBUG org.eclipse.emf.teneo.hibernate.mapping.property.EAttributePropertyHandler  - Expecting int as instance class but it is: java.lang.Integer
2009-01-3015:12:17.687 GMT+0000 278047 [btpool0-3 - /dwr/dwr/call/plaincall/SearchEquity.searchEquity.dwr] DEBUG org.eclipse.emf.teneo.hibernate.mapping.identifier.IdentifierCacheHandler  - Setting id: 594 for object com.stpenable.gensec.impl.IssueDataImpl in idcache 
2009-01-3015:12:17.687 GMT+0000 278047 [btpool0-3 - /dwr/dwr/call/plaincall/SearchEquity.searchEquity.dwr] DEBUG org.eclipse.emf.teneo.hibernate.mapping.property.EAttributePropertyHandler  - Expecting int as instance class but it is: java.lang.Integer
2009-01-3015:12:17.703 GMT+0000 278063 [btpool0-3 - /dwr/dwr/call/plaincall/SearchEquity.searchEquity.dwr] DEBUG org.hibernate.jdbc.AbstractBatcher  - about to close ResultSet (open ResultSets: 1, globally: 1)
2009-01-3015:12:17.703 GMT+0000 278063 [btpool0-3 - /dwr/dwr/call/plaincall/SearchEquity.searchEquity.dwr] DEBUG org.hibernate.jdbc.AbstractBatcher  - about to close PreparedStatement (open PreparedStatements: 1, globally: 1)
2009-01-3015:15:38.531 GMT+0000 478891 [btpool0-3 - /dwr/dwr/call/plaincall/SearchEquity.searchEquity.dwr] ERROR com.stpware.secm.dwr.SearchEquity  - Hibernate error: 
org.hibernate.HibernateException: Unable to resolve property: issueData

If I remove this "left join fetch issd.issueAmount issamt" out of the above HQL it works?

I am not able to get reasonable performance out of Hibernate for my large complex model.

By changing all the HQL to instead use session.createSQLQuery I should be able to control everything I need. A shame that HQL and lazy loading do not do it for me.

If there are caveats to how lazy loading and the HQL are applied I have not found them in the documentation, or there is something about my model that forces the behaviour that is different than documented.

Thx.

David

Top

pkleindl

Post subject:

Posted: Fri Jan 30, 2009 12:15 pm

Senior

Joined: Thu Jan 08, 2009 3:48 pm
Posts: 168

some input:
- HQL ignores mapping like lazy=true/false
- one-to-one relationship is a bit special, i have seen it mapped with lazy=false or proxy and still get initialized
- if any of the FETCHed targets is collection you have to use distinct or cast to a set to remove doubles

suggestions to start:
- remove all the lazy=true/false/proxy
- try change one-to-one to many-to-one with insert=false and update=false and column={id}
- reduce the big join fetch statement to one or two fetches for starters just to identify problems easier

just do not mix too many options at once ;o)

Top

olddave

Post subject:

Posted: Fri Jan 30, 2009 1:06 pm

Newbie

Joined: Wed Jan 28, 2009 12:00 pm
Posts: 12

Hi,

This statement really concerns me
- one-to-one relationship is a bit special, i have seen it mapped with lazy=false or proxy and still get initialized

If that is the case they should state in the documentation under what circumstances they override this.

Secondly, if I have to change the model to a false one to overcome this overridden behaviour for one to one lazy loading this should be explained in the documentation. This makes the documentation incomplete!

If it had have been complete it would encourage me to use Teneo to generate JPOX as a first port of call instead of Hibernate.

Thx for the pointers. But at this stage I think rather than waste more hours experimenting I am better off reverting to using the direct SQL capability in Hibernate.

David

Top

pkleindl

Post subject:

Posted: Fri Jan 30, 2009 2:28 pm

Senior

Joined: Thu Jan 08, 2009 3:48 pm
Posts: 168

Hi

1) http://www.hibernate.org/162.html
It explains some things about the one-to-one, for my part i just don't like it very much
On the other hand, a data model based on shared primary keys isn't especially beautiful on it's own don't you think?

2) I believe not too many people are facing the challenge of one-to-one because this should only exist in legacy DBs (my opinion, could be quite different)

3) I think lots of your experimenting were only necessary because you tried too many things at once, get one of your many-to-one associations working with the join fetch and chances are high the rest will work in a similar fashion

I don't know what Teneo/JPOX are but as far as i've seen Hibernate works fine for a lot of things

Top

olddave

Post subject:

Posted: Tue Feb 03, 2009 7:55 am

Newbie

Joined: Wed Jan 28, 2009 12:00 pm
Posts: 12

Hi,

In answer to your second point: - I have broad modelling experience and there are many valid cases for 1to1 where one entity is optional. In my case a financial Instrument can have Issue data if it is a particular type of instument. Similarly for debt issue data, a last corporate action event and an underlying count. This is the correct model in this case.

In answer to your 3rd point: - I worked systematically through the user guide, trying the different approaches, although did manage to miss the bit on constrained = "true". I eliminated each approach when it did not give me reasonable performance, due to n+1 selects.

The left fetch join looks the closest to an answer I have, but it has a problem I expressed earlier in this thread which no one has addressed. It allowed 18 joins but not 19, it seemed the limit either through the length of the select string or maybe the max number of joins, or similar. It is just that the exception it throws does not seem to shed light on the real reason behind the failure.

In addition I tried createSQLQuery, but that has the same problem, even though I select the onetoone in the query and the associated objects are fetched, it insists on fetching them again in n+1 selects, which is an optimisation I am surprised that Hibernate does not have by now. Details in the other thread I created.

So at this stage I am going to run native SQL and build the POJOs I need directly, as Hibernate does not have an solution I can use.

Thx.

David

Top

olddave

Post subject:

Posted: Wed Feb 04, 2009 8:48 am

Newbie

Joined: Wed Jan 28, 2009 12:00 pm
Posts: 12

Hi,

To finalise my findings. Basically if you have 1to1 with an optional end then the only way to prevent n+1 selects is use createSQLQuery and addScalar. Then load your DTO objects directly.

You can barstadize your model to use mto1 in place of the 1to1. If you use HQL with left join fetch, that works so long as there are no associations at the edge of your network of returned objects that also have 1to1 with optional. Of course using fetch="join" when you generate works, but with a large complex model like mine you end up returning a lot of data you mostly never use.

Things that surprised me are that Hibernate is not optimised to use the objects in the network of objects returned using createSQLQuery and addEntity to satisfy the test for the optional end of any 1to1 associations, it just selects them all again one select per row per 1to1 association. Also that there is no option to get it to not test that the optional object in a 1to1 optional is available and put a null there until the getter for it is accessed, and only then fetch it. This would allow us to use the objects were we do not need to know or care if the optional end of a 1to1 association exists and have performance.

So I am in the process of rewriting nearly all my access thru Hibernate to use createSQLQuery and addScalar. Only where I need a single object will I leave it to it's 1+4 selects ( I have 4 1to1 with optional end on my central entity).

Thx for your help.

Top

Joseph-learner

Post subject:

Posted: Fri Feb 27, 2009 12:33 am

Newbie

Joined: Sat Jul 12, 2008 4:06 am
Posts: 2

olddave wrote:

I have similar problem and I kept pounding my head against the wall for almost two days. Hope what I found helps:

I have class A and B and they are in OneToOne relationship. With A on the owning side in the reslationship.

select cA from A as cA left join fetch B ==> works ok.

select cB from B as cB left join fetch A ===> trigger n+1 sqls from using A to get B.

It looks like the owning side of the relationship and which one u query on has an effect on this. If u directly query the owning class, and left join fetch, it would work. But not the other way around.

Top

Page 1 of 1

[ 10 posts ]

Board index » Hibernate & Java Persistence » Hibernate Users

All times are UTC - 5 hours [ DST ]

You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum