November 22, 2006 at 7:11 am
Hi All,
I have a bit of a mystery with a table recently created on a database, this table contains a summary/cube of daily tables, eg Personprofile_20061121 etc for a whole month.
First of all, I created the table without indexes etc
Ran a query to generate summary information.
Insert the summary information for one day into this table.
After running the look for the whole month, I then end up with a massive table 209,006,389
Rows of information, If I place an index on this table, It takes around 6 hours to place indexes on the table, with the clustered index taking more than half the time.
GO
CREATE CLUSTERED INDEX IX_ROWNUM_STARTDATE ON SUMMARY_TABLE_200610 (ROWNUM,STARTDATE ASC) WITH FILLFACTOR = 100
GO
CREATE NONCLUSTERED INDEX IX_CALLING_CALLED ON SUMMARY_TABLE_200610 (PERSONURN,ADDRESSURN) WITH FILLFACTOR = 100
GO
CREATE NONCLUSTERED INDEX IX_LOCATION_CLIENTID ON SUMMARY_TABLE_200610 (LOCATION_ID,CLIENT_ID,PROS_ID) WITH FILLFACTOR = 100
GO
name | rows | reserved | data | index_size | unused |
SUMMARY_TABLE_200610 | 209006389 | 41831736 KB | 23211120 KB | 18620240 KB | 376 KB |
I have the following questions and would need help in answering them:
I have the following questions, the clustered index has been placed on a date, and also an identity column.
Should I place the index on the table before inserting information into it or after, bearing in mind that for a day, almost 7m records are inserted into the summary table.
Do I also need to place a clustered index on this table ?
Can I have an index on the table once created, as this would mean that I don't have to wait for 6hrs to have an index on it, after it has 209m rows, but the consequences are that the inserts might be slower and the table would be heavily fragmented, this can be solved by running a dbreindex query.
Looking forward to your reply.
November 27, 2006 at 8:00 am
This was removed by the editor as SPAM
November 29, 2006 at 1:09 pm
John,
1. Yes you can create indexes on a table after data is inserted
2. It would actually be much better if you place indexes after you perform data insertion, as having indexes SLOWS DOWN inserts
3. Check out http://www.sql-server-performance.com/ for many performance related questions/answers
November 29, 2006 at 1:13 pm
Also, yes, you should add a clustred index to a table. As far as I know, you should not add it only if you have a good reason not to - otherwise you should. This would keep data together - otherwise it is a heap and could be spread all over the place - slowing down performance of the queries run against this table.
November 30, 2006 at 10:32 am
Actually, a clustered index may not be necessary, depending on how the data is queried. If each client is selecting only a single record at a time, the clustered index is not very useful. It is most useful when a client needs several records at a time, and the columns in the clustered index cover the columns in the query.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply