Projects:Grid Config For Foreign Key Drop Down Filters/Technical Specifications
Contents |
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.