Grouper Developers Forum

["Redman, Chad" <>]

Carey, is this with Grouper 2.3.0 which uses hsqldb 2.3.3? I did see some 
kind of issue (https://sourceforge.net/p/hsqldb/bugs/1449/  - #1449 DROP 
CONSTRAINT causes SQLIntegrityConstraintViolationException) but that wasn't a 
performance issue. When we ran the full suite of tests in Grouper 2.3, 
performance problems did eventually appear midway during the 4-hour run. 
Queries gradually took longer and longer in general, until it became 
unusable. If you stop and restart the database, does it perform better?

Are you doing the action as a wheel user or non-wheel? The query looks like 
it could be checking permissions but I can't tell with the placeholders.

Hibernate creates the query as a cross join but it should get rewritten by 
the rdbms as long as there is a join clause between the two tables.

If you query EXPLAIN PLAN FOR {the original query} it should tell you what 
it's trying to do. Maybe it chose the wrong table as the driving table.

-Chad


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

 
[mailto:]
 On Behalf Of Black, Carey M.
Sent: Tuesday, October 16, 2018 2:01 PM
To: Hyzer, Chris 
<>
Cc: 

Subject: [grouper-dev] Possible performance issue with HSQLDB...

Chris, (et. al.)

I have a test instance that is currently still using the HSQLDB. ( I know... 
I am going to reset the env and move to a "real DB" ... soon...)

But I found that when a group is being added as a member of another group 
this SQL takes a very long time to execute:

select member0_.id as id1_24_, 
        member0_.hibernate_version_number as hibernat2_24_, 
        member0_.subject_id as subject_3_24_, 
        member0_.subject_source as subject_4_24_, 
        member0_.subject_type as subject_5_24_, 
        member0_.context_id as context_6_24_, 
        member0_.subject_identifier0 as subject_7_24_, 
        member0_.sort_string0 as sort_str8_24_, 
        member0_.sort_string1 as sort_str9_24_, 
        member0_.sort_string2 as sort_st10_24_, 
        member0_.sort_string3 as sort_st11_24_, 
        member0_.sort_string4 as sort_st12_24_, 
        member0_.search_string0 as search_13_24_, 
        member0_.search_string1 as search_14_24_, 
        member0_.search_string2 as search_15_24_, 
        member0_.search_string3 as search_16_24_, 
        member0_.search_string4 as search_17_24_, 
        member0_.name as name18_24_, 
        member0_.description as descrip19_24_ 
from grouper_members member0_ cross join grouper_memberships_all_v 
membership1_ 
where membership1_.owner_group_id=? and membership1_.field_id=? 
        and membership1_.member_id=member0_.id and 
membership1_.immediate_mship_enabled='T'

Maybe this is the "performance issue" that you have seen from time to time in 
the test scripts?
        ( "cross join" sounds.... like a bad idea on any system of any size.)

Maybe this is just a hibernate on HSQLDB issue? Maybe not?

Hope that helps.

-- 
Carey Matthew