Salesforce Skinny Tables

Introduction : 

Potentially most of the customers have the large amount of the data in salesforce tables, which may cause more processing time is, at least partly, dependent on how long it takes to scan the data especially when we are scanning on the relationships. So one way of improving performance is to trim up the Skinny table. Naturally, incremental processing is useful for these very large fact tables, but everything will go faster with Skinny tables.

What is the Skinny table?

A skinny table is a custom 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 under the hood.

 

Skinny Table to Speed up Account Queries

Do we need a Skinny table? 

Before deciding do we need the Skinny table or not, first, let’s understand how salesforce design the Standard database.Below Images how salesforce organizes the Account table under the hood.

From the user view, an account object is a single table. But internally salesforce stores in two different table for an Account. One table for account standard fields and one table Account custom field which is partitioned rather than a single table. When end-user query the account table salesforce will perform the join on both the tables to return the data, which is little overhead if you have the huge amount of records. In this case, you can contact salesforce to create Skinny tables which will return more rows per fetch.

When to Use?

Skinny tables are most useful with tables containing millions of records. 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. The skinny table allows you to reorganize selected columns and rows of data into a separate table.

Any Considerations?

  • Contact Salesforce to create Skinny tables
  • Used to fetch frequently used fields and to avoid joins.
  • Skinny tables are most useful with tables containing millions of records.
  • Skinny tables can contain these types of fields.  Checkbox,  Date, Date and time, Email, Number, Percent, Phone picklist, Picklist (multi-select), Text, Text area, Text area (long), URL
  • skinny tables do not include soft-deleted rows (i.e., records in the Recycle Bin with isDeleted = true),
  • The Force.com platform automatically synchronizes the rows between the base object and the skinny table, so the data is always kept current.

Any Impact?

  1.  Skinny tables can contain a maximum of 100 columns.
  2.  Skinny tables cannot contain fields from other objects.
  3.  Skinny tables are copied to your Full sandbox organization from Summer ’15 release.
  4.    Skinny tables are custom tables in the underlying Force.com database. They don’t have the dynamic metadata flexibility you find in the base object. If you alter a field type (e.g., change a number field to a text field) the skinny table becomes invalid, and you must contact salesforce.com Customer Support to create a new skinny table.