Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Analyzer
Posting Yak Master
115 Posts |
 Posted - 2008-03-27 : 11:49:01
|
| How do I improve a 3 column, composite clustered index on a large table when the developer insists there is no other way to achieve uniqueness? They say a uniqueindentifier column will not work. |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2008-03-27 : 11:53:37
|
What is large?You could try to add an IDENTITY column and make the present composite clustered index unique instead?CREATE UNIQUE NONCLUSTERED INDEX IX_MyComposite ON MyTable (Col1, Col2, Col3)Or don't have an index at all, just make a constraint?
E 12°55'05.25"N 56°04'39.16" |
 |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2008-03-27 : 11:54:21
|
As you can see, there are plenty of alternatives.Which one to choose depends on your business rules regarding that table.If the table is "large", I would suggest to drop the clustered part due to performance issues when inserting a new record at the "beginning".
E 12°55'05.25"N 56°04'39.16" |
|
|
|
Analyzer
Posting Yak Master
115 Posts |
Posted - 2008-03-27 : 12:11:35
|
| Tables range between 5-200 million rows (partitions/archiving is next on the list). Of course this composite idx is killing performance; but they also perform large READS from the table so until I overhaul the architecture I need to strike a balance. Whats the best strategy, remove the idx or change to a unique, non clustered? |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2008-03-27 : 12:59:00
|
Are the searches made upon some of the three columns in the composite index?It not, change the composite index to a constraint instead.
E 12°55'05.25"N 56°04'39.16" |
|
|
|
Analyzer
Posting Yak Master
115 Posts |
Posted - 2008-03-31 : 11:41:21
|
| It's still a conundrum; INSERTS require 3 column unique constraint; using PRIMARY KEY 3 column CLUSTERED IDX benchmarks x4 times quicker than the PRIMARY NONCLUSTERED 3 Col or just 3 Col CONSTRAINT alternatives. The 150,000,000 row table is also subject to READS and UPDATES so having no index is impossible. Problem is the 3 Col Clustered Index is also very poor and subject to range scans hurting performance. |
|
|
|
|
|
|
How to replace a 3 column composite index