Slow query on audit log in the UI

The following was sent to the grouper-core list on Jan 13, 2019 - per Mr. Hyzer creating ticket as well.

Hi all,

Something that has been bugging me is the slowness of the view audit on a group in the UI. I grabbed the SQL (first select below) and this was regularly taking 40 seconds on my laptop. I re-wrote it and this is the second query and it takes 6 seconds. I am going against MariaDB v10 latest but this may be a more general issue. Of course, repeated executions of each take a few ms due to the DB memory caching. When the cache is not used, these timings are consistent for the query executed in my environment. Clearly, the logic of anding/oring impacts the performance of the query. I recall experiencing slow audit logs when I ran grouper at Penn State against an Oracle DB.

I hope this helps. I am using tier/grouper:2.4.0-a14-u8-w1-p0-20190101-canary package.

/mrg

— the following was grabbed from the running SQL on the DB side when choosing “view audit log” in the UI for a group. This takes >40 seconds.
select this_.id as id1_10_0_, this_.hibernate_version_number as hibernat2_10_0_, this_.act_as_member_id as act_as_m3_10_0_, this_.audit_type_id as audit_ty4_10_0_, this_.context_id as context_5_10_0_, this_.created_on as created_6_10_0_, this_.description as descript7_10_0_, this_.env_name as env_name8_10_0_, this_.grouper_engine as grouper_9_10_0_, this_.grouper_version as grouper10_10_0_, this_.int01 as int11_10_0_, this_.int02 as int12_10_0_, this_.int03 as int13_10_0_, this_.int04 as int14_10_0_, this_.int05 as int15_10_0_, this_.last_updated as last_up16_10_0_, this_.logged_in_member_id as logged_17_10_0_, this_.server_host as server_18_10_0_, this_.string01 as string19_10_0_, this_.string02 as string20_10_0_, this_.string03 as string21_10_0_, this_.string04 as string22_10_0_, this_.string05 as string23_10_0_, this_.string06 as string24_10_0_, this_.string07 as string25_10_0_, this_.string08 as string26_10_0_, this_.duration_microseconds as duratio27_10_0_, this_.query_count as query_c28_10_0_, this_.user_ip_address as user_ip29_10_0_, this_.server_user_name as server_30_10_0_ from grouper_audit_entry this_ where ((this_.audit_type_id='a51ccce2836c4044ab1289845eab762c' and this_.string06='f6b58a62c1974bdd928e107943d92af9') or (this_.audit_type_id='27f52f1e6d3a4e8aad03c86741e0e63b' and this_.string02='f6b58a62c1974bdd928e107943d92af9') or (this_.audit_type_id='6ef4176c17964fe0b44017fe2ca6a18c' and this_.string01='f6b58a62c1974bdd928e107943d92af9') or (this_.audit_type_id='a3de638772ce4d159d5fc79b97fb309a' and this_.string02='f6b58a62c1974bdd928e107943d92af9') or (this_.audit_type_id='a0247d92f88445a5a7e700e1885a24e5' and this_.string02='f6b58a62c1974bdd928e107943d92af9') or (this_.audit_type_id='6b625c6fed3141b383586811953afc77' and this_.string04='f6b58a62c1974bdd928e107943d92af9') or (this_.audit_type_id='8cc36a96f311404ebb1759bb2f2151ce' and this_.string01='f6b58a62c1974bdd928e107943d92af9') or (this_.audit_type_id='ef8a81fe87d64897a7758e25c77e2ccd' and this_.string02='f6b58a62c1974bdd928e107943d92af9') or (this_.audit_type_id='363e3acc73e24c52bf52c96d0d684863' and this_.string06='f6b58a62c1974bdd928e107943d92af9') or (this_.audit_type_id='6967fb7698934d20b513f7a9dbc32980' and this_.string01='f6b58a62c1974bdd928e107943d92af9') or (this_.audit_type_id='6b625c6fed3141b383586811953afc77' and this_.string02='f6b58a62c1974bdd928e107943d92af9') or (this_.audit_type_id='721d867432204d1bb7bd2ee6cace3db9' and this_.string02='f6b58a62c1974bdd928e107943d92af9') or (this_.audit_type_id='a51ccce2836c4044ab1289845eab762c' and this_.string04='f6b58a62c1974bdd928e107943d92af9') or (this_.audit_type_id='ef8a81fe87d64897a7758e25c77e2ccd' and this_.string04='f6b58a62c1974bdd928e107943d92af9') or (this_.audit_type_id='eacdc10729b04abe96e725772635350f' and this_.string01='f6b58a62c1974bdd928e107943d92af9') or (this_.audit_type_id='363e3acc73e24c52bf52c96d0d684863' and this_.string04='f6b58a62c1974bdd928e107943d92af9') or (this_.audit_type_id='744fa6e806594a789392d5322e0e495d' and this_.string01='f6b58a62c1974bdd928e107943d92af9') or (this_.audit_type_id='c1aa04f5db56489f95d176705e7d43a6' and this_.string01='f6b58a62c1974bdd928e107943d92af9') or (this_.audit_type_id='b300ba35576e4b2c85a84252120823ee' and this_.string01='f6b58a62c1974bdd928e107943d92af9') or (this_.audit_type_id='ffd9ea9f3ab943e98801d903e9ed0de1' and this_.string01='f6b58a62c1974bdd928e107943d92af9') or (this_.audit_type_id='710eb50c84d846259fe9f5cee7e29b52' and this_.string01='f6b58a62c1974bdd928e107943d92af9') or (this_.audit_type_id='91b1486cde8644a19a71e0ea955161f9' and this_.string01='f6b58a62c1974bdd928e107943d92af9') or (this_.audit_type_id='51f1970dae89434babfbf05728f8691a' and this_.string02='f6b58a62c1974bdd928e107943d92af9') or (this_.audit_type_id='9790e6cfe0fe4edab33693520325ee6c' and this_.string02='f6b58a62c1974bdd928e107943d92af9') or (this_.audit_type_id='27f52f1e6d3a4e8aad03c86741e0e63b' and this_.string06='f6b58a62c1974bdd928e107943d92af9') or (this_.audit_type_id='6669aa4b8b9a487db8c527af7517c94e' and this_.string04='f6b58a62c1974bdd928e107943d92af9') or (this_.audit_type_id='a0247d92f88445a5a7e700e1885a24e5' and this_.string06='f6b58a62c1974bdd928e107943d92af9') or (this_.audit_type_id='a8d3d1042c33433e9ad93f3b67d5771e' and this_.string04='f6b58a62c1974bdd928e107943d92af9') or (this_.audit_type_id='ce6753a51a2d4067b7a75c061bd8b14d' and this_.string04='f6b58a62c1974bdd928e107943d92af9') or (this_.audit_type_id='6ffdbcdd71f04357987b2ef5e9a68550' and this_.string01='f6b58a62c1974bdd928e107943d92af9') or (this_.audit_type_id='a51ccce2836c4044ab1289845eab762c' and this_.string02='f6b58a62c1974bdd928e107943d92af9') or (this_.audit_type_id='6669aa4b8b9a487db8c527af7517c94e' and this_.string06='f6b58a62c1974bdd928e107943d92af9') or (this_.audit_type_id='a8d3d1042c33433e9ad93f3b67d5771e' and this_.string02='f6b58a62c1974bdd928e107943d92af9') or (this_.audit_type_id='ce6753a51a2d4067b7a75c061bd8b14d' and this_.string06='f6b58a62c1974bdd928e107943d92af9') or (this_.audit_type_id='4dacf62eeb264100a2f53932d964c48f' and this_.string01='f6b58a62c1974bdd928e107943d92af9') or (this_.audit_type_id='d553ff7d2c7e4a3bae7d5307f350ce78' and this_.string02='f6b58a62c1974bdd928e107943d92af9') or (this_.audit_type_id='d4a6a5eab43b404f8c25e9e8dc032e91' and this_.string01='f6b58a62c1974bdd928e107943d92af9') or (this_.audit_type_id='5f169a12e4da40a8a0b6bfc26c48adf3' and this_.string01='f6b58a62c1974bdd928e107943d92af9') or (this_.audit_type_id='a0247d92f88445a5a7e700e1885a24e5' and this_.string04='f6b58a62c1974bdd928e107943d92af9') or (this_.audit_type_id='6ef4176c17964fe0b44017fe2ca6a18c' and this_.string03='f6b58a62c1974bdd928e107943d92af9') or (this_.audit_type_id='27f52f1e6d3a4e8aad03c86741e0e63b' and this_.string04='f6b58a62c1974bdd928e107943d92af9'))
order by this_.last_updated desc limit 150;

