August 23, 2010 at 11:09 am
I have a warehouse loaded by a 3rd party app. Of course the bottle neck table has no indexes. This table has about 30 columns in it which are used to comprise data to load tables in the warehouse. What happens on a nitely basis is the data older than 1 year is deleted and the last night's data is loaded. So for this table not a lot of updates. mostly deletes and sequential adds. This table is in several of the loads to 2ndary tables and usually is over 100,000 rows plus with these loads. My thought was to consider making a fairly wide clustered index because of the large amounts of data (thinking that smaller or covering indexes are better off for small amounts of data), this clustered index may slow the initial load and delete, but I would get my gains with the large loads. Any thoughts on this approach
August 23, 2010 at 10:54 pm
Is the clustered index only index on the table? If there are other non-clustered indexes as well on the table, I won't prefer clustered index to be wide. In your case, creating clustered index on date column may be a good idea but adding too many columns in clustered index may not be good.
August 23, 2010 at 11:23 pm
This series may be worth a look - http://www.sqlservercentral.com/articles/Indexing/68439/
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 25, 2010 at 4:01 am
if possible , post your query which is being used for migration
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply