What are Skinny Tables?

Salesforce creates skinny tables to contain frequently used fields and to avoid joins, and it keeps the skinny tables in sync with their source tables when the source tables are modified. To enable skinny tables, contact Salesforce customer support.

For each object table, Salesforce maintains other, separate tables at the database level for standard and custom fields. This separation ordinarily necessitates a join when a query contains both kinds of fields. A skinny table contains both kinds of fields and does not include soft-deleted records.

This table shows an Account view, a corresponding database table, and a skinny table that would speed up Account queries.

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 SOSL.

Skinny tables can contain the following types of fields.
• Checkbox
• Date
• Date and time
• Email
• Number
• Percent
• Phone picklist
• Picklist (multi-select)
• Text (fewer than 80 characters)
• Text area
• Text area (long)
• URL

Here is an example of how a skinny table can speed up queries. Instead of using a date range like 01/01/11 to 12/31/11—which entails an expensive, repeated computation to create an annual or year-to-date report—you can use a skinny table to include a Year field and to filter on Year=’2011’.

Considerations
• Skinny tables can contain a maximum of 100 columns.
• Skinny tables cannot contain fields from other objects.
• Skinny tables are not copied to sandbox organizations. To have production skinny tables activated in a sandbox organization, contact Salesforce customer support.

SOQL Best Approaches

When Dealing with Large Data

Your SOQL query may return so many sObjects that the limit on heap size is exceeded and an error occurs. To resolve, use a SOQL query for loop instead, since it can process multiple batches of records through the use of internal calls to query and queryMore.

For example, if the results are too large, the syntax below causes a runtime exception:

Account[] accts = [SELECT Id FROM Account];

Instead, use a SOQL query for loop as in one of the following examples:

// Use this format if you are not executing DML statements 
                    
// within the for loop
                    
for (Account a : [SELECT Id, Name FROM Account 
                  WHERE Name LIKE 'Acme%']) {
    // Your code without DML statements here
}

// Use this format for efficiency if you are executing DML statements 
// within the for loop
for (List<Account> accts : [SELECT Id, Name FROM Account
                            WHERE Name LIKE 'Acme%']) {
    // Your code here
    update accts;
}

The following example demonstrates a SOQL query for loop used to mass update records. Suppose you want to change the last name of a contact across all records for contacts whose first and last names match a specified criteria:

public void massUpdate() {
    for (List<Contact> contacts:
      [SELECT FirstName, LastName FROM Contact]) {
        for(Contact c : contacts) {
            if (c.FirstName == 'Barbara' &&
              c.LastName == 'Gordon') {
                c.LastName = 'Wayne';
            }
        }
        update contacts;
    }
}

Instead of using a SOQL query in a for loop, the preferred method of mass updating records is to use batch apex, which minimizes the risk of hitting governor limits.

Efficient SOQL Queries

For best performance, SOQL queries must be selective, particularly for queries inside of triggers. To avoid long execution times, non-selective SOQL queries may be terminated by the system. Developers will receive an error message when a non-selective query in a trigger executes against an object that contains more than 100,000 records. To avoid this error, ensure that the query is selective.

Selective SOQL Query Criteria

• A query is selective when one of the query filters is on an indexed field and the query filter reduces the resulting number of rows below a system-defined threshold. The performance of the SOQL query improves when two or more filters used in the WHERE clause meet the mentioned conditions.
• The selectivity threshold is 10% of the records for the first million records and less than 5% of the records after the first million records, up to a maximum of 333,000 records. In some circumstances, for example with a query filter that is an indexed standard field, the threshold may be higher. Also, the selectivity threshold is subject to change.

Custom Index Considerations for Selective SOQL Queries

