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.

 

 

SEARCH ARTICLES

CATEGORIES

Sitecore 144
Commerce 100
Web Development 100
Sitecore Commerce 83
Sitecore Experience Commerce 9 64
Sitecore Experience Commerce 59
Content Management 55
eCommerce 48
B2B eCommerce 42
Sitecore Experience Platform 39
Sitecore Platinum Partner 39
Architecture 34
Insite 29
User Experience 26
Strategy 22
B2C eCommerce 21
B2B Commerce Blogs 21
commerceconnect 21
CloudCraze 20
SaaS (Software as a Service) 20
Cloud 17
Commerce Server 17
Salesforce B2B Commerce Cloud 16
Mobile 13
Search 13
Plugins 12
Analytics 12
Application Development 12
Digital Transformation 11
Sitecore Symposium 11
SPEAK 10
Helix 10
DMS 8
Social 8
Business Process 7
Authentication 7
5 Reasons 7
BI and Big Data 6
Data Visualization 6
Sitecore Presentation 6
Coveo 6
NET Development 5
Microsoft Azure 5
Automation 5
Front-End Development 4
SaaS (Software as a Service) 4
Digital Strategy 4
Brightcove 4
Press Release 4
Avalara Tax 3
Sitecore Experience Accelerator (SXA) 3
Sitecore Layouts 3
Video 3
SPE 3
Multi-site 3
Multi-lingual 3
Accessibility 3
Habitat 3
Vault 3
Identity 2
Managed Services 2
CDN 2
SMB 2
Cryptocurrency 2
Sitecore Forms 2
Sitecore Experience Commerce 9 Promotions 2
Uncategorized 2
EXM 2
Conversational Commerce 2
Sitecore SaaS 2
Security 2
Unit Testing 2
Headless Architecture 2
Sitecore Experience Awards 2
Google 1
Content Delivery Network 1
Configure Price Quote 1
CPQ 1
Blockchain 1
Coupons 1
Sitecore Rss 1
Artificial Intelligence 1
Machine Learning 1
Okta 1
RFP Process 1
NoSQL 1
Flex Accelerator for Sitecore 1
Reviews 1
SEO 1
Page Labels 1