[Vtigercrm-developers] Massive performance hit with Role based picklists in v7 compared to v6

Alan Lord alanslists at gmail.com
Tue Oct 9 08:31:06 GMT 2018

On a module where there are quite a few role based picklists and the 
system has a large number of roles/users, and your user is high up the 
Role hierarchy the page load time for Detail View is stupidly slow.

Compare these two queries from the logs:

> Prepared sql query being executed : SELECT distinct sales_stage FROM vtiger_sales_stage inner join vtiger_role2picklist on vtiger_sales_stage.picklist_valueid=vtiger_role2picklist.picklistvalueid and roleid in (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?) order by sortorderid
> Tue Oct  9 07:16:06 2018,350 [7280] DEBUG VT - Prepared sql query parameters : [H2,H202,H3,H38,H37,H112,H113,H172,H114,H115,H200,H116,H117,H173,H118,H119,H199,H120,H121,H168,H204,H122,H123,H124,H125,H183,H126,H127,H197,H129,H130,H131,H132,H133,H134,H188,H135,H145,H189,H136,H146,H193,H137,H147,H190,H138,H148,H191,H139,H149,H140,H150,H141,H151,H201,H142,H152,H143,H153,H186,H144,H203,H154,H187,H155,H156,H174,H157,H161,H158,H162,H159,H163,H16,H160,H164,H169,H170,H20,H17,H175,H179,H176,H180,H198,H177,H181,H178,H182,H21,H192,H184,H185,H22,H23,H171,H26,H52]

> ELECT sales_stage
>                   FROM vtiger_sales_stage
>                       INNER JOIN vtiger_role2picklist on vtiger_role2picklist.picklistvalueid = vtiger_sales_stage.picklist_valueid
>                   WHERE roleid=? and picklistid in (select picklistid from vtiger_picklist) order by sortorderid
> Tue Oct  9 07:29:27 2018,070 [6621] DEBUG VT - Prepared sql query parameters : [H2]

The difference appears to be due changes in the vtiger 
DetailRecordStructure model...

In v7 a role based picklist is generated by calling the 
getAssignedPicklistValues() function in the 
modules/Picklist/PicklistUtils.php file.

In v6, a role based picklist calls the 
Vtiger_Util_Helper::getRoleBasedPicklistValues() static method.

Note. This is not the same when you open the page in Edit view in v7, 
when the more simple getRoleBasedPicklistValues() static method is used 

Before I simply go and fix the Detail (and Summary) record structure 
models, I would like to know why the change was made and what the 
implications are of using the static method vs the 
getAssignedPicklistValues() function? I am not quite sure I understand 
why it is necessary to check all subordinate roles and why only in 
Detail/Summary and not in Edit?



More information about the vtigercrm-developers mailing list