Managing your Salesforce organization’s data is a crucial part of keeping your organization healthy, and you might have heard about one tool that can help it stay fit: skinny tables. Read this post to learn how skinny tables work, how they can help you with large volumes of data, and what you should consider before using them.

Long-Term Force.com Performance Best Practice: Keeping Your Org Lean

I’d like to re-emphasize the importance of managing your Salesforce organization’s data to keep your organization as lean as possible. The best way to lose weight is to avoid gaining it in the first place.

Managing your data properly can allow you to:

• Minimize how much data storage you use and avoid exceeding your organization’s data storage capacity
• Architect and implement solutions that minimize both the resources you use and your odds of hitting governor limits
• Simplify your object model so that it’s easier to implement and maintain

Defining and executing a data management strategy during every implementation can help you architect for usability and ensure peak performance, even if your organization has ample storage in Salesforce or does not anticipate significant data growth. Your strategy can also help you avoid costly performance problems and data management resolutions—an ounce of prevention is worth a pound of cure.

Here are some other, less catchy sayings, which are no less true.

• Only store what’s needed.
  Carefully study business requirements and design your application so that it’s functional, and balances data storage and usability.

• Know your expected data growth rate.
  Regardless of your organization’s data volumes, follow coding and architecting best practices for projecting data volume growth. Doing this allows you to test against base volume performance before going live, uncover potential risks, and even clarify what you need in your data-archiving and -purging strategy.

• Define criteria for data archiving and purging.
  Do not call your design phase “done” without an archiving-and-purging plan, the only plan for offsetting your data growth. Data purging at a later phase is much more complex than most people assume. It might be as simple as periodically using a Bulk API call with the hard delete option, implementing triggers to copy aggregated data into a custom object, or storing summarized data using analytic snapshots. It might also be as involved as moving the data to an external system and referencing the data with mashups.

To learn more about these important topics, watch the recording of the Extreme Salesforce Data Volumes webinar.

Quick Force.com Performance Fix: Using Skinny Tables

Say you’ve followed coding best practices and worked with salesforce.com Customer Support to place custom indexes wherever appropriate, but you’re still encountering performance problems. Users are complaining about their reports and dashboards timing out, and the SOQL called from your Visualforce page is performing slower and slower. If you desperately need to further improve performance, we have a special, prescription-strength solution: skinny tables. But as with any strong medication, there are side effects that you must carefully consider.

The Benefits of Using Skinny Tables

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 and keeps them completely transparent to you.

By having narrower rows and less data to scan than the base Salesforce object, skinny tables allow Force.com to return more rows per database fetch, increasing throughput when reading from a large object, as this diagram shows.

Furthermore, skinny tables do not include soft-deleted rows (i.e., records in the Recycle Bin with isDeleted = true), which could also reduce the table volume in some cases. Custom indexes on the base table are also replicated, and they usually perform better because of the reduced table joins that happen in the underlying database queries.

The Force.com platform automatically synchronizes the rows between the base object and the skinny table, so the data is always kept current. The Force.com platform determines at query runtime when it would make sense to use skinny tables, so you don’t have to modify your reports or develop any Apex code or API calls.

The Risks Associated with Using Skinny Tables

People sometimes mistake skinny tables as a convenient means of remedying performance issues, but skinny tables might not accommodate all use cases, or improve performance more than reading from the base Salesforce object with efficient indexes. They come with “side effects” that you should understand, as they might restrict or burden your business processes.

Before implementing skinny tables, consider the following.

• Skinny tables are skinny. To ensure optimal performance, they contain only the minimum set of fields required to fulfill specific business use cases. If you later decide to add a field to your report or SOQL query, contact salesforce.com Customer Support to re-create the table.
• You might think, “Why not just add all the fields at the beginning to get around the previous limitation?” Salesforce.com can add up to only 100 fields, and those fields must be of a certain type. For example, formula fields, fields derived from other objects (i.e., through lookup fields), and large CLOB fields cannot be added—if they were, we wouldn’t be calling skinny tables “skinny” at all.
• Skinny tables don’t get copied over to sandbox organizations. This limitation might not be an issue for you, but to keep your production and sandbox environments consistent, you must track your changes and work with salesforce.com Customer Support to keep your environments in sync.
• 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.

Summary

While it is important to follow best practices for managing large volumes of data—and to execute archiving-and-purging strategies as much as possible—understanding skinny tables might help you further improve your Force.com performance. If you take proactive measures, clarify skinny tables’ possible effects on end users, and use skinny tables only when appropriate, you can keep your organization fit, smart, and fast.

Related Resources:

• Best Practices for Deployments with Large Data Volumes
• Extreme Salesforce Data Volumes webinar
• Architect Core Resources

About the Author and CCE Technical Enablement

Daisuke Kawamoto is an Architect Evangelist within the Technical Enablement team of the salesforce.com Customer-Centric Engineering group. The team’s mission is to help customers understand how to implement technically sound salesforce.com solutions. Check out all of the resources that this team maintains on the Architect Core Resources page of Developer Force.