• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1082
  • Last Modified:

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_id_, v.asset_id_, v.modified_date, v.end_user_emp_id_ from (select flag_change_end_user_emp_id_, asset_id_, modified_date, end_user_emp_id_ from Aradmin.ams_ast_field_tracking@amsbase.itg.ti.com a
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_id_= 0 order by v.asset_id_


) 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)
0
tks_g100
Asked:
tks_g100
1 Solution
 
dro_lawCommented:
1. You may want to consider making your derived table (z) a view
2. Do you have indexes on all the join fields (i.e. asset_id, emp_id, etc.)?
3. Have you tired looking at the execution plan and seeing what is taking the longest time?
0
 
jfmadorCommented:
Hello also could you provide us the table row count for each table it will help to point where it is slow

I never used oracle before, could you tell me what the table name Aradmin.ams_ast_field_tracking@amsbase.itg.ti.com  mean, is it a kind of linked server ? if yes do you have good network performance with it ?

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
0
 
pellepCommented:
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.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Certified Penetration Testing

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now