2

I have a use case where I have a job that inserts around 30 million rows every hour. It's the kind of snapshot we are taking for some data source and we are interested only in the latest snapshot for reporting purpose, so once the new snapshot data comes, the old snapshot data becomes useless to us as reporting will be based on the new Snapshot row.

We have a field called last_updated_timestamp which is a flag to determine the latest data so all rows in one job will have the same timestamp and we are taking max(last_updated_timestamp) for our reporting needs.

We need to store data for 20 days only and 99% of our queries will have a fixed format so our queries will look like this:

select * from our_table where store_id = <>, dep_id = <> schedule_day <> and last_updated_timestamp = (max value which is present for that schedule_day for store_id and dep_id)

My Approach:

Is to create clustered index on store_id, dep_id, schedule_day, last_updated_timestamp so my queires will be very fast. For deleting the old snapshot how should I do it? Will it lead to index fragmentation?

schedule_day and last_updated_timestamp values are always increasing. Each job inserts the data for the present day and future 3 days no past data is inserted.

Any recommendations?

2 Answers 2

1

create clustered index on store_id, dep_id, schedule_day, last_updated_timestamp so my queires will be very fast. For deleting the old snapshot how should I do it? Will it lead to index fragmentation?

This is actually a perfect scenario for sliding window partitioning. Simply partition that clustered index by last_updated_timestamp, splitting the partition function for each day.

That way each day gets its own physical table, and to remove a day just truncate its partition.

6
  • I had partitioned my table based schedule_day , now i have to create cluster index on schedule_day also ? by default query with schedule_day filter will go to respective partition ?
    – Bad Coder
    Commented May 30, 2023 at 10:59
  • then i can create cluster index on three column only store_id, dep_id, 'last_updated_timestamp' ? Do i need schedule_day also in index ?
    – Bad Coder
    Commented May 30, 2023 at 11:01
  • The clustered index must include the partitioning column. Commented May 31, 2023 at 12:14
  • can i use the composite key then (schedule_day, store_id, store_id) ? Why is it necessary ?
    – Bad Coder
    Commented May 31, 2023 at 18:45
  • Within each partition the clustered index defines the sort order and the seek geometry. Commented Jun 1, 2023 at 0:48
0

Is to create clustered index on store_id, dep_id, schedule_day, last_updated_timestamp so my queires will be very fast.

Yes. Make sure there is no other queries with other conditions in WHERE section.

For deleting the old snapshot how should I do it?

If you can separate old snapshots like everything older than 3 days should be deleted you can create partitions based on insert data. In this case you do not delete rows but truncate or drop partition

Will it lead to index fragmentation?

Yes, it will. Worst case scenario (every page equally splitted) you have your table doubled in size. Is it a problem for you?

You should set up your priorities - if your primary concern is insert/delete you should change your clustered index with last_updated_timestamp and it won't cause a fragmentation - insert will in in one side of the table and delete from another side. However you have to create nonclustered index (store_id, dep_id, schedule_day) to support your main query.

In any case I'd run tests to measure every approach.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.