allBlogsList

Sitecore Commerce - How to Extend SQL Sharding

In previous versions of Sitecore Commerce the SQL Database Schema included 2 tables:

  • CommerceEntities: Stored all Commerce Entities (Catalog Items, Orders, Promotions…) as JSON.
  • CommerceLists: Maps Entities to Lists (SellableItems To Category for example).

Those tables quickly reached a huge amount of Data and caused significant performance issues in some cases.

To solve the dilemma**, Sitecore Commerce 9** introduced a simple SQL Sharding technique driven by an Environment JSON Policy-Set: PlugIn.SQL.Sharding.PolicySet-1.0.0.json

Sitcore Comemrce Sharding Policy Set:

This policy set includes two Policy Types:

1. Sitecore.Commerce.Plugin.SQL.EntityShardingPolicy:

Maps an Entity ID to a table using a regular expression.

Example:

Sitecore Commerce Extending SQL Sharding Example One

 In this example, all the Commerce Entities with Ids starting like ‘Entity-Catalog%’ would be persisted/expected in Table ‘CatalogEntities’.

2. Sitecore.Commerce.Plugin.SQL.ListShardingPolicy:

Maps a List ID to a table using a regular expression.

Example:

Sitecore Commerce Extending SQL Sharding Example Two

In this example, all the commerce Lists with Ids starting like ‘List-Promotion%’ would be persisted/expected in Table ‘PromotionLists’

As you can see it’s a very flexible technique that allows you to easily decide how you want to distribute your data. However, you need to be cautious to not use ambiguous rules. You need to make sure your Entity or List ID fits in one policy/regular expression only.

Out of the box, Sitecore commerce comes with these tables:

Sitecore Commerce Extending SQL Sharding Example Three

Extending the Sharding policy:

The Sharding policy can be extended in 3 easy steps:

1. Manually create the tables(s) you’d like to add to the schema.

2. Update : PlugIn.SQL.Sharding.PolicySet-1.0.0.json to add your policy.

3. Bootstrap Commerce Engine.

P.S: You should extend the Sharding policy at the beginning of your project or after a data clean/re-initialize. If you extend over existing data, you might not be able to retrieve old Entities/Lists.

Examples**:**

Below two examples of how you could extend the Sharding policies:

Example 1:

You can decide to store Customers in separate tables. (OOB they’re stored in CommerceEntities and CommerceLists tables).

Step 1: Create the two tables using same definition as CommerceEntities and CommerceLists.

Step 2: Update PlugIn.SQL.Sharding.PolicySet-1.0.0.json to add these policies:

Sitecore Commerce Extending SQL Sharding Example Four

Sitecore Commerce Extending SQL Sharding Example Five

Step3: Bootstrap Commerce Engine.

Example 2:

OOB all Catalog items (Catalogs, Categories, Sellable Items) are stored in CatalogEntities table.

Let’s say you have a huge catalog and would like to shard your sellable items so they don’t all go to this default table.

Let’s assume your EntityIDs look like this: ‘Entity-SellableItem-{Some Guid}

We know a Guid starts with a number 0-9 or a letter A-F.

We can decide that: 

  • Guids starting 0-3 be stored in table ProductEntities_0
  • Guids starting 4-7 be stored in table ProductEntities_1
  • Guids starting 8-B be stored in table ProductEntities_2
  • Guids starting C-F be stored in table ProductEntities_3

The policies in that scenario would look like:

Sitecore Commerce Extending SQL Sharding Example Six

Sitecore Commerce Extending SQL Sharding Example Seven

Sitecore Commerce Extending SQL Sharding Example Eight

Sitecore Commerce Extending SQL Sharding Example Nine

Summary:

Sitecore Commerce 9 Sharding Policy is very flexible and easy to customize.

When you’re starting a new Sitecore Commerce project, you should take some time to check if OOB policy meets your needs and customize it accordingly.

You can also use a different policy per environment if needed.