1

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

Goal

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.

Questions

  • 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 TreatmentId ?
  • Any other suggestion ?

Precisions to answer the comments:

  • Most of the querying revolves around this TreatmentId field. The other fields are not parts of where clauses.
  • Space is in issue because of virtual hosting billing.
  • I tried the index on TreatmentId alone first, it did not yield good results, as the queries did not pick it up.
  • 1
    You mention that TreatmentID is mostly used for queries. Are any other columns used for queries too? – Thomas Kejser Oct 8 at 17:00
  • 1
    Why is the space an issue? Is the table compressed at all? – LowlyDBA Oct 8 at 17:27
  • 1
    Have you tried creating an index with only TreatmentId? Although it won't be covering, the lookups should be far better than a table scan. You might change the question from "what is the preferred approach," which is impossible for us to know without more information and profiling different approaches. Instead you might ask what are the different options and the pros and cons of each option. – Tony Hinkle Oct 8 at 20:17

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.