[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 205
  • Last Modified:

Creating Indexes on a Table And Improving performance

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
ken hanse
Asked:
ken hanse
1 Solution
 
anilallewarCommented:
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

[Webinar] Kill tickets & tabs using PowerShell

Are you tired of cycling through the same browser tabs everyday to close the same repetitive tickets? In this webinar JumpCloud will show how you can leverage RESTful APIs to build your own PowerShell modules to kill tickets & tabs using the PowerShell command Invoke-RestMethod.

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