Offer History partitioning

Overview

Table partitioning splits a large table into smaller, more manageable pieces (partitions), while still being treated as a single logical table by the database. Each partition can store a subset of the table’s data based on a defined rule (e.g., timestamp. Applying partitioning is different in each database technology, but generally has some benefits, including performance and archiving older data. Having the partition key in the queries against the table will help performance, but archiving the data will also benefit performance by limiting the overall dataset when the partition key is not included in the query.

Additional information on Offer History (OH) best practices can be found here.

What do you use for partitioning?

There are timestamps in each of the following tables. Offer History Details is relatively small, so it shouldn’t need partitioning. The other tables should be partitioned on Timestamp.

• Offer History (partition)

• Offer History Sandbox (partition)

• Offer History States (partition)

• Offer History Details (small, no partition)

Learn more about Offer History Tables.

High-level steps to implement partitioning

The following steps show you how to apply partitioning to your Offer History tables:

1. Determine the date-based partition strategy; what is the size of the partitions based on using the timestamp in the Offer History tables (Monthly, quarterly, etc.).

2. Create partitioned tables; depending on your database technology, you will need to create/partition Offer History tables. You also may need to create OH from scratch vs. modifying the existing OH tables.

3. Migrate existing data into the new tables; if you are partitioning an existing table and it’s large, you may need to migrate the table in steps. Review the high-level steps below for partitioning an existing large set of Offer History tables in SQL Server.

4. Determine an archive strategy; over time the Offer History tables can grow large, and it's a good idea to maintain a rolling set of data in the table. The amount of data to keep will be specific to your business needs. Some examples would be 12, 25, or 36 months. The partitions can help with moving the data to the archive easier and faster than copying the data out and deleting it from the source table.

Failure to partition all Offer History tables consistently can lead to inconsistent behavior and performance

High-level steps for partitioning an existing SQL Server Offer History table

1. Create the date partition scheme and function.

2. Create the new partition table(s).

3. If using Column Store Index (CCI), then create CCI on the partition table(s).

4. Load data into the new partitioned table(s) from OH. If the data is large, you need to do this step in batches; utilize multiple temp_partitioned_tables in parallel for speed.

5. Create original OH INDEXES (add after load) on the new partitioned table(s).

6. Switch all new partitions to "final" temp_OH_table.

7. Check and reset "RPContactID" IDENTITY property to MAX+1 in final temp_OH_table.

8. Finally rename temp_OH_table to replace the existing table, swapping the unpartitioned with the partitioned.