• The following fields are indexed by default: primary keys (Id, Name and Owner fields), foreign keys (lookup or master-detail relationship fields), audit dates (such as LastModifiedDate), and custom fields marked as External ID or Unique.
• Fields that aren’t indexed by default might later be automatically indexed if the Salesforce optimizer recognizes that an index will improve performance for frequently run queries.
• Salesforce.com Support can add custom indexes on request for customers.
• A custom index can't be created on these types of fields: multi-select picklists, currency fields in a multicurrency organization, long text fields, some formula fields, and binary fields (fields of type blob, file, or encrypted text.) Note that new data types, typically complex ones, may be added to Salesforce and fields of these types may not allow custom indexing.
• Typically, a custom index won't be used in these cases:
  • The value(s) queried for exceeds the system-defined threshold mentioned above
  • The filter operator is a negative operator such as NOT EQUAL TO (or !=), NOT CONTAINS, and NOT STARTS WITH
  • The CONTAINS operator is used in the filter and the number of rows to be scanned exceeds 333,000. This is because the CONTAINS operator requires a full scan of the index. Note that this threshold is subject to change.
  • When comparing with an empty value (Name != '')
  However, there are other complex scenarios in which custom indexes won't be used. Contact your salesforce.com representative if your scenario isn't covered by these cases or if you need further assistance with non-selective queries.

Examples of Selective SOQL Queries

To better understand whether a query on a large object is selective or not, let's analyze some queries. For these queries, we will assume there are more than 100,000 records (including soft-deleted records, that is, deleted records that are still in the Recycle Bin) for the Account sObject.

Query 1:

SELECT Id FROM Account WHERE Id IN (<list of account IDs>)

The WHERE clause is on an indexed field (Id). If SELECT COUNT() FROM Account WHERE Id IN (<list of account IDs>) returns fewer records than the selectivity threshold, the index on Id is used. This will typically be the case since the list of IDs only contains a small amount of records.

Query 2:

SELECT Id FROM Account WHERE Name != ''

Since Account is a large object even though Name is indexed (primary key), this filter returns most of the records, making the query non-selective.

Query 3:

SELECT Id FROM Account WHERE Name != '' AND CustomField__c = 'ValueA'

Here we have to see if each filter, when considered individually, is selective. As we saw in the previous example the first filter isn't selective. So let's focus on the second one. If the count of records returned by SELECT COUNT() FROM Account WHERE CustomField__c = 'ValueA' is lower than the selectivity threshold, and CustomField__c is indexed, the query is selective.

Salesforce Environment

Abstract

To get started with Force.com, all you need is a computer and an internet connection. What you don't need are application servers, web servers, databases, and the costly provisioning and configuration of these resources. Instead, the Force.com platform providesenvironments, also called organizations, which are simply provisioned in the cloud when you request them. An environment lets you instantly start developing and testing your cloud computing application, and you don't need to touch a single server. You save precious time and can start building your application immediately.

There are several types of environments available to you while developing and testing on Force.com. This article provides an outline of these environments. It will discuss the various editions, best practices and design considerations, and recommend particular environments during the application life-cycle. The goal is to arm you with the necessary environments for success.

What is an Environment?

In Force.com terminology, environments and organizations are synonyms. In other words, an instance of the Force.com platform is an environment and is also called an organization, which is frequently shortened to org. Throughout this article, environments and organizations will be used interchangeably.

A good way to think about an instance of Force.com, is to think about Gmail: when you sign up for Gmail, you get a unique username and access to your private email account in the cloud. Similarly, when you sign up for a Force.com org, you get a unique username and private access to your Force.com cloud computing environment. Here are some important characteristics of an environment:

• Can be used for development, testing and/or production
• Contains data (records) and customizations (Custom Database Objects & Fields, Apex Code, Visualforce, Workflow, etc.)
• Each environment is based on an edition, which contains specific functionality, objects, storage and limits.
• By default, environments are not provisioned with certain advanced features (such as multi-currency or developer preview technology). You can request enablement of advanced functionality by contacting salesforce.com Customer Support.
• All environments can be accessed through a Web browser, but some can also be accessed from the Force.com IDE, SOAP API, and the Metadata API.

So an environment is an instance of the Force.com infrastructure and platform that lets you access, deploy or create applications with various feature sets, depending on the configuration of the environment.

Types of Environments

