December 19, 2006 at 3:20 am
Hi All,
I have become recently confused with the myth behind placing a clustered index on a table with heavy DDL operations, Inserts 24hours a day, 7 days a week, Selects 24hours a day, 7 days a week, Deletes twice a week.
This table forms part of a ETL application, the clustered index is on a varchar field and also a date field which is always increasing, If I want the inserts to be fast, Selects to be fast and also deletes not to suffer, Is it sensible to add a Clustered index on the date and order it in the clustered index, so that that way inserts will be placed at the end of the index tree etc, and also if I want to delete, the delete will be done from the top of the ordered clustered index.
Any ideas welcome.
December 19, 2006 at 4:34 am
John
I don't advise putting a clustered index on a varchar column, especially if it means inserts will be made in the middle of the index - this will cause page splits and slow down inserts and other database activity that may be happening at the same time. Also, if your varchar column is wide, your clustered index will also be wide, meaning all your non-clustered indexes will be wide, meaning you will fit fewer rows on page, and so more pages have to be read to return a result set. I'm sure you get the idea.
There have been quite a few discussions on this topic in the past... do a search and see what you come up with. Include "hotspot" in your search to see a potential gotcha involved with creating a clustered index on an increasing column.
John
December 19, 2006 at 6:14 am
The answer is the one that fits your situation .. I never take the blinkered approach as applications are often not ideal and thus "textbook" solutions do not apply. Beware also of posters who haven't been there and got the T shirt, Ken Henderson wrote a good page about how to appear to be an expert in a forum ( and I'm not pointing this at anyone at all on this site ). Ultimately you need to try it in a test environment.
The clustered index orders the data within the table, that's generally the most important factor to remember, If you want to order data by a varchar then fine - enter yet more arguments about real vs surrogate keys !! What works for you and what gives the performance you require is right .. having a clinical approach is cool but not cool if your application is not performing because you read somewhere you shouldn't put a clustered index on a varchar column!! enjoy!!
ps. I make my living tuning servers and applications - rule 1 - there are no rules!!
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
December 19, 2006 at 4:23 pm
Placing the CLUSTERED index on the Date column would be better that on the varchar column for DML commands but this is not my advice. First, why would you need a clustered index on the date column, or the varchar column or for any column? You need to check the frequently performed queries (Selects) on the table to determine whether to put an index or not and where to put it. The column size should be a secondary consideration.
i.e. Why would I put and index on the date column when my query is "Select * from table having sum(QTY) > 1000"?
Check your query. See where the index should be placed (useful). Then consider the performance over your DMLs.
December 20, 2006 at 2:42 pm
Another alternative would be table partitioning by date, whether a partition is one day, 7 days, etc. This determining factors would probably be insert volume(s) and table availability. The down side is more complexity, more tables, more maintenance and additional views with UNION ALL. The upside is quick insertion, quick index building, quick stats execution, quick ETL loads of staged data and quick deletions.
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply