Click here to Skip to main content
15,902,876 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)

I have a query , which is only select statement from 4 tables and views with 3 joins ... and its taking a lot of time .... so i need to optimize it and want execution plan ..... will temp table works ..... here what i am doing

select o.aa ,,c.dd
from aaa a
inner join bbb b (condition)
inner join ccc c (condition)
left join ddd d (condition )
where ( here 3 nested select statements )

can u please help how can i optimize it , or how can i use temp tables to fix it
ddd is not giving fields above , its only giving id for above conditions

Updated 5-Feb-15 10:03am
ZurdoDev 5-Feb-15 15:24pm    
You need to check for indexes. Check the execution plan and see what is happening.
saad88 5-Feb-15 16:35pm    
Hi ryan .... thanks ..... i have never checked execution plan .... can u just tell me how to get it in ssms 2012 ... do i have to do it on this query
ZurdoDev 5-Feb-15 16:44pm    
In SSMS 2008 it is under the Query menu. "Display Estimated Execution Plan." They are not easy to read if you are not strong in SQL server but you'll usually see 1 or 2 boxes that show the most percentage of processing time.

You can also click on Query, Analyze Query in Database Engine Tuning Advisor and then another window will open up that will run the query, analyze it and give suggested indexes and other things to improve performance.
saad88 5-Feb-15 16:51pm    
Thanks Ryan ... i appreciate....thats enough help
PIEBALDconsult 5-Feb-15 16:05pm    
I suspect the "3 nested select statements" in the WHERE clause; try to avoid that.
You may also find that Common Table Expressions will help.

1 solution

As mentioned in comments, you can use several tools to help you find out what the problem is.

For example, use the "Display Estimated Execution Plan" option under the Query menu. These plans are hard to read if you are not strong in SQL but you'll be looking for usually 1 or 2 steps that show a lot of processing percentage.

Then you can also use the Analyze Query in Database Engine Tuning Advisor, also under the Query menu, which will open a new window and will run your query and then will suggest indexes and other items to improve performance.

Also, as PIEBALDconsult mentioned, having 3 SELECT statements in the WHERE clause seems like may be the issue, unless they are very simple select statements. However, they might be able to be converted into joins instead.

Since we can't see any of your tables or data we can't give anything very specific.
Share this answer

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900