Need guidelines - Query Optimization

Collapse
X
 
  • Time
  • Show
Clear All
new posts
  • madankarmukta
    Contributor
    • Apr 2008
    • 308

    Need guidelines - Query Optimization

    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
    Recognized Expert Top Contributor
    • Jan 2008
    • 1134

    #2
    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

    [code=sql]
    --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
    [/code]

    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.

    Comment

    Working...