Broadly speaking, there are three types of environments:

• Production Environments - Salesforce.com environments that have active paying users accessing business critical data
• Development Environments - Salesforce.com environments where you can extend, integrate and develop on Force.com without affecting your production environments
• Test Environments - These can be Production or Development Environments specifically used for testing application functionality before deploying to production or releasing to customers

As you spend more time with Force.com, you'll notice you can implement many features within the production environment. For instance, you can easily build a custom object, field, or validation rule directly in your production environment without the need of a development or test environment. However, some features, like Apex Code, can only be created in development environments. Salesforce always recommends that you use a development environment when building a Force.com app. You can then use Force.com IDE and/orPackaging to deploy to production or commercial customers.

Typical Use of Environments

Depending on your relationship with salesforce.com, you may need one or more of these types of environments. If you are a customer or prospect, you will use a production environment to run your business. If you are a developer or partner, you will most likely have multiple development and test environments to build functionality and thoroughly test. If you are a customer, developer and partner, then you'll have all of the above. Registered partners are eligible for additional environment options that are outlined below.

Environment Licenses and Upgrading

It is important to note that each environment has a license. This license is used to describe the type of environment. There are 4 production licenses: Group Edition, Professional Edition, Enterprise Edition and Unlimited Edition. Environment licenses can change, but they can only go up in edition, not down. So you can start with Group Edition, upgrade to Professional Edition, upgrade to Enterprise Edition and finally upgrade to Unlimited Edition. You cannot go the opposite way. So once you reach Enterprise Edition, you can only upgrade to Unlimited and not downgrade to Professional or Group Edition.

Depending on the license you purchase, Force.com Edition will either run on top of Enterprise or Unlimited Edition. Therefore, you can transition from the Enterprise version of Force.com Edition to Unlimited version, but not vice versa.

Developer Edition is a special development license that does not allow the conversion to production. The same is true for Sandbox. You cannot upgrade Sandbox, but you can purchase additional or different types of Sandbox if you have Enterprise, Unlimited or Force.com Edition. 

Please note: Upgrading a license is different than upgrading the platform. When we release a new version of the Force.com platform, all editions and licenses are upgraded to this new release automatically. For example, as you read this article, all environments are on a particular release. When the next release goes live, all environments (which includes the various editions and licenses) will automatically be upgraded to the new release. See the Release Information page for more information about current releases.

Production Environments

Production Force.com environments store the live data that is actively used to run your business. Most of the production environments in use today are Salesforce CRM customers who purchased Group Edition, Professional Edition, Enterprise Edition, or Unlimited Edition. If you do not require any of the Salesforce CRM functionality, you can sign up for Force.com Edition.

When developing an application to use internally, you typically know the features and limits of your production environment. However, as a partner looking to build a Force.com application with commercial intent, it's important to understand the different environments, thereby ensuring that the application you build will work in the desired environment. You can find a detailed feature comparison guide of the production Sales Cloud, Service Cloud and Force.com Edition environments on their respective pages.

You can develop a custom Force.com application for your own production use, for existing salesforce.com customers, or for any business or organization in the world. When building an application, it is recommended you use a development environment.

Development Environments and Best Practices

Development environments are used strictly for developing and testing apps. These environments contain test data that are not business critical. Development can be done inside your browser or with the Force.com IDE, which is based on Eclipse. There are two types of development environments: Developer Edition and Sandbox.

A Developer Edition environment is a free, fully-featured copy of the Enterprise Edition environment, with less storage and users. DE is a logically separate environment, ideal as your initial development environment. You can sign-up for as many DE organizations as you need. This allows you to build an application designed for any of the Salesforce production environments.

Sandbox is a nearly identical copy of your production environment available to Enterprise or Unlimited Edition customers. The sandbox copy can include data, configurations, or both. It is possible to create multiple sandboxes in your production environments for a variety of purposes without compromising the data and applications in your production environment.

Note: Since Force.com Edition runs on top of Enterprise or Unlimited Edition, you can potentially have the same number of Sandboxes listed below.

