Projects:Grid Config For Foreign Key Drop Down Filters/Technical Specifications

Contents 1 Do not filter the records in the filter drop down 1.1 Comparison 2 Do not use the filter drop down 2.1 Populating the filter dropdown 2.2 Filtering using the id of the bussiness partner 2.3 Filtering using the identifier of the bussiness partner

Do not filter the records in the filter drop down

Currently the query done to populate a foreign key drop down is like this:

SELECT me FROM Organization AS me  
WHERE ( EXISTS (SELECT 1 FROM Product AS e INNER JOIN e.organization AS i 
                WHERE i = me AND e.organization.id IN (...) AND e.client.id  IN ('23C59575B9CF467C9620760EB255B389', '0')))
ORDER BY me.name

This query does not perform well in all scenarios. For instance, if the Organization filter drop down is opened in the Product window having millions of products and only a dozen organizations, it will take a long time (>10 seconds) to populate the drop down.

One way to improve this would be returning an ordered list of the identifiers of all the organizations, even if there are no products using them.

Comparison

Current SQL query, that took 705 ms having 1.000.000 products and 9 organizations:

SELECT organizati0_.Name AS Name494_
FROM AD_Org organizati0_ WHERE EXISTS (SELECT 1 
                                       FROM M_Product product1_ INNER JOIN AD_Org organizati2_ ON product1_.AD_Org_ID=organizati2_.AD_Org_ID 
                                       WHERE organizati2_.AD_Org_ID=organizati0_.AD_Org_ID 
                                       AND (product1_.AD_Org_ID IN ('D270A5AC50874F8BA67A88EE977F8E3B' , '4399136852B145BD96CC2A6CE0800C68' , '14B1927026BE471E9B85FE699BCA61C2' , '0' , 'B5DE96143D6642228E3B9DEC69886A47' , '3B187EC130A549A7A9388F8060EF156D' , '67839EEFA49E44AC969BD60093FCC899' , '01AD882EFC8545ACA6455E2F6FD51EE9' , 'BF129721D9FB4EB0819509934153E972')) 
                                       AND (product1_.AD_Client_ID IN ('39363B0921BB4293B48383844325E84C' , '0'))) 
ORDER BY organizati0_.Name 
LIMIT 76

Proposed SQL query, that took 12 ms:

SELECT name 
FROM ad_org 
WHERE ad_org_id IN ('D270A5AC50874F8BA67A88EE977F8E3B' , '4399136852B145BD96CC2A6CE0800C68' , '14B1927026BE471E9B85FE699BCA61C2' , '0' , 'B5DE96143D6642228E3B9DEC69886A47' , '3B187EC130A549A7A9388F8060EF156D' , '67839EEFA49E44AC969BD60093FCC899' , '01AD882EFC8545ACA6455E2F6FD51EE9' , 'BF129721D9FB4EB0819509934153E972')
AND ad_client_id IN ('39363B0921BB4293B48383844325E84C' , '0')
ORDER BY name, ad_org_id LIMIT 76

Do not use the filter drop down

Another scenario that could be improved is when the cost of populating the filter drop down is much higher than the cost of doing the filter using the startsWith operator. In that case, we could consider getting rid of the filter drop down.

Populating the filter dropdown

This is the query done to filter the business partner in the Business Partner subtab of the Discounts and Promotions window:

SELECT businesspa0_.name
FROM C_BPartner businesspa0_ 
WHERE EXISTS (SELECT 1 
              FROM M_Offer_BPartner pricingadj1_ 
              INNER JOIN C_BPartner businesspa2_ ON pricingadj1_.C_BPartner_ID=businesspa2_.C_BPartner_ID 
              WHERE pricingadj1_.M_Offer_ID='95AF235142FB4656A4B7D5A08DE24927' 
              AND businesspa2_.C_BPartner_ID=businesspa0_.C_BPartner_ID 
              AND (pricingadj1_.AD_Org_ID IN ('D270A5AC50874F8BA67A88EE977F8E3B' , '4399136852B145BD96CC2A6CE0800C68' , '14B1927026BE471E9B85FE699BCA61C2' , '0' , 'B5DE96143D6642228E3B9DEC69886A47' , '3B187EC130A549A7A9388F8060EF156D' , '67839EEFA49E44AC969BD60093FCC899' , '01AD882EFC8545ACA6455E2F6FD51EE9' , 'BF129721D9FB4EB0819509934153E972')) 
             AND (pricingadj1_.AD_Client_ID IN ('39363B0921BB4293B48383844325E84C' , '0'))) 
ORDER BY businesspa0_.Name 
LIMIT 76

Having a discount with 1.000.000 business partners, executing the query takes 2655 ms.

Filtering using the id of the bussiness partner

Once the dropdown is populated, filtering using the id of the business partners performs very well (it takes 12 ms):

SELECT pricingadj0_.M_Offer_Bpartner_ID AS M1_504_, pricingadj0_.AD_Client_ID AS AD2_504_, pricingadj0_.AD_Org_ID AS AD3_504_, pricingadj0_.IsActive AS IsActive504_, pricingadj0_.Created AS Created504_, pricingadj0_.Createdby AS Createdby504_, pricingadj0_.Updated AS Updated504_, pricingadj0_.Updatedby AS Updatedby504_, pricingadj0_.M_Offer_ID AS M9_504_, pricingadj0_.C_BPartner_ID AS C10_504_ 
FROM M_Offer_BPartner pricingadj0_ LEFT OUTER JOIN C_BPartner businesspa1_ ON pricingadj0_.C_BPartner_ID=businesspa1_.C_BPartner_ID 
WHERE pricingadj0_.C_BPartner_ID='100001' 
AND pricingadj0_.M_Offer_ID='95AF235142FB4656A4B7D5A08DE24927' 
AND (pricingadj0_.AD_Org_ID IN ('D270A5AC50874F8BA67A88EE977F8E3B' , '4399136852B145BD96CC2A6CE0800C68' , '14B1927026BE471E9B85FE699BCA61C2' , '0' , 'B5DE96143D6642228E3B9DEC69886A47' , '3B187EC130A549A7A9388F8060EF156D' , '67839EEFA49E44AC969BD60093FCC899' , '01AD882EFC8545ACA6455E2F6FD51EE9' , 'BF129721D9FB4EB0819509934153E972')) 
AND (pricingadj0_.AD_Client_ID IN ('39363B0921BB4293B48383844325E84C' , '0')) 
ORDER BY businesspa1_.Name, pricingadj0_.M_Offer_Bpartner_ID 
LIMIT '101'

Filtering using the identifier of the bussiness partner

Usually the query that results when filtering this way is much slower than the one that results when filtering using the id, but with a couple of improvements (removing unneded coalesce(to_char()) function calls and supporting indexes that use functions), the query is as performant as the one used when filtering using the id:

SELECT pricingadj0_.M_Offer_Bpartner_ID 
FROM M_Offer_BPartner pricingadj0_ LEFT OUTER JOIN C_BPartner businesspa1_ ON pricingadj0_.C_BPartner_ID=businesspa1_.C_BPartner_ID 
WHERE (upper(businesspa1_.Name) LIKE upper('art%') escape '|') 
AND pricingadj0_.M_Offer_ID='95AF235142FB4656A4B7D5A08DE24927' 
AND (pricingadj0_.AD_Org_ID IN ('D270A5AC50874F8BA67A88EE977F8E3B' , '4399136852B145BD96CC2A6CE0800C68' , '14B1927026BE471E9B85FE699BCA61C2' , '0' , 'B5DE96143D6642228E3B9DEC69886A47' , '3B187EC130A549A7A9388F8060EF156D' , '67839EEFA49E44AC969BD60093FCC899' , '01AD882EFC8545ACA6455E2F6FD51EE9' , 'BF129721D9FB4EB0819509934153E972')) 
AND (pricingadj0_.AD_Client_ID IN ('39363B0921BB4293B48383844325E84C' , '0')) 
ORDER BY businesspa1_.Name, pricingadj0_.M_Offer_Bpartner_ID 
LIMIT '101'

In most cases filtering using the identifier is much faster than populating the filter drop down. For instance, in the test environment there were 1.000.001 business partners in the Business Partner tab of the Discounts and Promotions window. 1.000.000 of them where called 'BusinessPartnerX', X being the textual representation of a number between 1 and 1.000.000. The other business partner was called Arturo Montoro. If 'Art' was entered in the filter editor, it took 12 ms to retrieve the proper row. If 'businesspartner10' was entered (111.000 business partners start with that substring), it took 121 ms. If 'businesspartner100' was entered, it took 23 ms. Remember that populating the drop down took 2655 ms.