0 Replies Latest reply on Mar 22, 2004 1:04 PM by Joachim Van der Auwera

    EJB-QL to SQL compiler sub-optimal

    Joachim Van der Auwera Expert

      The following EJB-QL query

      SELECT OBJECT(o) FROM Weight o
      WHERE
       ( o.sale.reference = ?1 )
      OR ( o.purchase.reference = ?2 )
      OR ( o.declaration.reference = ?3 )
      OR ( o.externalSale.reference = ?4 )
      OR ( o.externalPurchase.reference = ?5 )
      


      Is compiled to the following SQL query :

      SELECT t0_o.UNID_UOID
      FROM T_WEIGHT t0_o, T_WEIGHTGROUP t7_o_externalSale, T_WEIGHTGROUP t3_o_purchase, T_WEIGHTGROUP t1_o_sale, T_WEIGHTGROUP t5_o_declaration, T_WEIGHTGROUP t9_o_externalPurchase
      WHERE
      ((t1_o_sale.f_Reference = ?) AND t0_o.f_Sale_UOID=t1_o_sale.UNID_UOID)
      OR ((t3_o_purchase.f_Reference = ?) AND t0_o.f_Purchase_UOID=t3_o_purchase.UNID_UOID)
      OR ((t5_o_declaration.f_Reference = ?) AND t0_o.f_Declaration_UOID=t5_o_declaration.UNID_UOID)
      OR ((t7_o_externalSale.f_Reference = ?) AND t0_o.f_ExternalSale_UOID=t7_o_externalSale.UNID_UOID)
      OR ((t9_o_externalPurchase.f_Reference = ?) AND t0_o.f_ExternalPurchase_UOID=t9_o_externalPurchase.UNID_UOID)
      


      Which is very suboptimal (and actually makes the DB search for a very long time), while the simple query

      SELECT t0_o.UNID_UOID
      FROM T_WEIGHT t0_o, T_WEIGHTGROUP t1_o
      WHERE
      ((t1_o.f_Reference = ?) AND t0_o.f_Sale_UOID=t1_o.UNID_UOID)
      OR ((t1_o.f_Reference = ?) AND t0_o.f_Purchase_UOID=t1_o.UNID_UOID)
      OR ((t1_o.f_Reference = ?) AND t0_o.f_Declaration_UOID=t1_o.UNID_UOID)
      OR ((t1_o.f_Reference = ?) AND t0_o.f_ExternalSale_UOID=t1_o.UNID_UOID)
      OR ((t1_o.f_Reference = ?) AND t0_o.f_ExternalPurchase_UOID=t1_o.UNID_UOID)
      


      is equivalent and much easier/faster to evaluate for any DB.

      Joachim