0024887: Bad perfromance in BPLoc query

diff -r 7bc3163005c1 src/org/openbravo/retail/posterminal/master/BPLocation.java
--- a/src/org/openbravo/retail/posterminal/master/BPLocation.java	Wed Oct 02 14:58:20 2013 +0000
+++ b/src/org/openbravo/retail/posterminal/master/BPLocation.java	Fri Oct 04 13:02:49 2013 +0200
@@ -51,48 +51,45 @@
             + regularBPLocationHQLProperties.getHqlSelect()
             + "from BusinessPartnerLocation bploc "
             + "where bploc.businessPartner.id in ("
-            + "SELECT bpl.businessPartner.id "
+            + "SELECT"
+            + " bpl.businessPartner.id "
             + "FROM BusinessPartnerLocation AS bpl left outer join bpl.businessPartner.aDUserList AS ulist "
-            + "WHERE (bpl.$incrementalUpdateCriteria or bpl.businessPartner.$incrementalUpdateCriteria or bpl.locationAddress.$incrementalUpdateCriteria or ulist.$incrementalUpdateCriteria) AND bpl.businessPartner.active=true AND ("
-            + "(bpl.id = '"
-            + org.getObretcoCBpLocation().getId()
-            + "')"
-            + " OR "
-            + "(bpl.businessPartner.id <> '"
-            + org.getObretcoCBpartner().getId()
-            + "' AND "
+            + "WHERE "
             + "bpl.invoiceToAddress = true AND "
             + "bpl.businessPartner.customer = true AND "
             + "bpl.businessPartner.priceList IS NOT NULL AND "
             + "bpl.$readableClientCriteria AND "
