February 18, 2010 at 11:15 am
I've got a table with a poor clustered index: it's always increasing, except for one value, that keeps inserting new rows.
The table (15 million rows, high read rate, low write rate) looks like this:
CREATE TABLE Shipment (
ShipmentNo int NOT NULL,
Row int NOT NULL,
.... some more columns ...
PRIMARY KEY CLUSTERED (ShipmentNo, Row)
)
The Shimpment number is always increasing and the Row column contains the row number in the shipment. Every shipment is created completely and never updated with new rows or deleted rows, so that the clustered index is always increasing.
The problem here is a special "virtual" shipment with a fixed number (9999), that is updated with new rows every day. This makes the index horribly fragmented, as shipments exist with higher and lower shipment numbers.
I was thinking of partitioning the table, in order to create a different partition for shipment number 9999.
I have never looked into partitioning, but this looks like a good chance to learn how to do it. I read some articles[/url], but I can't understand if this is a good application for partitioning. Also I don't know what will happen with performance: will it be better or, as I read in some articles, worse?
What should I consider to decide where to go?
Thanks
Gianluca
-- Gianluca Sartori
February 18, 2010 at 11:24 am
I would definitely consider breaking that into its own partition.
As for performance, et al, the only way to be sure is to test it thoroughly.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
February 18, 2010 at 11:32 am
Thanks Gus. I'll be sure to test it, obviously.
I read on a quite old article that partitioning makes reads worse, but improves writes. Is it true in your experience?
Also, will this prevent the index from fragmenting the way it does today?
-- Gianluca Sartori
February 18, 2010 at 11:57 am
The speed really depends on how you set it up.
I currently have a table with over 30-million rows of varbinary(max) data in it. I'm in the process of partitioning it because a 2 Tb table is a bit much to deal with (backups, etc., are a pain). On the test system, read and write time go down by half each in the partitioned version compared to the non-partitioned version. Both speed up. That's before splitting it onto separate I/O systems.
I used to deal with an OLTP system that had a couple of big tables (hundreds of thousands of rows), where only the most recent thousand or so had any data changes going on, and the rest were essentially read-only. I partitioned that, using the older techniques in SQL 2000, with a scheduled archiving routine from Live to Archive. The Live partition only had a few key indexes on it. The Archive partition had covering indexes for just about everything that regularly hit it. Setting that up took some serious work, but it sped up the whole application by a huge amount. Everything was MUCH faster, reads and writes.
Others may have very different experiences, but for me partitioning has always resulted in a very good overall speed improvement. But I've only done it a few times on some big, active tables.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply