Solved

Creating Indexes on a Table And Improving performance

Posted on 2009-07-13
1
186 Views
Last Modified: 2012-05-07
My table has a composite primary key on five columns, so indexes also have five columns. If I want to improve performance can I drop the composite index and build an index on each of the five columns individually? Would this help me in improving performance?
0
Comment
Question by:ken hanse
1 Comment
 
LVL 11

Accepted Solution

by:
anilallewar earned 250 total points
ID: 24846559
Not necessarily; depends on the data distribution. Is your primary key is clustered? If so ensure that the most frequent column on which you query is the first in the primary composite key. If you use group by column create seconday indexes with first column in primary key and the group by columns. This way you can use the physical ordering clreated by the clusterred index and the group by column.

I had a similar situation for a reporting application which had around 40 million records and close to 400 bytes per row. The table previously had a primary key on identity(sequence) column and indexes on all other columns that could be part of dynamic select statements. The performance was poor.

We changed the index structure to have primary key on the 2 most used columns and the sequence(resulting in unique primary key) and non-clustered indexes on other columns which were part of group by. The non-clustered indexes were created as combination of first column in primary key and group by column.

The performance improvment we got was close to 4000% on an average!!
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

In database programming, custom sort order seems to be necessary quite often, at least in my experience and time here at EE. Within the realm of custom sorting is the sorting of numbers and text independently (i.e., treating the numbers as number…
If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

707 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now