Grouper Users - Open Discussion List

["Hyzer, Chris" <>]

Basically what this code is doing is getting the memberships and then 
resolving the groups.  Or something like that.  Since we are looking up 
specific groups, we cannot exceed the maximum number of bind variables that 
databases support, which generally is 200.  So we limit the loop there to 
100, so we don’t exceed it.  I guess it possible to get everything in one 
large join, which is fewer queries and more data.   Which is probably faster. 
 We can look in to that or your dev can.  But you cant just change that loop 
size to something more than 200 (including all other bind vars which you 
don’t know what those are).  thanks

-----Original Message-----
From: 

 
<>
 On Behalf Of Reid Watson
Sent: Monday, October 15, 2018 9:51 PM
To: 

Subject: Re: [FORGED] [FORGED] [grouper-users] Grouper-WS-2.3 - 
GetMemberships — “PageSize and PageNumber"

One of our developers found the following 

"verified that indeed there are 5 calls to the database, By debugging the 
code Hib3MembershipDAO: 692"

 private Set<Object[]> findAllByGroupOwnerOptionsHelper(Collection<String> 
totalGroupIds, Collection<String> totalMemberIds,
      Collection<String> totalMembershipIds, MembershipType membershipType,
      Collection<Field> fields, final Collection<Privilege> 
privilegesTheUserHas,
      Set<Source> sources, String scope, Stem stem, Scope stemScope, Boolean 
enabled, Boolean checkSecurity, FieldType fieldType,
      String serviceId, ServiceRole serviceRole, QueryOptions 
queryOptionsForMember, String filterForMember, boolean splitScopeForMember, 
      boolean hasFieldForMember, boolean hasMembershipTypeForMember, 
QueryOptions queryOptionsForGroup, 
      String scopeForGroup, boolean splitScopeForGroup, boolean 
hasFieldForGroup,
      boolean hasMembershipTypeForGroup, Member memberHasMembershipForGroup)

    int groupBatches = GrouperUtil.batchNumberOfBatches(totalGroupIds, 100);

    Potentially the code should be 

    int groupBatches = GrouperUtil.batchNumberOfBatches(totalGroupIds, 
queryOptionsForGroup.getQueryPaging().getPageSize());

They are going to post the details to 
“"

Current Patches for our v2.3.0 install

API: 109
UI: 47
WS: 12
PSP: 21

Cheers

Reid 

