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.
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:
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.
To learn more about these important topics, watch the recording of the Extreme Salesforce Data Volumes webinar.
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.
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.
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.
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.
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.