The multiple variations of these two environments are outlined in the table below:

Development Environment	User Licenses	Data Storage	API Enabled*	Notes
Developer Edition	2 full CRM licenses 3 Force.com Platform licenses and more...	20 MB (about 10,000 records)	Yes	Sign-up is Free!
Partner Developer Edition¹	20 full CRM licenses 20 Force.com Platform licenses and more...	250 MB (about 125,000 records)	Yes	This is a DE org with more storage, features and licenses. Free for enrolled partners.
Full-copy Sandbox²	Same as production environment		Yes	This is a nearly identical copy of your production org including data and customization. You may order up to a maximum of 3 full sandboxes. Unlimited Edition includes 1 full sandbox. Enterprise Edition customers may purchase a full-copy sandbox for an additional fee.
Configuration Only Sandbox²	Same as production environment	500 MB (about 250,000 records)	Yes	This only copies the customization, it does not copy production data. You may order up to a maximum of 6 configuration-only sandboxes. Unlimited Edition includes 5 configuration-only sandboxes. Enterprise Edition customers may purchase a configuration-only sandbox for an additional fee.
Developer Sandbox²	Same as production environment	10 MB (about 5,000 records)	Yes	This only copies the customization, it does not copy production data. Unlimited Edition includes 10 developer sandboxes. Enterprise Edition includes 1 developer sandbox.

* This includes both the Force.com Web Services and Metadata API - limits can be found here
¹ Available for registered Force.com ISV and Consulting Partners
² You can refresh each sandbox 29 days from its previous refresh or creation

Developer Edition is ideal if:

• You are a partner who intends to build a commercially available Force.com app by creating a managed package for distribution through AppExchange and/or Trialforce. - NOTE: Only Developer Edition or Partner Developer Edition environments can create managed packages.
• You are a salesforce.com customer with a Professional, Group, or Personal Edition, and you do not have access to Sandbox.
• You are a developer looking to explore the Force.com platform for FREE!

Partner Developer Edition is ideal if:

• You are developing in a team and you require a master environment to manage all the source code - in this case each developer would have a Developer Edition environment and check their code in and out of this master repository environment.
• You expect more than 2 developers to log in to develop and test.
• You require a larger environment that allows more users to run robust tests against larger data sets.

Sandbox is ideal if:

• You are a salesforce.com customer with Enterprise, Unlimited, or Force.com Edition, which includes Sandbox.
• You are developing a Force.com application specifically for your production environment.
• You are not planning to build a Force.com application that will be distributed commercially.
• You have no intent to list on the AppExchange or distribute through Trialforce.

Best Practices and Development Considerations

---

• Always develop in a development environment (like Developer Edition (DE) or Sandbox) and deploy to a production environment.
• Determine early on if you need a larger development environment, as DE cannot be upgraded to Partner DE.
• If you plan to build an app for existing Salesforce CRM customers, review the supported features of each edition, so you build your application accordingly. In other words, do not assume that if a feature is available in your Developer Edition environment, that it is also available to customers who may be using Group or Professional Edition.
• Always test your application before deployment (review the next section for more information).
• Keep your development and testing environments separate.

Testing Environments and Best Practices

Before you deploy your app to production or release it to customers, migrate your app to a dedicated testing environment where you can perform integration tests with large sets of data, do security checks for multiple users and profiles, and uncover bugs or discover enhancements you'd like to add. Automated test scripts can't determine a user's level of satisfaction with your app, so you'll want to train several new users to evaluate your application in a real-world setting without affecting your business data. In essence, you'll want to create another development environment that mimics your production environment, and have beta testers use it.

You have multiple options for creating a dedicated testing environment. One is to create a sandbox copy of your production environment and deploy directly from your development environment to your sandbox. In this way, you'll test not only the finished application, but the procedure used to deploy the completed application as well.

If you don't have a sandbox, you can use a DE environment as your testing environment. The standard DE environment may be limiting because of license and storage limits. However the table below outlines larger test environments available to partners, customers, and developers:

