Results 1 to 6 of 6
  1. #1
    Join Date
    Apr 2008
    Posts
    1

    Unanswered: urgently need the Query Optimization

    hi guys

    i have a query which will take lot of load on the sql server and it need to optimized.

    can any one suggest in optimizing the below query.


    SELECT TOP 1 p.store_purchase_id

    FROM valid_purchase p WITH (NOLOCK)

    INNER JOIN purchase_item pitm WITH (NOLOCK)

    ON p.purchase_id = pitm.purchase_id

    INNER JOIN customer_phone c WITH (NOLOCK)

    ON p.customer_brand_id = c.customer_brand_id

    INNER JOIN phone ph WITH (NOLOCK)

    ON c.phone_id = ph.phone_id

    WHERE

    p.last_name = ''

    AND pitm.item_datetime > CURRENT_TIMESTAMP

    AND pitm.item_datetime <= DATEADD(d, 12, CURRENT_TIMESTAMP)

    AND REPLACE(ph.number, '-', '' ) = ''



    Thanks
    Ramakanth

  2. #2
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    do you see any table scans in the execution plan? what does your table schema look like? where are your indexes?

    You do know that SELECT TOP 1 without an order by is meaningless and will eventually give you unintended results?
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  3. #3
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    That's gonna be pretty decent performance (schema not withstanding). An index on phone number can't be used though:
    Code:
                 AND ph.number IN('-', '') 
    Testimonial:
    pootle flump
    ur codings are working excelent.

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Hmmm. Customer phone table looks like an iffy design decision though.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  5. #5
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Quote Originally Posted by pootle flump
    That's gonna be pretty decent performance (schema not withstanding). An index on phone number can't be used though:
    Code:
                 AND ph.number IN('-', '') 
    Actually, if an index exists on "number", it will be used with this syntax. It will not be used in the original query.
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Thanks for clarifying Robert - that was my point but poorly made!
    Testimonial:
    pootle flump
    ur codings are working excelent.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •