View source | View content page | Page history | Printable version   
Toolbox
Main Page
Upload file
What links here
Recent changes
Help

PDF Books
Show collection (0 pages)
Collections help

Search

Projects:Grid Config For Foreign Key Drop Down Filters/QA

Contents

Functional Tests

NewGridConfiguration.png

For both subprojects it has been checked that the following features work as expected:

Do not filter the records in the filter drop down

When the flag is enabled:

Do not use the filter drop down

When the flag is enabled:

Performance Tests

Do not filter the records in the filter drop down

Without the new configuration

SELECT organizati0_.Value AS Value494_
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

Having 1.000.000 products and 19 organizations, this query takes 687 ms.

With the new configuration

SELECT organizati0_.Value AS Value494_
FROM AD_Org organizati0_ 
WHERE (organizati0_.AD_Client_ID IN ('39363B0921BB4293B48383844325E84C' , '0')) 
AND organizati0_.IsActive='Y' 
ORDER BY organizati0_.AD_Org_ID DESC LIMIT 76

Having 1.000.000 products and 19 organizations, this query takes 12 ms. It is 57 times faster than the query that results if the new configuration is not enabled.

Do not use the filter drop down

Without the new configuration

This query is done to populate the filter drop down of the Business Partner column of 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 1.000.000 business partners in that subtab, the query takes 2653 ms. Once the dropdown is populated, the query that results from selecting a specific business partner is very fast, taking aroung 11 ms.

With the new configuration

With the new configuration there is no dropdown and the column has to be filtered by entering the fk identifier and pressing enter / blurring out of the filter editor. This is the query done when 'arturo' is entered in the filter editor, after having configured the 'iStartsWith' as operator in the grid configuration:

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_.M_Offer_ID='95AF235142FB4656A4B7D5A08DE24927' 
AND (upper(businesspa1_.Name) LIKE upper('%arturo%') escape '|') 
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

Using this index, the query takes 12 ms (221 times faster than populating the dropdown):

 CREATE INDEX c_bpartner_name_upper 
 ON c_bpartner 
 USING btree (upper(name) COLLATE pg_catalog."default" varchar_pattern_ops);

TestLink Tests

Unfiltered Foreign Key Combo configuration works properly

Disable Foreign Key Filter Combo configuration works properly

Disable Foreign Key Filter Combo configuration works properly with previously defined saved views

Automated Tests

No automated tests have been built for this project. The main changes introduced by this project affect the way the queries are built in the back end, and there is no simple way to test this automatically.

Retrieved from "http://wiki.openbravo.com/wiki/Projects:Grid_Config_For_Foreign_Key_Drop_Down_Filters/QA"

This page has been accessed 261 times. This page was last modified on 12 May 2015, at 15:20. Content is available under Creative Commons Attribution-ShareAlike 2.5 Spain License.