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

Posted on 2008-11-06
Last Modified: 2012-05-05
Query takes more time to Execute. Please suggest how to improve the execution timing.

e.remedy_login_id as LoginCTM,
e.person_id as personID_CTM,
e.instanceid as InstanceCTM,

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 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)
Question by:tks_g100
    LVL 3

    Accepted Solution

    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?
    LVL 5

    Expert Comment

    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  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
    LVL 4

    Expert Comment

    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.

    Featured Post

    Why You Should Analyze Threat Actor TTPs

    After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

    Join & Write a Comment

    Introduction Hopefully the following mnemonic and, ultimately, the acronym it represents is common place to all those reading: Please Excuse My Dear Aunt Sally (PEMDAS). Briefly, though, PEMDAS is used to signify the order of operations (http://en.…
    'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
    Migrating to Microsoft Office 365 is becoming increasingly popular for organizations both large and small. If you have made the leap to Microsoft’s cloud platform, you know that you will need to create a corporate email signature for your Office 365…
    how to add IIS SMTP to handle application/Scanner relays into office 365.

    732 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

    Need Help in Real-Time?

    Connect with top rated Experts

    17 Experts available now in Live!

    Get 1:1 Help Now