By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,266 Members | 1,312 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,266 IT Pros & Developers. It's quick & easy.

Need guidelines - Query Optimization

P: 293

One I get the query plan for a particular query..

Can anybody provide me the guidelines for how to proceed for optimization ?

Aug 15 '09 #1
Share this Question
Share on Google+
1 Reply

Expert 100+
P: 1,134
Probably the best guideline I can give you is to write two different queries in query analyser that achieve the same result. Simple queries at first.

Then you will be able to see the execution plan for both at the same time and query analyser will caompare both and tell you whether one is better than the other. From there you can study the execution plans and you will see why one is better over the other. Over time you will learn what to look for.

an example of 2 queries with identical resuls

Expand|Select|Wrap|Line Numbers
  1. --This one is bad
  2. Select InitiatorID,
  3.           (select UserName from tblUsers where InitiatiorID=ID) as Initiator,
  4.           TopicID,
  5.           PostedComment,
  6.           PosterID,
  7.           (select UserName from tblUsers where PosterID=ID) as Poster,
  8. From tblPosts
  10. --This one should be better (especially if tblUsers has a lot of records in it)
  11. Select InitiatorID,
  12.           b.UserName as Initiator,
  13.           TopicID,
  14.           PostedComment,
  15.           PosterID,
  16.           c.UserName as Poster,
  17. From tblPosts a
  18. join tblUsers b on a.InitiatiorID=b.ID
  19. join tblUsers c on a.PosterID=c.ID
Put two queries something like those into the same query analyser window
and open "show query plan" and you should see a comparison of the two.
Aug 16 '09 #2

Post your reply

Sign in to post your reply or Sign up for a free account.