Still shows scan

Question

Post reply

Still shows scan

samsql

Hall of Fame

Points: 3044
More actions
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

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

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

Suresh B. SSC-Insane Points: 22986 More actions · Answer 1

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

Jack Corbett SSC Guru Points: 184393 More actions · Answer 2

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

Jeff Moden SSC Guru Points: 1004177 More actions · Answer 3

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)

MANU-J. SSC-Dedicated Points: 31126 More actions · Answer 4

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