Post reply

Issues in Optimization

  • December 5, 2007 at 11:17 pm

    #119346

    Hi All,

    I have written a query. Now i need to check whether the query is written in optimized way?

    i'm using "show execution plan" tool to check the performance of query. what are the points should be noted in that plan,so that query is executing in optimized manner?

  • December 5, 2007 at 11:41 pm

    #757525

    Check for the performance hindering operations such as table scans, spools or bookmark/RID lookups.

    --Ramesh

  • December 5, 2007 at 11:47 pm

    #757527

    See also execution plan.

  • December 5, 2007 at 11:59 pm

    #757531

    Thanks!!!

    Could you able to explain the above points (table scan, BookMark etc.,) or is there any URL?

  • December 6, 2007 at 12:01 am

    #757532

    Keep in mind that both the estimated and actual execution plans can lie like a rug... if you have any form of a loop in your code or, sometimes, a UDF, you may never see the problem...

    Same thing goes for any correlated sub-queries... only the plan and rowcount for the first row of execution will be displayed... something might look like you're handling 10,000 rows quite nicely in the estimated execution plan but, for things like triangular joins, the query could be spawing more than 50 million internal rows (that will many times show up in the actual execution plan)... kinda tough on performance when that happens.

    Neither execution plan is the bible... it's just a guide. Real truth comes to bear when you run the code and measure the performance.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply