Grouper Users - Open Discussion List

[Reid Watson <>]

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
> 
>