Need guidelines - Query Optimization

[madankarmukta]

Hi,

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

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

Thanks!

[Delerna]

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

--This one is bad
Select InitiatorID,
          (select UserName from tblUsers where InitiatiorID=ID) as Initiator,
          TopicID,
          PostedComment,
          PosterID,
          (select UserName from tblUsers where PosterID=ID) as Poster,
From tblPosts
 
--This one should be better (especially if tblUsers has a lot of records in it)
Select InitiatorID,
          b.UserName as Initiator,
          TopicID,
          PostedComment,
          PosterID,
          c.UserName as Poster,
From tblPosts a
join tblUsers b on a.InitiatiorID=b.ID
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.