Grouper Users - Open Discussion List

[Shilen Patel <>]

If you run that query below manually, I would expect it to take milliseconds and not seconds.  Have you tried analyzing tables? 

 

https://spaces.at.internet2.edu/pages/viewpage.action?pageId=14517958#APIBuilding&Configuration-AnalyzingTablestoImproveQueryPerformance

 

- Shilen

 

From: <> on behalf of Reid Watson <>
Date: Monday, October 15, 2018 at 3:43 AM
To: "" <>
Subject: [grouper-users] Grouper-WS-2.3 - GetMemberships — “PageSize and PageNumber"

 

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