SQLServer2000 is not using and index in query

Question

SQLServer2000 is not using and index in query

Mehboob ur Rehman

Grasshopper

Points: 15
More actions
May 29, 2007 at 7:38 am

#117256

I have 2 tables A, B in SQL 2000. Table A contains 1,323,540 rows and table B contains 1,902,932 rows. The Primary Key of Table A is used as foreigh in Key in Table B. Indexes on both fields in both tables are created (PK in A, Non-Clustered in B).
I am simply joing tables A and B on foreign key and applying a filter in where criteria on table A. The query is executed very slow. I checked the EXECUTION PLAN and it shows that it is scanning the table B. I think it should seek index created on table B but it is not doing this.
Please help why it is behaving like this.

Viewing 13 posts - 1 through 12 (of 12 total)

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

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 1

Could be a number of things. How often do you update your statistics and/or reindex your tables? Use DBCC SHOWCONTIG to find out how fragmented your indexes are.

John

philcart SSC-Forever Points: 47794 More actions · Answer 2

Posting the query might help us guide you towards ensuring the indexes you have are appropriate.''

--------------------
Colt 45 - the original point and click interface

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

Yes, please post the query... chances are, the criteria in the WHERE clause is what's keeping the index from being used...

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

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 4

Without seeing anything I can pretty confidently state that it is either old statistics (and you are asking for data at the 'tail' of the set) or more likely you are simply asking for more than 10-20% of the total data in the set in which case the optimizer won't use an index anyway since index usage will be more costly than table scans.

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 5

or more likely you are simply asking for more than 10-20% of the total data in the set in which case the optimizer won't use an index anyway since index usage will be more costly than table scans

Heh... we've been though this... that's just not true in all cases especially if a covering index is involved...

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

Scott Coleman One Orange Chip Points: 27452 More actions · Answer 6

Another possibility, if the previous excellent suggestions don't fix the problem, is having different data types in the joined fields. This requires a conversion function which makes indexes useless. The fields in a foreign key relationship would have to be compatible, but if there are additional fields in the join this is a possibility.

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 7

Good of you to remember our last discourse Jeff!! HEHEHE

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

Peter Tillotson Hall of Fame Points: 3325 More actions · Answer 8

Try moving the filter condition on table a from the WHERE clause to the ON clause. This generally speeds up most queries, as the WHERE clause is not applied until all the records in the tables are merged.

Regards, Peter

Jeff Gray SSChampion Points: 10667 More actions · Answer 9

Do you have any examples of this? I'd be interested in seeing them.

Peter Tillotson Hall of Fame Points: 3325 More actions · Answer 10

I take it back. With this simple example it doesn't seem make any difference when I checked further. It is more useful when there are more than two tables joined together.

What I was suggesting was changing:

Select * from tableb b inner join tablea a on b.fkey = a.pkey where a.somefield = 'somevalue'

to:

Select * from tableb b inner join tablea a on b.fkey = a.pkey and a.somefield = 'somevalue'

kiatisak chaiyossomporn Newbie Points: 1 More actions · Answer 11

This query may useful

Select a.* from a

inner join b

on (a.id = b.id and b.age >30)

where a.sex = 'M'

Bye

philcart SSC-Forever Points: 47794 More actions · Answer 12

???

--------------------
Colt 45 - the original point and click interface