Testing Environment	User Licenses	Data Storage	API Enabled*	Notes
Partner Test Edition(Enterprise/Platform Edition)¹	25 full CRM licenses 20 Force.com Platform licenses and more...	1 GB (about 1,000,000 records)	Yes	This is Enterprise Edition with more storage, features, licenses and 3 Sandbox orgs
Partner Test Edition(Professional Edition) ¹	10 PE licenses and more...	1 GB (about 1,000,000 records)	Yes	This is Professional Edition with more storage, features, licenses and the ability to install Beta Managed Packages
Sandbox²	See Development Environments for details

* This includes both the Force.com Web Services and Metadata API - limits can be found here
¹ Available for registered Force.com ISV and Consulting Partners
² You can refresh each sandbox 29 days from its previous refresh or creation

Partner Test Edition (Enterprise/Platform Edition) is ideal if:

• You are a partner looking for a production-like environment with more users and storage to run real-life tests.
• You are a partner developing a managed package to release commercially and you need a sandbox to test your beta managed package.
• You want to be sure your app will run smoothly in Enterprise and Force.com Editions.

Partner Test Edition (Professional Edition) is ideal if:

• You are a partner looking for a production-like environment with more users and storage to run real-life tests.
• You are a partner developing a managed package to release commercially and you need to test your beta managed package against Professional Edition. NOTE: This special Professional Edition test environment will allow the install of beta managed package.
• You want to be sure your app will run smoothly in Professional Edition.

Sandbox is ideal if:

• You want to test against a copy of your production environment (including production customization and data).
• You have an Enterprise, Unlimited, or Force.com Edition environment with a sandbox.
• You want to test a beta managed package.

Best Practices and Testing Considerations

---

• Partners can take advantage of additional test environments. Visit Partner Development & Test Environments for more details.
• If you are developing a Force.com app that leverages Apex Code, you will need to ensure that you have included test methods with greater than 75% code coverage to deploy. For more help, check out An Introduction to Apex Code Test Methods.
• Always test your app before deployment.
• Always test your app as a beta-managed package before releasing. For more help, check out An Introduction to Packaging .

Scenarios

Now that you have a better understanding of environments and what's available for you to leverage, let's take a look at a few scenarios that outline what a customer, developer, and partner may experience.

Scenario 1: You are a Developer looking to get started for free building a Force.com application

• Sign up for a free Developer Edition environment here.
• Begin building on Force.com and using all of the platform features.
• Sign up for another free Developer Edition to test your application.
• Use the Force.com IDE to develop and deploy your application from development to test to production.

Scenario 2: You are a Customer who wants to build a new Force.com application for your production environment

• Get a free Developer Edition organization or set up a sandbox (depending on your production environment).
• Start developing your Force.com application in the development environment you just set up.
• Use another Developer Edition or sandbox environment to test your application's functionality.
• Use the Force.com IDE to develop and deploy your application to production.

Scenario 3: You are a Partner who wants to build a Force.com application to sell

• Sign up for a free Developer Edition or Partner DE organization.
• Use the Force.com IDE to develop your application.
• Package your app and upload it as a beta managed package.
• Test your beta managed package in sandbox, DE and/or the Partner Test environments.
• Upload the package as a released managed package and deploy it to your customers.

Scenario 4: You are a Partner who wants to build and sell a composite app that integrates with Force.com

• Sign up for a free Developer Edition or Partner DE organization. Both environments include access to the various Force.com APIsthat can enable you to build your integration.
• Use the Force.com IDE to develop your application.
• Package your app and upload it as a beta managed package.
• Test your beta managed package in sandbox, DE and/or the Partner Test environments.
• Upload the package as a released managed package and deploy it to your customers.

Summary

This article provides an overview of the various Force.com environments. The Force.com platform provides a variety of robust environments for developing and testing. Whether you are a developer looking to build a Force.com app for your production environment or a partner looking to commercialize your next SaaS app on Force.com, there are many environments you can leverage throughout the app life-cycle. 

