how to optimize a SQL query inside server 2012(ssms)

Question

1.00/5 (1 vote)

See more:

SQL-Server-2008R2

Hi

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 ,b.cc,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

Thanks

Posted 5-Feb-15 9:10am

saad88

Updated 5-Feb-15 10:03am

PIEBALDconsult

v2

Add a Solution

Comments

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.

saad88 5-Feb-15 16:34pm

Thanks .... can you give me a general query idea or code for CTE for this senario .... anyhelp would be much appriciated

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

ZurdoDev · Answer 1 · 2015-02-05T10:55:00

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.