CODEWITHSUNDEEP
sqldatabaseoracle-databaseperformancequery-optimization
Geeky
Geeky

Reputation: 11

How to improve the performance of SQL query in Oracle DB

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.

Upvotes: 1

Views: 162

Answers (1)

William Robertson
William Robertson

Reputation: 16001

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.

Upvotes: 1

Related Questions