Post reply

Optimizing the Query..

  • October 6, 2010 at 9:43 am

    #1232730

    vijay1327-891581 (10/6/2010)

    1: Remove count(*) and replace it with count(any one INTEGER type column).

    Great if you're trying to slow the query down or change it's meaning. Not very successful at speeding one up though...

    http://sqlinthewild.co.za/index.php/2009/04/14/on-counts/

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • October 6, 2010 at 5:16 pm

    #1233202

    Great Suggestions!

    Thanks,

    Sahasam

  • October 10, 2010 at 10:23 pm

    #1234769

    vijay1327-891581 (10/6/2010)

    There are 2 suggestion for this:

    1: Remove count(*) and replace it with count(any one INTEGER type column). Here all you are doing is checking if there is any row > 0

    2: In the mentioned query, if there are any static TABLES used, make sue of (NOLOCK) which will help reduce the time

    e.x: if the same table is being used by some other object at the same time this will be a problem hence use NOLOCK.

    NOTE: Use this NOLOCK only if its astatic table, else you will be DIRTY reading.

    Actually, removing COUNT(*) in favor of COUNT(1) is... a myth. In fact, COUNT(*) may work better in some cases because it can take advantage of indexing whereas COUNT(1) cannot.

    As previously mentioned, this appears to be an ORACLE query. There is no WITH(NOLOCK) in Oracle. Besides, as a blanket recommendation, it's a bad thing to do because it can allow for the duplication of data and, face it... it's just a mask for bad code. The key is to fix the code.

    😉

    --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)

  • October 10, 2010 at 10:31 pm

    #1234772

    The two COUNT(*)'s are part of correlated subqueries (joined on the "pm" alias) with aggregates. It would likely be much cheaper computationally if those aggregates were done separately and stored in a Temp Table and then joined to. Unfortunately, Oracle's concept of what a Temp Table is is MUCH different than what SQL Server has.

    So... I recommend some "Subquery Refactoring" which is Oracle's fancy name for Common Table Expressions. You could also do the Subquery Refactoring in the FROM clause. The real key is to turn the subqueries into NON correlated subqueries because of the aggregations involved.

    Then, there are the other things that other folks have already stated. Non Sargable WHERE clauses like the following make it impossible to get an index seek... even in Oracle.

    trunc(pm.last_completion_dt) > trunc(sysdate-7)

    --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 4 posts - 16 through 18 (of 18 total)

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