August 22, 2011 at 10:03 am
I have this table containing 11407662329 rows and new data is constantly inserted into the table.
There is only a primary composite key in this table using three columns - columnA INT, column B smallint, columnC BIGINT. It seems to affect the performance especially when users try to read the record and insert the record at the same time. Would it be better creating a surrogate key and change the primary composite to non-cluster index.
Thanks
August 22, 2011 at 10:04 am
No possible way to make that call without seeing the standard queries hitting that table, their execution plans and the % of each query type.
August 22, 2011 at 10:16 am
It really depends on the queries. Are they getting seeks on the index or not? Also, you said primary key, but is it the clustered index? How the cluster is set up can certainly affect reads in terms of blocking while inserts occur.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
August 22, 2011 at 10:48 am
Yes it is the cluster key.
The bigint actually is a time field (millisecond) calculating from 1970/01/01.
The where clause of the main query is
COlumna = ? and columnC >= ? and columnC <= ?
August 22, 2011 at 10:59 am
Then why is the CI not a,c, then b?
August 22, 2011 at 11:08 am
It is because when the application wants to get all data for columnB.
But all three columns are used to make a unique id.
August 22, 2011 at 11:12 am
I don't see anything for column b here?
Loner (8/22/2011)
Yes it is the cluster key.The bigint actually is a time field (millisecond) calculating from 1970/01/01.
The where clause of the main query is
COlumna = ? and columnC >= ? and columnC <= ?
August 22, 2011 at 11:28 am
I want to return all columnB.
August 22, 2011 at 11:36 am
Loner (8/22/2011)
I want to return all columnB.
Then I don't think columnb needs to be in the index at all. Since it's a clustered index, columnb is stored at the leaf. That's all you need.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply