• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 841
  • Last Modified:

Adding Indexes to Table hurts query performance (self-join)

I'm optimizing a query on SQL 2000.  The query joins a single table to itself 5 times to get various MAX(values).  

When I add a large covering clustered (or non-clustered index) to the table to help the query, performance suffers greatly.  This is a select statement only.

Why would a large index negatively impact read performance ?
0
JaffaKREE
Asked:
JaffaKREE
  • 2
  • 2
1 Solution
 
SQL_SERVER_DBACommented:
DO REBUILD
0
 
JaffaKREEAuthor Commented:
Rebuild the index ?  I've just created it.
0
 
JR2003Commented:
How many rows has the table?
Are you accessing columns that aren't in the index?
Can you paste the query in with the indexes you have created?
0
 
JaffaKREEAuthor Commented:
> Are you accessing columns that aren't in the index?

After looking at this, I believe I made a mistake and excluded a necessary column from the index.  

I was concerned that the process, since it was accessing the same table/index 5 times, may have been deadlocking/blocking itself.


0
 
JR2003Commented:
sometimes it does take longer using an index depending one how many rows are accessed and what columns are on the index.
0

Featured Post

2018 Annual Membership Survey

Here at Experts Exchange, we strive to give members the best experience. Help us improve the site by taking this survey today! (Bonus: Be entered to win a great tech prize for participating!)

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now