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

[Alan Lord]

Bump.

On 09/10/2018 09:31, Alan Lord wrote:
> 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:
> 
> V7.1
>> 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] 
>>
> 
> V6.3
>> 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 
> again.
> 
> 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?
> 
> 
> Cheers
> 
> Al
> 
> _______________________________________________
> http://www.vtiger.com/
>