— this altered version takes 6 seconds.
select this_.id as id1_10_0_, this_.hibernate_version_number as hibernat2_10_0_,
this_.act_as_member_id as act_as_m3_10_0_, this_.audit_type_id as audit_ty4_10_0_,
this_.context_id as context_5_10_0_, this_.created_on as created_6_10_0_, this_.description as descript7_10_0_,
this_.env_name as env_name8_10_0_, this_.grouper_engine as grouper_9_10_0_, this_.grouper_version as grouper10_10_0_,
this_.int01 as int11_10_0_, this_.int02 as int12_10_0_, this_.int03 as int13_10_0_, this_.int04 as int14_10_0_,
this_.int05 as int15_10_0_, this_.last_updated as last_up16_10_0_, this_.logged_in_member_id as logged_17_10_0_,
this_.server_host as server_18_10_0_, this_.string01 as string19_10_0_, this_.string02 as string20_10_0_,
this_.string03 as string21_10_0_, this_.string04 as string22_10_0_, this_.string05 as string23_10_0_,
this_.string06 as string24_10_0_, this_.string07 as string25_10_0_, this_.string08 as string26_10_0_,
this_.duration_microseconds as duratio27_10_0_, this_.query_count as query_c28_10_0_,
this_.user_ip_address as user_ip29_10_0_, this_.server_user_name as server_30_10_0_
from grouper_audit_entry this_
where (this_.string01='f6b58a62c1974bdd928e107943d92af9' and
(this_.audit_type_id='b300ba35576e4b2c85a84252120823ee'
or this_.audit_type_id='c1aa04f5db56489f95d176705e7d43a6'
or this_.audit_type_id='eacdc10729b04abe96e725772635350f'
or this_.audit_type_id='d4a6a5eab43b404f8c25e9e8dc032e91'
or this_.audit_type_id='ffd9ea9f3ab943e98801d903e9ed0de1'
or this_.audit_type_id='6ffdbcdd71f04357987b2ef5e9a68550'
or this_.audit_type_id='4dacf62eeb264100a2f53932d964c48f'
or this_.audit_type_id='91b1486cde8644a19a71e0ea955161f9'
or this_.audit_type_id='744fa6e806594a789392d5322e0e495d'
or this_.audit_type_id='8cc36a96f311404ebb1759bb2f2151ce'
or this_.audit_type_id='710eb50c84d846259fe9f5cee7e29b52'
or this_.audit_type_id='6967fb7698934d20b513f7a9dbc32980'
or this_.audit_type_id='5f169a12e4da40a8a0b6bfc26c48adf3'
or this_.audit_type_id='6ef4176c17964fe0b44017fe2ca6a18c'))
or (this_.string02='f6b58a62c1974bdd928e107943d92af9' and
(this_.audit_type_id='9790e6cfe0fe4edab33693520325ee6c'
or this_.audit_type_id='a0247d92f88445a5a7e700e1885a24e5'
or this_.audit_type_id='721d867432204d1bb7bd2ee6cace3db9'
or this_.audit_type_id='a8d3d1042c33433e9ad93f3b67d5771e'
or this_.audit_type_id='51f1970dae89434babfbf05728f8691a'
or this_.audit_type_id='a51ccce2836c4044ab1289845eab762c'
or this_.audit_type_id='27f52f1e6d3a4e8aad03c86741e0e63b'
or this_.audit_type_id='6b625c6fed3141b383586811953afc77'
or this_.audit_type_id='d553ff7d2c7e4a3bae7d5307f350ce78'
or this_.audit_type_id='a3de638772ce4d159d5fc79b97fb309a'
or this_.audit_type_id='ef8a81fe87d64897a7758e25c77e2ccd'))
or (this_.string03='f6b58a62c1974bdd928e107943d92af9' and this_.audit_type_id='6ef4176c17964fe0b44017fe2ca6a18c')
or (this_.string04='f6b58a62c1974bdd928e107943d92af9' and
(this_.audit_type_id='363e3acc73e24c52bf52c96d0d684863'
or this_.audit_type_id='a8d3d1042c33433e9ad93f3b67d5771e'
or this_.audit_type_id='27f52f1e6d3a4e8aad03c86741e0e63b'
or this_.audit_type_id='6669aa4b8b9a487db8c527af7517c94e'
or this_.audit_type_id='a51ccce2836c4044ab1289845eab762c'
or this_.audit_type_id='ef8a81fe87d64897a7758e25c77e2ccd'
or this_.audit_type_id='ce6753a51a2d4067b7a75c061bd8b14d'
or this_.audit_type_id='6b625c6fed3141b383586811953afc77'
or this_.audit_type_id='a0247d92f88445a5a7e700e1885a24e5'))
or (this_.string06='f6b58a62c1974bdd928e107943d92af9' and
(this_.audit_type_id='27f52f1e6d3a4e8aad03c86741e0e63b'
or this_.audit_type_id='6669aa4b8b9a487db8c527af7517c94e'
or this_.audit_type_id='363e3acc73e24c52bf52c96d0d684863'
or this_.audit_type_id='a0247d92f88445a5a7e700e1885a24e5'
or this_.audit_type_id='a51ccce2836c4044ab1289845eab762c'
or this_.audit_type_id='ce6753a51a2d4067b7a75c061bd8b14d'))
order by this_.last_updated desc limit 150
;