I have a table name d tblAttribHist

[lpatnaik]

I have a table name d tblAttribHistory. This table contains columns "attribid", "attribtype", "languageid", "attribname", "validfrom" and "orgid". The prmary key comprises of "attribid", "attribtype", "languageid" and "validfrom".

There is the following index on the table:
- nonclustered, unique, primary key located on PRIMARY(AttribID, Attribtype, LanguageID, ValidFrom)

There could be a lot of rows with all the columns same except for the "validfrom" column.

The requirement is to improve performance of this table.

Please inform me how i can improve performance of this table by using indexes/views.

 

[SQLSister]

Views do not improve performance.

I would suggest that if you are ever using this table to join on other tables that your primary key idea is poor. Joins are more efficient if done on integers and are especially inefficient if done on multiple fields like this one.

I would have an identity field as the primary key and set these four field up with just a unique constraint. Then you wouldn't have to repeat the information in any child tables either.

As to what indexes, it depends on how you search the data. What fields are you using in the search queries? Run your queries and look at the execution plan and try to make it more efficient.

It may take a lot of experimenting to get just the right mix.

And you need a better requirement than just improve performance. Inmprove insert performance? Improve select performance? (You can't do both simulateously) Improve which particular queries or stored procedures? How much improvement is needed?

Suggest you buy a book on performance tuning as this is a highly complex subject with lots of different variables. I like SQL Server Query Performance Tuning Ditilled by Sajal Dam.