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

[Alan Lord]

Thanks for the reply Prasad,

I'm fine thanks.

My customer isn't impressed by the time it takes to load a Detail View 
of a large module now we have migrated them to v7.

There are several hundred users, lots of Roles and lots of role-based 
picklists. If a user is toward the end of a branch of the role hierarchy 
it can take 10s of seconds for a Detail view page to load. Edit View is 
fine. On v6 it was never a problem.

I have suggested we migrate as many of the picklists as possible to 
non-role-based, but this didn't seem to make a huge difference as there 
were still quite a few which need to be role based, plus some 
multi-picklist fields too.

So, I guess from what you say, if I simply change the call to the 
getAssignedPicklistValues() function in the 
setupAccessiblePicklistValueList() method of the DetailRecordStructure 
all that is really going to change, apart from a speed improvement, is 
the re-appearance of some of these "Not Accessible" values?

Thanks

Al


On 17/10/2018 14:10, Prasad wrote:
> Alan,
> 
> How are you doing well.
> 
> The intention about this behaviour change was:
> 
>   * Picklist value assignment to role was supposed to control the
>     transition into the state.
>       o Allow user to view actual value assigned to field (Detail)
>       o Deny user from changing to value he/she not assigned to (Edit).
> 
> Why?
> 
>   * V6 was showing 'Not Accessible' in Detail of record if picklist
>     value was not assigned to user.
>       o This was very confusing and troubling user to carry out
>         filtering or acting on record.
> 
> So was the changes you pointed.
> 
> Regards,
> Prasad
> 
> --
> FB <http://www.facebook.com/vtiger> I Twit 
> <http://twitter.com/vtigercrm> I LIn 
> <https://www.linkedin.com/company/1270573?trk=tyah> I Blog 
> <https://blogs.vtiger.com> I Website <https://www.vtiger.com/>
> 
> On Wed, Oct 17, 2018 at 1:36 PM, Alan Lord 
> <alanslists at gmail.com 
> <mailto:alanslists at gmail.com>> wrote:
> 
>     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/
> 
> 
> 
>     _______________________________________________
>     http://www.vtiger.com/
> 
> 
> 
> _______________________________________________
> http://www.vtiger.com/
>