Solved

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

Posted on 2007-11-26
5
836 Views
Last Modified: 2012-06-27
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
Comment
Question by:JaffaKREE
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
5 Comments
 
LVL 16

Expert Comment

by:SQL_SERVER_DBA
ID: 20352377
DO REBUILD
0
 
LVL 6

Author Comment

by:JaffaKREE
ID: 20352448
Rebuild the index ?  I've just created it.
0
 
LVL 18

Accepted Solution

by:
JR2003 earned 500 total points
ID: 20352475
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
 
LVL 6

Author Comment

by:JaffaKREE
ID: 20352547
> 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
 
LVL 18

Expert Comment

by:JR2003
ID: 20353758
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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

691 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question