Post reply

Still shows scan

  • December 25, 2008 at 10:22 pm

    #216869

    hi i hav an update query which show table scan m confused how to i put indexes so that it does not scan the table

    Update temp1 Set Date = NULL

    Where (Amount <> Updated or Amount1 <> Updated1 or

    Amount3 <> Paid) and Date is Not Null And

    BID = '1002' and ID = 123 and L = 8

    i hav put the index on this table as

    1st index BID+ID+L

    2ND INDEX AMOUNT+UPDATE

    3RD INDEX AMOUNT1+UPDATE1

    STILL SHOWS TABLE SCAN AVEN AFTER THE INDEXES

    THIS QUERY UPDATE THOUSANDS OF DATA

  • December 25, 2008 at 11:30 pm

    #917692

    When <> (not equal to) condition is used table scan happens because all the records are to be compared.

  • December 26, 2008 at 8:19 am

    #917787

    Or's will also often cause table or index scans.

    Jack Corbett
    Consultant - Straight Path Solutions
    Check out these links on how to get faster and more accurate answers:
    Forum Etiquette: How to post data/code on a forum to get the best help
    Need an Answer? Actually, No ... You Need a Question

  • December 26, 2008 at 10:22 am

    #917857

    samsql (12/25/2008)

    hi i hav an update query which show table scan m confused how to i put indexes so that it does not scan the table

    Update temp1 Set Date = NULL

    Where (Amount <> Updated or Amount1 <> Updated1 or

    Amount3 <> Paid) and Date is Not Null And

    BID = '1002' and ID = 123 and L = 8

    i hav put the index on this table as

    1st index BID+ID+L

    2ND INDEX AMOUNT+UPDATE

    3RD INDEX AMOUNT1+UPDATE1

    STILL SHOWS TABLE SCAN AVEN AFTER THE INDEXES

    THIS QUERY UPDATE THOUSANDS OF DATA

    I don't believe it's gonna matter what you do because you are comparing columns within the same table. In fact, the indexes probably aren't helping at all nor can they be made to do so, in this case. The good news is that these types of updates really fly... this one will probably handle a million rows in less than 7 seconds.

    --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)
    Intro to Tally Tables and Functions

  • December 26, 2008 at 12:29 pm

    #917902

    How much records are there in temp1 table and how much out of these are getting updated by this code? Also, for making use of the present indexes try to have one covering index then only I think that index will be considered by the optimizer.

    MJ

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

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