[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

SQL Query takes long time to execute -Need Help in fine tune the query.

Posted on 2008-11-06
5
Medium Priority
?
1,078 Views
Last Modified: 2012-05-05
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
Comment
Question by:tks_g100
3 Comments
 
LVL 3

Accepted Solution

by:
dro_law earned 2000 total points
ID: 22901237
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
 
LVL 5

Expert Comment

by:jfmador
ID: 22901795
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
 
LVL 4

Expert Comment

by:pellep
ID: 22903322
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

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
This shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
As many of you are aware about Scanpst.exe utility which is owned by Microsoft itself to repair inaccessible or damaged PST files, but the question is do you really think Scanpst.exe is capable to repair all sorts of PST related corruption issues?
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses

873 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question