How to improve the performance of SQL query in Oracle DB

Question

one of the tab is broken in the Application (when hit that tab it keeps on rotating and calling the particular service and getting 404 error after sometime). we created indexes for few columns in Application DB to see if that improves performance. Creating indexes didn't make much difference.

so We planned to rewrite the sql query to improve the performance and fix it.

select distinct 
    editedUser.*
from
    users editedUser,
    relationship_ref editedUserRelationship,
    users approvingUser,
    user_role approvingUserRoles,
    role_permission approvingUserRolePermissions,
    account approvingUserAccount
where
    approvingUser.user_id = 175263
    and approvingUser.user_id = approvingUserRoles.user_id
    and approvingUserRoles.role_id = approvingUserRolePermissions.role_id
    and approvingUserRoles.user_role_status_id = 2
    and editedUserRelationship.relationship_id = 
        editedUser.submitted_relationship_id
    and (approvingUser.account_id = approvingUserAccount.account_id
         or approvingUser.account_id is null)
    and editedUser.review_status = 'R'
    and approvingUserRolePermissions.permission_id = 
        editedUserRelationship.view_pending_permission_id;

It is taking nearly 6 mins.So can one please suggest how to use the proper joins in this query. It has 36 columns and 30,000 records.

if i use left or right join instead of cross join.It may improve performance — Geeky
– Geeky, Commented Dec 24, 2018 at 16:29

William Robertson · Accepted Answer · 2018-12-24 16:46:40Z

1

Rearranged in a style that is easier to read (to me at least), I get this:

select distinct ed.*
from   users ap
       join user_role ro
            on  ro.user_id = ap.user_id
       join account ac
            on  ac.account_id = ap.account_id or ap.account_id is null
       join role_permission rp
            on  rp.role_id = ro.role_id
       join relationship_ref re
            on  re.view_pending_permission_id = rp.permission_id
       join users ed
            on  ed.submitted_relationship_id = re.relationship_id
where  au.user_id = 175263
and    ro.user_role_status_id = 2
and    ed.review_status = 'R'

How many approving users have null account_id? For any of those, you retrieve all accounts in the system. Is that actually the business requirement? I'm not sure that it makes any sense. The query does not make any further use of that table, so perhaps you can remove the account join entirely.

edited Dec 24, 2018 at 16:46

answered Dec 24, 2018 at 16:39

William Robertson

16.2k4 gold badges40 silver badges49 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to improve the performance of SQL query in Oracle DB

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related