SQL Query takes long time to execute -Need Help in fine tune the query.
Query takes more time to Execute. Please suggest how to improve the execution timing.
SELECT
z.end_user_emp_id_,
y.name,
y.reconciliationidentity,
y.instanceid,
y.requestid,
y.assetid,
y.classid,
e.remedy_login_id as LoginCTM,
e.person_id as personID_CTM,
e.instanceid as InstanceCTM,
e.internet_e_mail,
e.phone_number_business,
e.full_name,
e.default_notify_mechanism
f.ENTRY_ID
From
(
select v.flag_change_end_user_emp
where not exists
(select 1 asset_id_ from owner.table1@EDB b
where a.asset_id_=b.asset_id_ and b.modified_date > a.modified_date )) v where v.modified_date > '1223855640' and v.flag_change_end_user_emp
) z
LEFT JOIN BASETAB y ON substr(z.asset_id_, 4) = substr(y.assetid, 4)
LEFT JOIN PEOPLETAB e ON z.end_user_emp_id_= e.remedy_login_id
LEFT JOIN ASTPEOPLETAB f ON substr(z.asset_id_, 4) = substr(f.asset_id_, 4)
SELECT
z.end_user_emp_id_,
y.name,
y.reconciliationidentity,
y.instanceid,
y.requestid,
y.assetid,
y.classid,
e.remedy_login_id as LoginCTM,
e.person_id as personID_CTM,
e.instanceid as InstanceCTM,
e.internet_e_mail,
e.phone_number_business,
e.full_name,
e.default_notify_mechanism
f.ENTRY_ID
From
(
select v.flag_change_end_user_emp
where not exists
(select 1 asset_id_ from owner.table1@EDB b
where a.asset_id_=b.asset_id_ and b.modified_date > a.modified_date )) v where v.modified_date > '1223855640' and v.flag_change_end_user_emp
) z
LEFT JOIN BASETAB y ON substr(z.asset_id_, 4) = substr(y.assetid, 4)
LEFT JOIN PEOPLETAB e ON z.end_user_emp_id_= e.remedy_login_id
LEFT JOIN ASTPEOPLETAB f ON substr(z.asset_id_, 4) = substr(f.asset_id_, 4)
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
What the above posters said - execution plan would be a great help in identifying the bottlenecks.
Also, just looking at the query, it looks like the various asset_id fields you use in your join/where criterias are of string (varchar) type, correct? Using string types as criterias are generelly very "expensive" performance-wise. Especially if they are not indexed, if the data set is large and/or if you're using functions like substr in the criteria.
Also, as was already suggested, try to modify your query to remove the nesting. Unless not possible, I generally advice to join rather than nest queries.
Also, just looking at the query, it looks like the various asset_id fields you use in your join/where criterias are of string (varchar) type, correct? Using string types as criterias are generelly very "expensive" performance-wise. Especially if they are not indexed, if the data set is large and/or if you're using functions like substr in the criteria.
Also, as was already suggested, try to modify your query to remove the nesting. Unless not possible, I generally advice to join rather than nest queries.
I never used oracle before, could you tell me what the table name Aradmin.ams_ast_field_trac
Also do not forget that you use three nested query, if it is possible try to change those to use simple join it will improve the performance