Hello Team,
There is a report which is responsible for fetching the Applications, its roles, Entitlements inside roles, Child Entitlements inside roles and then its Functions.
Below is the query which was developed:
SELECT DISTINCT rep.ENDPOINTNAME AS 'APPLICATION NAME'
,ifnull(r.DISPLAYNAME, r.ROLE_NAME) AS 'REQUESTABLE ROLE'
,ifnull(ev.DISPLAYNAME, ev.ENTITLEMENT_VALUE) AS 'ENTITLEMENT'
,ifnull(ev2.DISPLAYNAME, ev2.ENTITLEMENT_VALUE) AS 'Privilege'
,ifnull(f.DESCRIPTION, f.FUNCTION_NAME) AS 'FunctionName'
FROM roles r
INNER JOIN role_entitlements rev ON rev.ROLEKEY = r.ROLEKEY
INNER JOIN endpoints rep ON rep.ENDPOINTKEY = r.ENDPOINTKEY
LEFT JOIN entitlement_values ev ON ev.ENTITLEMENT_VALUEKEY = rev.ENTITLEMENT_VALUEKEY
LEFT JOIN entitlements2 e2 ON ev.ENTITLEMENT_VALUEKEY = e2.ENTITLEMENT_VALUE1KEY
LEFT JOIN entitlement_values ev2 ON e2.ENTITLEMENT_VALUE2KEY = ev2.entitlement_valuekey
LEFT JOIN function_entitlements fe ON (
ev.ENTITLEMENT_VALUEKEY = fe.ENTITLEMENT_VALUEKEY
OR e2.ENTITLEMENT_VALUE2KEY = fe.ENTITLEMENT_VALUEKEY
)
LEFT JOIN functions f ON (
fe.functionkey = f.functionkey
AND f.STATUS = 1
)
WHERE rep.CUSTOMPROPERTY6 = 'sodareport'
AND r.STATUS = 1
AND r.REQUESTABLE = TRUE;
But this query uses the tables which contains data in crores (eg. entitlements2) because of which data is not getting saved and nor it is giving the value as it is taking more than 20 min to run the data. So we need to optimize the query so that it gets saved, and we are able to fetch the data.
We have then modified the query by using subquery in the joins but it is still not saving because of OR in the left join. If we try to remove OR and create the query by using UNION then it doesn't give the expected output.
Modified Query:
SELECT DISTINCT rep.ENDPOINTNAME AS 'APPLICATION NAME'
,ifnull(r.DISPLAYNAME, r.ROLE_NAME) AS 'REQUESTABLE ROLE'
,ifnull(ev.DISPLAYNAME, ev.ENTITLEMENT_VALUE) AS 'ENTITLEMENT'
,ifnull(ev2.DISPLAYNAME, ev2.ENTITLEMENT_VALUE) AS 'Privilege'
,ifnull(f.DESCRIPTION, f.FUNCTION_NAME) AS 'FunctionName'
FROM roles r
INNER JOIN role_entitlements rev ON rev.ROLEKEY = r.ROLEKEY
INNER JOIN (select ENDPOINTKEY,CUSTOMPROPERTY6,ENDPOINTNAME from endpoints) as rep ON rep.ENDPOINTKEY = r.ENDPOINTKEY
LEFT JOIN (select ENTITLEMENT_VALUE,DISPLAYNAME,ENTITLEMENT_VALUEKEY from entitlement_values) as ev ON ev.ENTITLEMENT_VALUEKEY = rev.ENTITLEMENT_VALUEKEY
LEFT JOIN (select ENTITLEMENT_VALUE1KEY,ENTITLEMENT_VALUE2KEY from entitlements2) as e2 ON ev.ENTITLEMENT_VALUEKEY = e2.ENTITLEMENT_VALUE1KEY
LEFT JOIN (select ENTITLEMENT_VALUE,DISPLAYNAME,ENTITLEMENT_VALUEKEY from entitlement_values) as ev2 ON e2.ENTITLEMENT_VALUE2KEY = ev2.entitlement_valuekey
LEFT JOIN (select ENTITLEMENT_VALUEKEY,functionkey from function_entitlements) as fe ON (
ev.ENTITLEMENT_VALUEKEY = fe.ENTITLEMENT_VALUEKEY
OR e2.ENTITLEMENT_VALUE2KEY = fe.ENTITLEMENT_VALUEKEY
)
LEFT JOIN (select DESCRIPTION,FUNCTION_NAME,functionkey,status from functions) as f ON
(fe.functionkey = f.functionkey and f.status=1)
WHERE rep.CUSTOMPROPERTY6 = 'sodareport'
AND r.STATUS = 1
AND r.REQUESTABLE = TRUE;
Can you guys help me with the way that I can optimize the above query or remove OR from left join?