> On 16/10/2018, at 12:51 PM, Reid Watson 
> <>
>  wrote:
> 
> Today we looked at the following view “grouper_memberships_lw_v — This view 
> contains all memberships (direct, indirect, groups, people, etc).” and it 
> seems this view returns the data required within milliseconds compared to 
> seconds, correct me if Im wrong but this view is used for large list of 
> data and potentially we should integrate the query we require into 
> Grouper-ws.   
> 
> https://spaces.at.internet2.edu/display/Grouper/Grouper+SQL+interface
> 
> Im just looking for advice on this topic 
> 
> Cheers
> 
> Reid 
> 
>> On 15/10/2018, at 8:43 PM, Reid Watson 
>> <>
>>  wrote:
>> 
>> Hi Everyone, 
>> 
>> The grouper team added some improvements to GetMemberships — “PageSize and 
>> PageNumber"
>> 
>> https://bugs.internet2.edu/jira/browse/GRP-1570
>> 
>> With the amount domains and groups (Folder / Groups) a user has assigned 
>> were noticing a response time between 6 and 15 seconds for one request to 
>> Grouper-ws Service "v2_3_000/memberships"
>> 
>> Example - user has 643 groups (Grouper v2.3) 
>> 
>> 1. POST /grouper-ws/servicesRest/json/v2_3_000/subjects HTTP/1.1.
>> {"WsRestGetSubjectsLiteRequest":{"pageNumber":"1","pageSize":"20","ascending":"T","sortString":"id","subjectIdentifier":"wwan174”}
>> 
>> 1.1. Result 
>> {"WsGetSubjectsResults":{"responseMetadata":{"millis":"258","serverVersion":"2.3.0"},"resultMetadata":{"resultCode":"SUCCESS","resultMessage":"Queried
>>  1 
>> subjects","success":"T"},"wsSubjects":[{"id":"4695399","identifierLookup":"wwan174","name":"Wenlai
>>  Wang","resultCode":"SUCCESS","sourceId":"jdbc","success":"T"}]}}
>> 
>> 
>> 2. POST /grouper-ws/servicesRest/json/v2_3_000/memberships HTTP/1.1.
>> {"WsRestGetMembershipsRequest":{"pageSize":"500","pageNumber":"1","ascending":"T","wsSubjectLookups":[{"subjectId":"4695399"}]}}
>> 
>> 2.1 Snippet of the result  
>> 
>> {"WsGetMembershipsResults":{"responseMetadata":{"millis":"8340","serverVersion":"2.3.0"},"resultMetadata":{"resultCode":"SUCCESS","resultMessage":"Found
>>  2227 results involving 500 groups and 1 
>> subjects","success":"T"},"wsGroups":[{"description":"NAAdminGroups","displayExtension":"NAAdminGroups","displayName":"1989.auckland.ac.nz:NAAdminGroups","extension":"NAAdminGroups","idIndex":"446040","name":"1989.auckland.ac.nz:NAAdminGroups","typeOfGroup":"group","uuid":"e13b649a10514393b3373ea7d1c85592"},{"description":"NAAdminGroups","displayExtension":"NAAdminGroups","displayName":"1996.auckland.ac.nz:NAAdminGroups","extension":"NAAdminGroups","idIndex":"411614","name":"1996.auckland.ac.nz:NAAdminGroups","typeOfGroup":"group","uuid":"0507d40a28274c62a2d071e6672e4870"},{"description":"NAAdminGroups","displayExtension":"NAAdminGroups","displayName":"1997.auckland.ac.nz:NAAdminGroups","extension":"NAAdminGroups","idIndex":"411630","name":"1997.auckland.ac.nz:NAAdminGroups","typeOfGroup":"group","uuid":"7a4085bb84274c17a2a1061e6fe21bb3"},{"description":"NAAdminGroups","displayExtension":"NAAdminGroups","displayName":”1998
>> 
>> 
>> The problem is the following query runs 5 times because we set 
>> "pageSize:500” and the id is batched to 100 items, each query takes 1.7 
>> seconds to respond directly from the Oracle DB.. 
>> 
>>   select distinct membership1_.membership_id as membership_id1_26_0_, 
>> membership1_.immediate_membership_id as immediate_membersh2_26_0_, 
>> membership1_.group_set_id as group_set_id3_26_0_, group2_.id as id1_22_1_, 
>> member0_.id as id1_24_2_, membership1_.hibernate_version_number as 
>> hibernate_version_4_26_0_, membership1_.owner_id as owner_id5_26_0_, 
>> membership1_.owner_attr_def_id as owner_attr_def_id6_26_0_, 
>> membership1_.owner_group_id as owner_group_id7_26_0_, 
>> membership1_.owner_stem_id as owner_stem_id8_26_0_, membership1_.member_id 
>> as member_id9_26_0_, membership1_.field_id as field_id10_26_0_, 
>> membership1_.immediate_field_id as immediate_field_i11_26_0_, 
>> membership1_.via_composite_id as via_composite_id12_26_0_, 
>> membership1_.membership_creator_id as membership_creato13_26_0_, 
>> membership1_.membership_create_time as membership_create14_26_0_, 
>> membership1_.group_set_creator_id as group_set_creator15_26_0_, 
>> membership1_.group_set_create_time as group_set_create_16_26_0_, 
>> membership1_.context_id as context_id17_26_0_, 
>> membership1_.group_set_parent_id as group_set_parent_18_26_0_, 
>> membership1_.via_group_id as via_group_id19_26_0_, membership1_.depth as 
>> depth20_26_0_, membership1_.mship_type as mship_type21_26_0_, 
>> membership1_.immediate_mship_enabled as immediate_mship_e22_26_0_, 
>> membership1_.immediate_mship_enabled_time as immediate_mship_e23_26_0_, 
>> membership1_.immediate_mship_disabled_time as immediate_mship_d24_26_0_, 
>> group2_.hibernate_version_number as hibernate_version_2_22_1_, 
>> group2_.last_membership_change as last_membership_ch3_22_1_, 
>> group2_.last_imm_membership_change as last_imm_membershi4_22_1_, 
>> group2_.parent_stem as parent_stem5_22_1_, group2_.creator_id as 
>> creator_id6_22_1_, group2_.create_time as create_time7_22_1_, 
>> group2_.modifier_id as modifier_id8_22_1_, group2_.modify_time as 
>> modify_time9_22_1_, group2_.name as name10_22_1_, group2_.display_name as 
>> display_name11_22_1_, group2_.extension as extension12_22_1_, 
>> group2_.display_extension as display_extension13_22_1_, 
>> group2_.description as description14_22_1_, group2_.context_id as 
>> context_id15_22_1_, group2_.alternate_name as alternate_name16_22_1_, 
>> group2_.type_of_group as type_of_group17_22_1_, group2_.id_index as 
>> id_index18_22_1_, member0_.hibernate_version_number as 
>> hibernate_version_2_24_2_, member0_.subject_id as subject_id3_24_2_, 
>> member0_.subject_source as subject_source4_24_2_, member0_.subject_type as 
>> subject_type5_24_2_, member0_.context_id as context_id6_24_2_, 
>> member0_.subject_identifier0 as subject_identifier7_24_2_, 
>> member0_.sort_string0 as sort_string8_24_2_, member0_.sort_string1 as 
>> sort_string9_24_2_, member0_.sort_string2 as sort_string10_24_2_, 
>> member0_.sort_string3 as sort_string11_24_2_, member0_.sort_string4 as 
>> sort_string12_24_2_, member0_.search_string0 as search_string13_24_2_, 
>> member0_.search_string1 as search_string14_24_2_, member0_.search_string2 
>> as search_string15_24_2_, member0_.search_string3 as 
>> search_string16_24_2_, member0_.search_string4 as search_string17_24_2_, 
>> member0_.name as name18_24_2_, member0_.description as description19_24_2_ 
>> from grouper_members member0_ cross join grouper_memberships_all_v 
>> membership1_ cross join grouper_groups group2_ cross join grouper_fields 
>> field3_ where membership1_.owner_group_id=group2_.id and 
>> membership1_.member_id=member0_.id and 
>> membership1_.immediate_mship_enabled='T' and 
>> membership1_.field_id=field3_.id and field3_.type='list' and 
>> (membership1_.owner_group_id in ('5f6fbfe087e74471a5badfd3b3e4c24b' , 
>> 'e89ac5a4027d415dbb6e82b0a83ee49b' , '615f41a690dc4503a3b59190c52c2149' , 
>> 'f704e9e33f884f23bf57f75721e9daee' , '33139201d35f4372a99efc8b2c76aadf' , 
>> '56c3b795eed64810a7f21d1fd1c79dc0' , '714d1347db904a298074065e24099a5f' , 
>> 'b0ba21fb6d194fbdb9bbc0fb7b9f227a' , '66ba7e778615484ba5b78f44859dd076' , 
>> 'a776e803f9b1475a92984b977043c96e' , '64a0faed6ac440979d14877a93d2dd5c' , 
>> 'b5a7db29a7f14b4d92ecc7c7b4591945' , '304fb43c85f847e1b7729d241529f7b1' , 
>> '01984212137a4ba5bd9ea299dbf78259' , 'c2d567315f384be99e3c88149df84c74' , 
>> '526e8fa76146428589d15140a25d16b1' , '2041c0d557624814b267dd2c97cbb66a' , 
>> '8472df45758343eb8ece1b0a4b54bbd8' , '91677c7eeba34571af49a80b13dba750' , 
>> '5bc9679b210542c8875215df99d9b80d' , '1432ace3a5d243e69b4b0d7a0309cd15' , 
>> '12c9ec4221b349a3aef419c32ea2bce1' , '4f6c6a93993242108e412357394f764d' , 
>> '794b01653e404eaf89be1dcd520d1eba' , '95bf323f921b491ab818daf449f1a033' , 
>> 'f909aaf0917f48bb88689d925041a0aa' , '64e4cbecb9d44cf3b6825acb705ab09c' , 
>> '00b062c6b5334dac9f1456c9497acd9b' , 'e1dfa96735d24cc0adfd30f8dc678fa5' , 
>> '4da63441c75c4597a2a41d62b7960742' , '04f69e2014e544b0972331f431c45096' , 
>> 'c5f96126c808489c86dbc0f7a3d7ff1b' , '8ace229d662f4bc6a33723a7d4d0dc74' , 
>> 'a70962032ab54635ac20f6bc4f9d70b0' , '66bca41dcbd347418224330e759eddca' , 
>> '6c6275c458dd4be8bb6a74f828776295' , 'b782d24ccf09413a8e84d6fcc8eb92a3' , 
>> 'e7a9e7d35f404635a611678c18f13304' , 'a1d5c4027844444392afb9a468304b4e' , 
>> '9df335f404584e9e92a4de660f459f25' , '33953b2e677e463b968927ebbcacf513' , 
>> '689b7a4ea39142339a70cc7bd2d67914' , '134c62b1ba5043798efe16106d129938' , 
>> 'b01a2ed3cf084614990755d62c8357b2' , 'dcc3940ba74e42e59786d8b3e50e20ec' , 
>> '98ccb0e7bc4e45358ed6f5f306c9a751' , '7447ff9e2b9049fe84fab1b0a9d0c6a2' , 
>> '69ed88423a1e4838ae2228bfe183a354' , '8c52eba0c710498da3fecd33fae276a4' , 
>> '8c9fd31bcbae4ea3b3dfaaafa4c626d6' , 'e8524ff5975e4b939d2810e73986635c' , 
>> '5a12e00f022742b68f85927efb5b3f91' , '10c16a018dc04abca342cfbb6aa8a088' , 
>> 'cde48ccad1b74cb591ab4bf484aeac12' , 'bbf0e98bdc42464a94cd040c6233669f' , 
>> '3adf91449be74332ae2cd89b42472d88' , '4036c58865df4cbea691a346b2463177' , 
>> '845da7370f4e4cf3bc4d31b490e67170' , 'b13628533a7744a08db68df5f24b16b0' , 
>> '4a00d5daf65640deb7d145b5755e41c7' , '723c8cf0b43745409422fd4de88b9fda' , 
>> '70308d952c9f44e0a5664a8b48d8afe3' , 'e70ffdf56565495f93de7148c2b11776' , 
>> 'bb12a37e9ae64e8a81242095859b6da5' , 'aab8be74d392415db5ae81e3cb99c4ee' , 
>> 'a4f112f41e964294a7214765b6f804b1' , '93d1ae5a5e9149b78fc6fff10d144ffd' , 
>> 'aa7a3edf2d224198b2863c3b09f5de0c' , '1d461d0bc54f49b5911ad13f3e1c8b79' , 
>> 'daf877a600b74b20bedc0fbc5f29566c' , '83052510c6eb46db83711c21d2da8671' , 
>> '226820f3837c4dc2bfa94aa5cef4afb5' , '7dedd10b25004d67857233dc3ae1d745' , 
>> '137e20b8fc43466ca31878967460f956' , 'fe9e9cb756e24e849dd4ea10dfe7b832' , 
>> '0306497361d34e389b86b7477b8affe3' , '116777951bdc44ee82fc22aa86b175e0' , 
>> '734934019e8543df9c2f2242aa3b5700' , 'ace9dbf53e4e4ab9937088fb89ffee2c' , 
>> '8779d027eb83427183cb1bf09d8c7eaa' , '881c77e476064f5f8d27dc721a147871' , 
>> 'c7db595b90524d9b995567e7439dca06' , '33e59abeeb7a4839a8e3e21061c12f0a' , 
>> '6e864564e99b4c498264ad22fbd7d29f' , '17b7aa53b4684bcdbcd4d6378e77ef25' , 
>> 'cde139198a4c4a87b651264b3c63f4a7' , '23352904f9da4b319025445dcc526d66' , 
>> '30feb447977647d999304bf9105e489f' , 'c6f98ce79f8c479f9588786fc737239b' , 
>> '8b0a1b77fb164f34b446704307e0f85d' , 'a26c09e0ab1b4cc3b5683f19cd27a0c0' , 
>> 'c6bd8669e11c4e9ba58bad23491f185a' , '55258aa1c8e64312ba98ee06b9f547dd' , 
>> 'b917c19aaad143519531cf6365f0d935' , '15b37ee403404cdf8e52eac4f26b8792' , 
>> 'd9daff4da8f54288ac159c6c7067e8ca' , '607a0353a37a4062a17a373c1c30a55f' , 
>> 'e09dac2f79e9407c80d9f1a8c7990007' , '9b5557848c0345fcb77aed4f98126efe' , 
>> 'd462d13ba3134841aec3c057357c21d0')) and (membership1_.member_id in 
>> ('ae0b887741fc492aa86fb843af0f02cc'))
>> 
>> Any advice to improve the response time from the query ? 
>> 
>> Cheers
>> 
>> Reid Watson
>> 
>> 
>