Skinny Tables – Increase performance for large data volume

From an end users perspective, a form is just a bunch of fields to put the information. However deep down to technical world, there is a complex mechanism to maintain the information to speed up the search and performance of application.

Salesforce Database Architecture

In Salesforce, we have Standard Fields and Custom Fields. You might think there should be a single table where all the fields are considered as column. However this assumption is not true. Salesforce maintains two tables for single object, One for Standard Fields and another for Custom Fields.

Skinny Tables - Increase performance for large data volume
Skinny Table Architecute

As show in above image, Acme is an account having 6 fields, out from which Account Name, Website, Employee and FAX are standard fields and Region, District are custom fields. For an end user, its simply a form. Even for developer, it seems a single table while retrieving data from SOQL. However inside Salesforce database, there are two tables to store information of Standard fields and Custom field separately.

How Salesforce retrieves data from these two tables?

Salesforce actually performs a JOIN over two tables to combine the data and present it to end user, which is little overhead if any org is having huge amount of records. This is where Skinny table comes into consideration.

From the image above, lets say out from all the fields on account, only Account Name, Website, Employees and Region field data is needed for frequent data retrieve. So skinny table will only contain these four fields which are frequently needed. Due to this, excessive table JOIN will be skipped while retrieving information from Account Name, Website, Employees and Region fields.

What is Skinny Table?

A skinny table is an another table in the Force.com platform that contains a subset of fields from a standard or custom base Salesforce object. Force.com can have multiple skinny tables if needed, and maintains them and keeps them completely transparent to you.

What does Skinny Table do?

Its a separate table maintained which consists frequently used fields from an object. This avoids JOIN of two tables each time while requesting data. This results increase in performance and hence more data can be retrieved with single call.

Skinny tables are most useful with tables containing millions of records to improve the performance of read-only operations, such as reports. They can be created on custom objects, and on Account, Contact, Opportunity, Lead, and Case objects. In addition, they can enhance performance for reports, list views, and SOQL.

Skinny tables can contain the following types of fields.

  • Checkbox
  • Phone
  • Picklist (multi-select)
  • Date
  • Date and time
  • Text
  • Text area
  • Text area (long)
  • URL
  • Email
  • Number
  • Percent

Some Considerations:

  • Can contain a maximum of 100 columns.
  • Do not have fields from other objects.
  • Copied to Full copy sandbox on activation or refresh, not applied for partial, developer pro or developer sandboxes.
  • To activate, need to contact Salesforce Support.