Inefficient Multiple Subselect Query?

§upernova§oftware · **Posted:** Fri Jun 02, 2006 9:13 pm

I have the following query that I would appreciate any optimization advice. See explanation below.

Code:

        select distinct new Map(sp.state as receive)
        from Coupling c
        join c.statePrevious sp
        where (select count(sp0) from c.statePrevious sp0 where sp0.state in (from ReceiveCoupling))=1 and
              (select count(sp1) from c.statePrevious sp1 where sp1.state in (from InvoiceCoupling))=0 and
              sp.state in (from ReceiveCoupling) and
              c.shipment.poCoupling.size=:size and
              c.shipment.poCoupling.grade=:grade and
              c.shipment.poCoupling.endFinish=:endFinish and
              c.contact=:contact 

Coupling is a Joined Subclass of GenericItem and is mapped to StatePrevious as @OneToMany(mappedBy="genericItem").

ReceiveCoupling, InvoiceCoupling are Joined subclasses of State.

Every line item of inventory has a list of states that is has been through.

What I want is all ReceiveCoupling that have no InvoiceCoupling in its StatePrevious and has Couplings of the specific type attached.

I would greatly appreciate any help as this query runs unacceptably slow and there is a minute percentage of data currently in the database.

genekhart · **Joined:** Thu Jun 23, 2005 4:11 pm **Posts:** 24

If you are using MySQL I wouldn't even bother trying to use subselects at all until they implement some basic optimizations.

§upernova§oftware · **Posted:** Sat Jun 03, 2006 9:49 pm

I am using PostgreSQL 8.1.3.

genekhart · **Joined:** Thu Jun 23, 2005 4:11 pm **Posts:** 24

Have you tried looking at the resulting sql and performed an explain to see what the query engine is doing?

§upernova§oftware · **Posted:** Mon Jun 05, 2006 6:35 pm

The sql from Hibernate Console follows. I am fairly good a writing SQL and HQL, but do not have much experiece optimizing it.

Any advice would be appreciated.

Code:

select
  distinct stateprevi1_.state_id as col_0_0_ 
 from
  t_coupling coupling0_ 
 inner join
  t_generic_item coupling0_1_ 
   on coupling0_.id=coupling0_1_.id 
 inner join
  t_state_previous stateprevi1_ 
   on coupling0_.id=stateprevi1_.genericItem_id 
 inner join
  t_state state2_ 
   on stateprevi1_.state_id=state2_.id 
 left outer join
  t_po_coupling state2_1_ 
   on state2_.id=state2_1_.id 
 left outer join
  t_invoice_coupling state2_2_ 
   on state2_.id=state2_2_.id 
 left outer join
  t_receive_coupling state2_3_ 
   on state2_.id=state2_3_.id 
 left outer join
  t_shipment_coupling state2_4_ 
   on state2_.id=state2_4_.id 
 left outer join
  t_dock state2_5_ 
   on state2_.id=state2_5_.id 
 left outer join
  t_po_pipe state2_6_ 
   on state2_.id=state2_6_.id 
 left outer join
  t_shipment state2_7_ 
   on state2_.id=state2_7_.id 
 left outer join
  t_voyage state2_8_ 
   on state2_.id=state2_8_.id 
 left outer join
  t_cancel state2_9_ 
   on state2_.id=state2_9_.id 
 left outer join
  t_confirm state2_10_ 
   on state2_.id=state2_10_.id 
 left outer join
  t_hold state2_11_ 
   on state2_.id=state2_11_.id 
 left outer join
  t_move state2_12_ 
   on state2_.id=state2_12_.id 
 left outer join
  t_production state2_13_ 
   on state2_.id=state2_13_.id 
 left outer join
  t_receive state2_14_ 
   on state2_.id=state2_14_.id 
 left outer join
  t_reclassify state2_15_ 
   on state2_.id=state2_15_.id 
 left outer join
  t_release state2_16_ 
   on state2_.id=state2_16_.id 
 left outer join
  t_sale state2_17_ 
   on state2_.id=state2_17_.id 
 left outer join
  t_transportation state2_18_ 
   on state2_.id=state2_18_.id,
  t_shipment_coupling shipmentco8_ 
 inner join
  t_state shipmentco8_1_ 
   on shipmentco8_.id=shipmentco8_1_.id,
  t_po_coupling pocoupling9_ 
 inner join
  t_state pocoupling9_1_ 
   on pocoupling9_.id=pocoupling9_1_.id 
 where
  shipmentco8_.poCoupling_id=pocoupling9_.id 
  and coupling0_.shipment_id=shipmentco8_.id 
  and (
   select
    count(stateprevi3_.id) 
   from
    t_state_previous stateprevi3_ 
   where
    coupling0_.id=stateprevi3_.genericItem_id 
    and (
     stateprevi3_.state_id in (
      select
       receivecou4_.id 
      from
       t_receive_coupling receivecou4_ 
      inner join
       t_state receivecou4_1_ 
        on receivecou4_.id=receivecou4_1_.id
      )
     )
    )=1 
    and (
     select
      count(stateprevi5_.id) 
    from
     t_state_previous stateprevi5_ 
    where
     coupling0_.id=stateprevi5_.genericItem_id 
     and (
      stateprevi5_.state_id in (
       select
        invoicecou6_.id 
       from
        t_invoice_coupling invoicecou6_ 
       inner join
        t_state invoicecou6_1_ 
         on invoicecou6_.id=invoicecou6_1_.id
       )
      )
     )=0 
     and (
      stateprevi1_.state_id in (
       select
        receivecou7_.id 
      from
       t_receive_coupling receivecou7_ 
      inner join
       t_state receivecou7_1_ 
        on receivecou7_.id=receivecou7_1_.id
      )
     ) 
     and pocoupling9_1_.size_id=1 
     and pocoupling9_1_.grade_id=1 
     and pocoupling9_1_.endFinish_id=1 
     and coupling0_1_.contact_id=1