Nulls and Formula fields : SOQL Consideration

Until the Winter ‘13 release, best practices for building Salesforce SOQL queries on large data volumes (LDV) included avoiding filtering on fields with nulls, and similarly avoiding filtering on formula fields. If you plan to implement new queries—or want to clean up some of the workarounds you implemented before the Winter ’13 release—consider the following updates related to filtering on nulls and formula fields, which could help tune your SOQL performance.

Custom Indexes for Deterministic Formula Fields

Formula fields are custom fields that can help you dynamically calculate the value of a field based on other fields, expressions, or values.

As you can with any other field, you can use formula fields to filter SOQL queries. For example, you might write a query such as:

1	SELECT Id, Name FROM Contact WHERE FormulaField__c = '10';

Just because you can filter queries using a formula field doesn’t mean that you should, however. By default, formula fields don’t have underlying indexes, so they require full scans to find target records. They also have to calculate values on the fly because actual values are not stored in the database. So when an object has a lot of records, queries that filter using a formula field can be painfully slow.

To get around the pre-Summer ’12 index limitations involving formula fields, many customers created workarounds. For example, you might have created triggers or workflow rules to store formula field values in a separate custom field that you could index, and then built queries that filtered using the custom field instead of the formula field. That strategy works, but it requires overhead and is not intuitive to developers.

Since the Winter ’13 release, you have been able to contact salesforce.com Customer Support to create a custom index on a formula field, provided that the function that defines the formula field is deterministic.

Here are examples of common non-deterministic formulas. Force.com cannot index fields that:

• Reference other entities (i.e., fields accessible through lookup fields)
• Include other formula fields that span over other entities
• Use dynamic date and time functions (e.g., TODAY, NOW)

A formula is also considered non-deterministic when it includes:

• Owner, autonumber, divisions, or audit fields (except for CreatedDate and CreatedByID fields)
• References to fields that Force.com cannot index
• Multi-select picklists
• Currency fields in a multicurrency organization
• Long text area fields
• Binary fields (blob, file, or encrypted text)
• Standard fields with special functionalities
• Opportunity: Amount, TotalOpportunityQuantity, ExpectedRevenue, IsClosed, IsWon
• Case: ClosedDate, IsClosed
• Product: Product Family, IsActive, IsArchived
• Solution: Status
• Lead: Status
• Activity: Subject, TaskStatus, TaskPriority

Because values might vary over time or change when a transaction updates a related entity, Force.com cannot index non-deterministic formulas.

Remember that indexing a deterministic formula field does not alone guarantee that the Force.com platform will always use it; general selectivity rules still apply. To ask for an indexing evaluation for building new custom indexes on your deterministic formula fields, contact salesforce.com Customer Support.

Custom Indexes Containing null Rows

Customers often need to query an object and find only the records in which a certain field is empty (i.e., null). For example:

1	SELECT Id, Name FROM Contact WHERE CustomField__c = null;

That sounds simple enough, but when your query targets an object that has a lot of records, consider the performance implications. By default, underlying Force.com field indexes don’t include nulls. Therefore, queries such as the examples in this post require full scans, which again can be painfully slow.

Since the Winter ’13 release, you have been able to work with salesforce.com Customer Support to create custom indexes that include null rows. Even if you already have custom indexes on your custom fields, they need to be explicitly enabled and rebuilt to get the empty-value rows into index tables.

Note that this option does not apply to picklists, external IDs, and foreign key fields. If you need to query on a null external ID field, you can work with salesforce.com Customer Support to create a two-column (compound) index instead.

Again, creating a custom index with null records does not alone guarantee that the platform will always make effective use of it, and general selectivity rules still apply. 

To Summarize

When building your SOQL queries, consider the following new filtering options: filtering on deterministic formula fields and filtering on nulls. While not all types of formula fields or fields with nulls can benefit from these new options, they’re definitely worth exploring if you want to simplify your implementation and tune your SOQL performance.

Remember that to set up custom indexes and enable the null indexing option, you must contact salesforce.com Customer Support.