I have a big fact table in a datawarehouse (40 Gb worth of data in dev, 120gb in production ) which was created in a very classic way
CREATE TABLE BigFactTable ( BigFactTableId bigint IDENTITY PRIMARY KEY [PK.BigFactTable] CLUSTERED, TreatmentId int FOREIGN KEY [FK.BigFactTable.TreatmentID] REFERENCES Treatment(TreatmentId), --other columns )
But I have noticed this table is queried mostly with the TreatmentId field.
One of the most notable property is that TreatmentId grows along BigFactTableId, working like chapters in a big technical book.
Defining an index on (TreatmentId) with included fields bloats indexspace
- 30 Gb in dev for a lit less than 3 millions rows
- 100 Gb in production for more than 9 millions rows
Reduce the footprint of the table while keeping or even improving the query/insert performances
What I tried
In dev, dropping the indexes and the former clustered primary key, to rebuild the unique key associated to the now nonclustered primary key and then the right clustered index
CREATE CLUSTERED INDEX [CI.BigFactTable] on [BigFactTable] (TreatmentId,BigFactTableId)
The single issue : it took 1 hour and 40 minutes in Dev, during when the system was very busy. But at the very least, the queries based on TreatmentId go much faster.
- What is the preferred approach for this?
- Should I create a new table with the right clustered key right off the bat and insert the content of the former table in the right order, and then use a drop and rename approach ?
- Should I envision partitioning along
- Any other suggestion ?
Precisions to answer the comments:
- Most of the querying revolves around this
TreatmentIdfield. The other fields are not parts of
- Space is in issue because of virtual hosting billing.
- I tried the index on
TreatmentIdalone first, it did not yield good results, as the queries did not pick it up.