Help in making this query much faster

egervari · **Joined:** Sun Oct 26, 2003 5:05 am **Posts:** 139

Hi, I could use some help in making this query in the source code much more performant. I'm using postgres as stated below and my table does not have any indexes just yet (since I here you can make it slower if you don't know what you are doing, which I don't). Right now it executes at 125 milliseconds. And yes, I have spring aop transaction management configured properly, so it's within a transaction and a session close if that makes any difference on performance.

Hibernate version:
2.1, latest off homepage

Mapping documents:

Code:

   <class
      name="com.upfactor.rns.domain.business.Business"
      lazy="true"
      table="business"
      >
      <id name="id" column="id" type="long" unsaved-value="0">
         <generator class="sequence">
            <param name="sequence">business_id_seq</param>
         </generator>
      </id>
      <many-to-one name="administrator" column="administrator_id" class="com.upfactor.rns.domain.business.Administrator"/>
      <property name="mailbox" column="mailbox"/>
      <property name="name" column="name"/>
      <property name="address" column="address"/>
      <property name="city" column="city"/>
      <property name="province" column="province"/>
      <property name="postalCode" column="postal_code"/>
      <many-to-one name="country" column="country_id" class="com.upfactor.rns.domain.Country"/>
      <property name="phoneNumber" column="phone_number"/>
      <property name="faxNumber" column="fax_number"/>
      <set name="persons" table="business_to_person" lazy="true">
         <key column="business_id"/>
         <many-to-many column="person_id" class="com.upfactor.rns.domain.business.Person"/>
      </set>
      <property name="accountState" column="account_state"/>
      <property name="type" column="type"/>
   </class>

   <class name="com.upfactor.rns.domain.core.ReleaseOffice" table="release_office">
      <cache usage="read-write" />
      <id name="id" column="id" type="long" unsaved-value="0">
         <generator class="sequence">
            <param name="sequence">release_office_id_seq</param>
         </generator>
      </id>
      <property name="name" column="name" />
      <property name="number" column="number" />
   </class>

   <class name="com.upfactor.rns.domain.core.ServiceOption" table="service_option">
      <cache usage="read-write" />
      <id name="id" column="id" type="long" unsaved-value="0">
         <generator class="sequence">
            <param name="sequence">service_option_id_seq</param>
         </generator>
      </id>
      <property name="name" column="name" />
      <property name="number" column="number" />
   </class>

   <class name="com.upfactor.rns.domain.core.SubLocation" table="sub_location">
      <cache usage="read-write" />
      <id name="id" column="id" type="long" unsaved-value="0">
         <generator class="sequence">
            <param name="sequence">sub_location_id_seq</param>
         </generator>
      </id>
      <property name="name" column="name" />
      <property name="number" column="number" />
   </class>

   <class name="com.upfactor.rns.domain.core.RnsTransaction" table="rns_transaction">
      <id name="id" column="id" type="long" unsaved-value="0">
         <generator class="sequence">
            <param name="sequence">rns_transaction_id_seq</param>
         </generator>
      </id>
      <many-to-one name="business" column="business_id"
         class="com.upfactor.rns.domain.business.Business" />
      <property name="cargoControlNumber" column="cargo_control_number" />
      <property name="transactionNumber" column="transaction_number" />
      <many-to-one name="serviceOption" column="service_option_id" class="com.upfactor.rns.domain.core.ServiceOption"/>
      <many-to-one name="releaseOffice" column="release_office_id" class="com.upfactor.rns.domain.core.ReleaseOffice"/>
      <many-to-one name="subLocation" column="sub_location_id" class="com.upfactor.rns.domain.core.SubLocation"/>
      <property name="containerNumber" column="container_number" />
      <bag name="histories" table="rns_transaction_history" inverse="true"
         order-by="date desc" cascade="save-update">
         <key column="rns_transaction_id"/>
         <one-to-many class="com.upfactor.rns.domain.core.RnsTransactionHistory" />
      </bag>
   </class>

   <class
      name="com.upfactor.rns.domain.core.RnsTransactionHistory"
      proxy="com.upfactor.rns.domain.core.RnsTransactionHistory"
      table="rns_transaction_history"
   >
      <id name="id" column="id" type="long" unsaved-value="0">
         <generator class="sequence">
            <param name="sequence">rns_transaction_history_id_seq</param>
         </generator>
      </id>
      <many-to-one name="rnsTransaction" column="rns_transaction_id" class="com.upfactor.rns.domain.core.RnsTransaction"/>
      <property name="date" column="date" />
      <many-to-one name="releaseCode" column="release_code_id" class="com.upfactor.rns.domain.core.ReleaseCode"/>
      <property name="ediMessage" column="edi_message" />
      <property name="deliveryInstructions" column="delivery_instructions" />
   </class>

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

Code:

   public List findAllByReleaseCodes(
      final Long businessId, final List releaseCodes, final int firstResult, final int maxResults )
   {
        return getHibernateTemplate().executeFind( new HibernateCallback() {
         public Object doInHibernate( Session session ) throws HibernateException, SQLException {
            Query query = session.createQuery(
               "select transaction " +
               "from RnsTransaction transaction left outer join transaction.histories history " +
               "where history.date = ( " +
               "  select max( history.date ) from RnsTransactionHistory history " +
               "  where history in elements(transaction.histories) " +
               ") and " +
               "transaction.business.id = :businessId and " +
               "history.releaseCode.number = :releaseCodeNumber " +
               "order by history.date desc"
            );

            query.setParameter( "businessId", businessId );
            query.setParameter( "releaseCodeNumber", releaseCodes.get( 0 ) );

            query.setFirstResult( firstResult );
            query.setMaxResults( maxResults );

            return query.list();
         }
      } );

Name and version of the database you are using:
Postgres 7.5

Thanks for your help.

anthony · **Posted:** Mon Aug 23, 2004 2:56 am

how many sql queries are generated?

egervari · **Joined:** Sun Oct 26, 2003 5:05 am **Posts:** 139

anthony wrote:

how many sql queries are generated?

Actually a lot:

Code:

[08:54:26,515]DEBUG SQL:226 - select rnstransac0_.id as id, rnstransac0_.business_id as business2_, rnstransac0_.cargo_control_number as cargo_co3_, rnstransac0_.transaction_number as transact4_, rnstransac0_.service_option_id as service_5_, rnstransac0_.release_office_id as release_6_, rnstransac0_.sub_location_id as sub_loca7_, rnstransac0_.container_number as containe8_ from rns_transaction rnstransac0_ left outer join rns_transaction_history histories1_ on rnstransac0_.id=histories1_.rns_transaction_id, release_code releasecod4_ where (histories1_.date=(select max(rnstransac2_.date) from rns_transaction_history rnstransac2_ where (rnstransac2_.id in(select histories3_.id from rns_transaction_history histories3_ where rnstransac0_.id=histories3_.rns_transaction_id))))and(rnstransac0_.business_id=? )and(releasecod4_.number=?  and histories1_.release_code_id=releasecod4_.id) order by  histories1_.date desc limit ?
[08:54:26,593]DEBUG SQL:226 - select histories0_.rns_transaction_id as rns_tran2___, histories0_.id as id__, histories0_.id as id1_, histories0_.rns_transaction_id as rns_tran2_1_, histories0_.date as date1_, histories0_.release_code_id as release_4_1_, histories0_.edi_message as edi_mess5_1_, histories0_.delivery_instructions as delivery6_1_, releasecod1_.id as id0_, releasecod1_.name as name0_, releasecod1_.number as number0_ from rns_transaction_history histories0_ left outer join release_code releasecod1_ on histories0_.release_code_id=releasecod1_.id where histories0_.rns_transaction_id=? order by histories0_.date desc
[08:54:26,609]DEBUG SQL:226 - select histories0_.rns_transaction_id as rns_tran2___, histories0_.id as id__, histories0_.id as id1_, histories0_.rns_transaction_id as rns_tran2_1_, histories0_.date as date1_, histories0_.release_code_id as release_4_1_, histories0_.edi_message as edi_mess5_1_, histories0_.delivery_instructions as delivery6_1_, releasecod1_.id as id0_, releasecod1_.name as name0_, releasecod1_.number as number0_ from rns_transaction_history histories0_ left outer join release_code releasecod1_ on histories0_.release_code_id=releasecod1_.id where histories0_.rns_transaction_id=? order by histories0_.date desc
[08:54:26,625]DEBUG SQL:226 - select histories0_.rns_transaction_id as rns_tran2___, histories0_.id as id__, histories0_.id as id1_, histories0_.rns_transaction_id as rns_tran2_1_, histories0_.date as date1_, histories0_.release_code_id as release_4_1_, histories0_.edi_message as edi_mess5_1_, histories0_.delivery_instructions as delivery6_1_, releasecod1_.id as id0_, releasecod1_.name as name0_, releasecod1_.number as number0_ from rns_transaction_history histories0_ left outer join release_code releasecod1_ on histories0_.release_code_id=releasecod1_.id where histories0_.rns_transaction_id=? order by histories0_.date desc
[08:54:26,625]DEBUG SQL:226 - select histories0_.rns_transaction_id as rns_tran2___, histories0_.id as id__, histories0_.id as id1_, histories0_.rns_transaction_id as rns_tran2_1_, histories0_.date as date1_, histories0_.release_code_id as release_4_1_, histories0_.edi_message as edi_mess5_1_, histories0_.delivery_instructions as delivery6_1_, releasecod1_.id as id0_, releasecod1_.name as name0_, releasecod1_.number as number0_ from rns_transaction_history histories0_ left outer join release_code releasecod1_ on histories0_.release_code_id=releasecod1_.id where histories0_.rns_transaction_id=? order by histories0_.date desc
[08:54:26,640]DEBUG SQL:226 - select histories0_.rns_transaction_id as rns_tran2___, histories0_.id as id__, histories0_.id as id1_, histories0_.rns_transaction_id as rns_tran2_1_, histories0_.date as date1_, histories0_.release_code_id as release_4_1_, histories0_.edi_message as edi_mess5_1_, histories0_.delivery_instructions as delivery6_1_, releasecod1_.id as id0_, releasecod1_.name as name0_, releasecod1_.number as number0_ from rns_transaction_history histories0_ left outer join release_code releasecod1_ on histories0_.release_code_id=releasecod1_.id where histories0_.rns_transaction_id=? order by histories0_.date desc
[08:54:26,671]DEBUG SQL:226 - select histories0_.rns_transaction_id as rns_tran2___, histories0_.id as id__, histories0_.id as id1_, histories0_.rns_transaction_id as rns_tran2_1_, histories0_.date as date1_, histories0_.release_code_id as release_4_1_, histories0_.edi_message as edi_mess5_1_, histories0_.delivery_instructions as delivery6_1_, releasecod1_.id as id0_, releasecod1_.name as name0_, releasecod1_.number as number0_ from rns_transaction_history histories0_ left outer join release_code releasecod1_ on histories0_.release_code_id=releasecod1_.id where histories0_.rns_transaction_id=? order by histories0_.date desc
[08:54:26,687]DEBUG SQL:226 - select histories0_.rns_transaction_id as rns_tran2___, histories0_.id as id__, histories0_.id as id1_, histories0_.rns_transaction_id as rns_tran2_1_, histories0_.date as date1_, histories0_.release_code_id as release_4_1_, histories0_.edi_message as edi_mess5_1_, histories0_.delivery_instructions as delivery6_1_, releasecod1_.id as id0_, releasecod1_.name as name0_, releasecod1_.number as number0_ from rns_transaction_history histories0_ left outer join release_code releasecod1_ on histories0_.release_code_id=releasecod1_.id where histories0_.rns_transaction_id=? order by histories0_.date desc
[08:54:26,703]DEBUG SQL:226 - select histories0_.rns_transaction_id as rns_tran2___, histories0_.id as id__, histories0_.id as id1_, histories0_.rns_transaction_id as rns_tran2_1_, histories0_.date as date1_, histories0_.release_code_id as release_4_1_, histories0_.edi_message as edi_mess5_1_, histories0_.delivery_instructions as delivery6_1_, releasecod1_.id as id0_, releasecod1_.name as name0_, releasecod1_.number as number0_ from rns_transaction_history histories0_ left outer join release_code releasecod1_ on histories0_.release_code_id=releasecod1_.id where histories0_.rns_transaction_id=? order by histories0_.date desc

And this just seems to get bigger as I allow more max results :/

michael · **Posted:** Mon Aug 23, 2004 9:00 am

Look at lazy="true" and outer-join="true"

anthony · **Posted:** Mon Aug 23, 2004 10:13 am

also take a look at FETCH query keyword

all is in the doc

egervari · **Joined:** Sun Oct 26, 2003 5:05 am **Posts:** 139

It seems that fetching helps quite a bit as opposed to Hibernate.initialize(). I'm noticing that the queries are getting faster. Is there anything I can do in postgres to make them even faster?

egervari · **Joined:** Sun Oct 26, 2003 5:05 am **Posts:** 139

Arg... while I get the queries to work nice on a moderate dataset, they do not work right on large amounts of data still. For instance, I'm trying to search through about 1200 rns transcations rows and running this query takes about ~2 seconds even with the fetching. I'm not an expert about databases, so this could be normal even without hibernate, or it could just be postgres. I am very concerned though since this database is going to be filled with hundreds of thousands of rns transaction rows this year alone. Working with 1200 is just a months worth a usage of the current system with only 2 users, but now that we will get 20 or 30 users, I can see this being a big problem.

Can anyone give me some more pointers like removing the sub-select or other optimiziations? Thanks so much.

rollatwork · **Posted:** Mon Aug 23, 2004 1:45 pm

What sort of optimizations have you applied since reading the docs?

Read the Performance chapter in the Hibernate docs. It made my life a whole lot simpler.

Also, does your app have any of the anti-patterns mentioned in this blog? http://blog.hibernate.org/cgi-bin/blosxom.cgi/Gavin%20King/perf-problems.html

egervari · **Joined:** Sun Oct 26, 2003 5:05 am **Posts:** 139

rollatwork wrote:

What sort of optimizations have you applied since reading the docs?

Read the Performance chapter in the Hibernate docs. It made my life a whole lot simpler.

Also, does your app have any of the anti-patterns mentioned in this blog? http://blog.hibernate.org/cgi-bin/blosxom.cgi/Gavin%20King/perf-problems.html

Actually, I don't think I'm doing anything wrong with respect to his blog. I'm spring transactions, which pretty much guarentees that everything is happening in one session for certain request (since I'm just calling the business service method once, where a single session/transaction is used. Furthermore, this query is the only thing that is being called in this case, so it's pretty hard to see that this is a session problem. I think transaction/session problems pertain to stores anyway, in which I'm having ZERO problems there - everything is lightning fast.

As for the n+1 selects problem, I don't think it's happening. I do a debug on my generated queries and it's 1 sql select for every method call now. This leads me to believe that something is very, very wrong :/

anthony · **Posted:** Mon Aug 23, 2004 2:27 pm

not specially, depending the db, the schema, the joined table in the query and the amount of data... the query may be long. And 2 medium queries can be faster than one big.

Try a workaround in the "in elements" part of your query, and ask your dba help

egervari · **Joined:** Sun Oct 26, 2003 5:05 am **Posts:** 139

Well, we don't have a dba. In fact, I'm the closest thing we have for a dba and that isn't saying all that much since I'm a programmer and understand higher level things much better than little details.

One thing I noticed is that when I run the below query (similar to the other one), it will take the same time no matter what I specify as the max results.

Code:

               Query query = session.createQuery(
                   "select rnsTransaction " +
                  "from RnsTransaction rnsTransaction " +
                  "   left join fetch rnsTransaction.histories as history " +
                  "where rnsTransaction.business.id = :businessId and " +
                  "history.date = ( " +
                  "   select max( history.date ) from RnsTransactionHistory as history " +
                  "   where history.rnsTransaction = rnsTransaction " +
                  ") and " +
                  "history.date between :from and :to " +
                  "order by history.date desc "
               );

So if I go query.setMaxResults( 1 ) or query.sexMaxResults( 500 ), it still takes the same amount of time. For me, this sounds like a big problem and I don't have the experience to know how to fix it :/ This query usually takes 1900-2100 milliseconds to execute.

This does make me wonder where the time is being spent at. If Hibernate can map RnsTransaction object in roughly the same as it can make 1000, then the time must be spent inside the database, no? How can postgres be that terrible?

Another thing I noticed that if I take out the sub-select, the time improves to 500-700 milliseconds but that makes the query incorrect. I just wanted to know how much that part of the query was slowing it down.

I'm really at a loss and I'm getting really worried that I can't make this work :/

egervari · **Joined:** Sun Oct 26, 2003 5:05 am **Posts:** 139

I do an explain for this query, where business_id = 2, limit 500 offset 0 and the dates are everything in the system (some 2003 and 2005 date)

Code:

explain select rnstransac0_.id as id0_, histories3_.id as id1_, sublocatio1_.id as id2_, releaseoff2_.id as id3_, rnstransac0_.business_id as business2_0_, rnstransac0_.cargo_control_number as cargo_co3_0_, rnstransac0_.transaction_number as transact4_0_, rnstransac0_.service_option_id as service_5_0_, rnstransac0_.release_office_id as release_6_0_, rnstransac0_.sub_location_id as sub_loca7_0_, rnstransac0_.container_number as containe8_0_, histories3_.rns_transaction_id as rns_tran2_1_, histories3_.date as date1_, histories3_.release_code_id as release_4_1_, histories3_.edi_message as edi_mess5_1_, histories3_.delivery_instructions as delivery6_1_, sublocatio1_.name as name2_, sublocatio1_.number as number2_, releaseoff2_.name as name3_, releaseoff2_.number as number3_, histories3_.rns_transaction_id as rns_tran2___, histories3_.id as id__ from rns_transaction rnstransac0_ left outer join sub_location sublocatio1_ on rnstransac0_.sub_location_id=sublocatio1_.id left outer join release_office releaseoff2_ on rnstransac0_.release_office_id=releaseoff2_.id left outer join rns_transaction_history histories3_ on rnstransac0_.id=histories3_.rns_transaction_id where (rnstransac0_.business_id=2 )and(histories3_.date=(select max(rnstransac4_.date) from rns_transaction_history rnstransac4_ where (rnstransac4_.rns_transaction_id=rnstransac0_.id )))and(histories3_.date between '2002-08-04' and '2005-08-20' ) order by  histories3_.date desc, histories3_.date desc limit 500 offset 0

Postgres responds with:

Code:

Limit  (cost=148.36..148.37 rows=1 width=381) 
  ->  Sort  (cost=148.36..148.37 rows=1 width=381) 
        Sort Key: histories3_.date 
        ->  Merge Join  (cost=29.67..148.35 rows=1 width=381) 
              Merge Cond: ("outer".id = "inner".rns_transaction_id) 
              Join Filter: ("inner".date = (subplan)) 
              ->  Nested Loop Left Join  (cost=0.00..92.13 rows=4 width=241) 
                    ->  Nested Loop Left Join  (cost=0.00..70.31 rows=4 width=161) 
                          ->  Index Scan using rns_transaction_pkey on rns_transaction rnstransac0_  (cost=0.00..49.41 rows=4 width=106) 
                                Filter: (business_id = 2) 
                          ->  Index Scan using sub_location_pkey on sub_location sublocatio1_  (cost=0.00..5.21 rows=1 width=55) 
                                Index Cond: ("outer".sub_location_id = sublocatio1_.id) 
                    ->  Index Scan using release_office_pkey on release_office releaseoff2_  (cost=0.00..5.44 rows=1 width=80) 
                          Index Cond: ("outer".release_office_id = releaseoff2_.id) 
              ->  Sort  (cost=29.67..29.69 rows=7 width=140) 
                    Sort Key: histories3_.rns_transaction_id 
                    ->  Seq Scan on rns_transaction_history histories3_  (cost=0.00..29.57 rows=7 width=140) 
                          Filter: ((date >= '2002-08-04 00:00:00-04'::timestamp with time zone) AND (date <= '2005-08-20 00:00:00-04'::timestamp with time zone)) 
              SubPlan 
                ->  Aggregate  (cost=26.50..26.50 rows=1 width=8) 
                      ->  Seq Scan on rns_transaction_history rnstransac4_  (cost=0.00..26.48 rows=7 width=8) 
                            Filter: (rns_transaction_id = $0) 

I run this query normal without explain and it takes about 2 seconds. Thus, this can't be hibernate's fault. Arg. I'm so screwed :/

egervari · **Joined:** Sun Oct 26, 2003 5:05 am **Posts:** 139

Creating this index seemed to make the time drop to ~280 millseconds, which is a big improvement. Can anyone suggest other indexs that I could add?

CREATE INDEX rns_transaction_history_rns_transaction_id_index ON rns_transaction_history USING btree (rns_transaction_id)

christian · **Posted:** Mon Aug 23, 2004 7:01 pm

Instead of continuing this ad-hoc optimization strategy, please pick up a copy of "SQL Tuning" by Dan Tow and read it.

egervari · **Joined:** Sun Oct 26, 2003 5:05 am **Posts:** 139

christian wrote:

Instead of continuing this ad-hoc optimization strategy, please pick up a copy of "SQL Tuning" by Dan Tow and read it.

Yeah, I think that's probably the best thing. However, I think I do understand it a bit more through this 'ad-hoc' performance tuning, at least more than I did before. To optimize my 6 queries, I settled on 2 indexes that did major improvements and did no more. I realize that adding indexes on tables with 10 or 20 rows is pointless and that fetch joining is increases performance a good deal. I also realized that the query had a big problem in that it was doing history in elements(rnsTransaction.histories) when it could have done history.rnsTransaction.id = rnsTransaction, saving a nested sub-select within the hql sub-query. This nearly split the time required to perform the query in half. Then my index chooped it up in a 1/4 of even that time. I can't make it go any faster than 270 milliseconds though, but it's so much better that I'm not complaining I guess. At least now I got some time to read the book before the next performance crisis happens. Thanks everyone.