Hello,
I wanted to share something I found as solution for much better query execution performance. It is for SQL queries in general but I worked with Hibernate and JPA and have such query example.
It was about finding available `Organization`-s which were not already assigned to an `Application` but result must be filtered also by User security from active UserGroup for certain User.
Query where I found bad performance:
Code:
from Organization so left outer join so.catalogs cs where
(cs in (:catalogList) and
so.deleted = false and
so.id not in (select asm.organizationId from ApplicationOrgMapping asm where asm.applicationId = :applicationId) and
(exists(select 1 from UserGroupCountry sc where sc.countryId=so.country.id and sc.userGroupId=:userGroupId and sc.userId=:userId)
or
exists(select 1 from UserGroupOrganization sc where sc.orgId=so.id and sc.userGroupId=:userGroupId and sc.userId=:userId))
as you can see here we select organizations which were not in ApplicationOrgMapping table but country of organization must be in UserGroupCountry or organization must be assigned in UserGroupOrganization meaning user MUST have permission for it.
My idea was to replace this `
OR` with `
AND` in query and I ve done it with this:
Code:
from Organization so left outer join so.catalogs cs where (cs in (:catalogList) and so.deleted = false and
not exists(select 1 from ApplicationOrgMapping asm where asm.organizationId=so.id and asm.applicationId = :applicationId) and
not exists(select 1 from Organization o,Country c where so.id = o.id and c.id = o.country.id and
(not exists(select 1 from UserGroupCountry sc where sc.countryId=c.id and sc.userGroupId=:userGroupId and sc.userId=:userId) and
not exists(select 1 from UserGroupOrganization sx where sx.orgId=o.id and sx.userGroupId=:userGroupId and sx.userId=:userId)))
Result was
15 times faster execution time.
Then I tried on many places, also in native SQL queries, everywhere where I found that we can apply the same idea and always we had much better performance.
In general here is the word about that if I want to see if every element from A = [1,3,5] is containing in B = [2,3,5,6,8] I do not need to check for every single element of A that is also containing in B, instead of that I will check if NOT EXISTS element in A which IS NOT in B.I m not an expert, but hope that someone will find this useful.
Login
FAQ
Search