-            + "bpl.$naturalOrgCriteria "
-            + "))"
-            // This section is added to prevent more than one row
-            // for each business partner from
-            // being
-            // selected (check issues 22249 and 22256)
-            + " AND bpl.id in (SELECT max(bpl2.id)"
-            + "FROM BusinessPartnerLocation AS bpl2 "
-            + "WHERE (bpl2.$incrementalUpdateCriteria or bpl2.businessPartner.$incrementalUpdateCriteria or bpl2.locationAddress.$incrementalUpdateCriteria or ulist.$incrementalUpdateCriteria) AND ("
-            + "(bpl2.id = '"
-            + org.getObretcoCBpLocation().getId()
-            + "')"
-            + " OR "
-            + "(bpl2.businessPartner.id <> '"
-            + org.getObretcoCBpartner().getId()
-            + "' AND "
-            + "bpl2.invoiceToAddress = true AND "
-            + "bpl2.businessPartner.customer = true AND "
-            + "bpl2.$readableClientCriteria AND "
-            + "bpl2.$naturalOrgCriteria "
-            + ")) GROUP BY bpl2.businessPartner.id)"
-            + " AND (ulist is null or ulist.id in (select max(ulist2.id) from ADUser as ulist2 where ulist2.businessPartner is not null group by ulist2.businessPartner))"
-            // Here the section to prevent the same business partner
-            // from being selected more than
-            // once
-            // ends
-            + "GROUP BY bpl.businessPartner.id, bpl.businessPartner.organization.id, bpl.businessPartner.name, bpl.businessPartner.name, bpl.businessPartner.searchKey, bpl.businessPartner.description, bpl.businessPartner.taxID, bpl.businessPartner.sOBPTaxCategory.id, bpl.businessPartner.priceList.id, bpl.businessPartner.paymentMethod.id, bpl.businessPartner.paymentTerms.id, bpl.businessPartner.invoiceTerms, bpl.id, ulist.email, ulist.id, ulist.phone,bpl.locationAddress.cityName, bpl.locationAddress.postalCode, bpl.businessPartner.businessPartnerCategory.id, bpl.businessPartner.businessPartnerCategory.name, bpl.businessPartner.creditLimit, bpl.businessPartner.creditUsed "
-            + "ORDER BY bpl.businessPartner.name" + ") ORDER BY bploc.locationAddress.addressLine1");
+            + "bpl.$naturalOrgCriteria AND"
+            + " (bpl.$incrementalUpdateCriteria or bpl.businessPartner.$incrementalUpdateCriteria or bpl.locationAddress.$incrementalUpdateCriteria or ulist.$incrementalUpdateCriteria) AND bpl.businessPartner.active=true"
+            + " and bpl.id in (select max(bpls.id) as bpLocId from BusinessPartnerLocation AS bpls where bpls.$readableClientCriteria AND "
+            + " bpls.$naturalOrgCriteria group by bpls.businessPartner.id)"
+            + " and (ulist.id in (select max(ulist2.id) from ADUser as ulist2 where ulist2.businessPartner is not null group by ulist2.businessPartner))"
+            + " GROUP BY bpl.businessPartner.id, bpl.businessPartner.organization.id, bpl.businessPartner.name, bpl.businessPartner.name, bpl.businessPartner.searchKey, bpl.businessPartner.description, bpl.businessPartner.taxID, bpl.businessPartner.sOBPTaxCategory.id, bpl.businessPartner.priceList.id, bpl.businessPartner.paymentMethod.id, bpl.businessPartner.paymentTerms.id, bpl.businessPartner.invoiceTerms, ulist.email, ulist.phone,bpl.locationAddress.cityName, bpl.locationAddress.postalCode, bpl.businessPartner.businessPartnerCategory.id, bpl.businessPartner.businessPartnerCategory.name, bpl.businessPartner.creditLimit, bpl.businessPartner.creditUsed, bpl.id , ulist.id  "
+            + " ORDER BY bpl.businessPartner.name"
+            + ") ORDER BY bploc.locationAddress.addressLine1");
+
+    hqlQueries
+        .add("select"
+            + regularBPLocationHQLProperties.getHqlSelect()
+            + "from BusinessPartnerLocation bploc "
+            + "where bploc.businessPartner.id in ("
+            + "SELECT "
+            + " bpl.businessPartner.id "
+            + "FROM BusinessPartnerLocation AS bpl left outer join bpl.businessPartner.aDUserList AS ulist "
+            + "WHERE "
+            + "bpl.invoiceToAddress = true AND "
+            + "bpl.businessPartner.customer = true AND "
+            + "bpl.businessPartner.priceList IS NOT NULL AND "
+            + "bpl.$readableClientCriteria AND "
+            + "bpl.$naturalOrgCriteria AND"
+            + "(bpl.$incrementalUpdateCriteria or bpl.businessPartner.$incrementalUpdateCriteria or bpl.locationAddress.$incrementalUpdateCriteria or ulist.$incrementalUpdateCriteria) AND bpl.businessPartner.active=true"
+            + " and bpl.id in (select max(bpls.id) as bpLocId from BusinessPartnerLocation AS bpls where bpls.$readableClientCriteria AND "
+            + " bpls.$naturalOrgCriteria group by bpls.businessPartner.id)"
+            + " and ulist.id is null "
+            + " GROUP BY bpl.businessPartner.id, bpl.businessPartner.organization.id, bpl.businessPartner.name, bpl.businessPartner.name, bpl.businessPartner.searchKey, bpl.businessPartner.description, bpl.businessPartner.taxID, bpl.businessPartner.sOBPTaxCategory.id, bpl.businessPartner.priceList.id, bpl.businessPartner.paymentMethod.id, bpl.businessPartner.paymentTerms.id, bpl.businessPartner.invoiceTerms, ulist.email, ulist.phone,bpl.locationAddress.cityName, bpl.locationAddress.postalCode, bpl.businessPartner.businessPartnerCategory.id, bpl.businessPartner.businessPartnerCategory.name, bpl.businessPartner.creditLimit, bpl.businessPartner.creditUsed, bpl.id , ulist.id  "
+            + " ORDER BY bpl.businessPartner.name"
+            + ") ORDER BY bploc.locationAddress.addressLine1");
+
     return hqlQueries;
   }