Salesforce indexing

Salesforce indexing DEFAULT

In this session we talk about how to Optimize Salesforce query for large data volume Salesforce. We also cover the Skinny Table, Indexing Table and Optimize the SOQL in Salesforce.

Agenda

  • Multi Tenant Architecture
  • Skinny Table
  • Indexing Table & Index Statistics
  • Upper Limit on
  • Standard & Custom Indexed Field
  • Limit on AND , OR and LIKE operator
  • SystemModstamp vs LastModifiedDate
  • Query Plan Tool
  • Q&A

Multi Tenant Architecture

Why Traditional Database optimization Technique would not work for Salesforce?

Lightning Platform Query Optimizer friendly Data Model

  • No need to perform joins between Standard and Custom Object
  • Don’t consider record in database
  • There would be actual table containing data only for your Org
  • Throughput would be increased
    • In same time Salesforce Query Engine can return more record, as it has to scan less records
  • Get a solution for Object bloat (Just in Case Field)

That’s what it is – Skinny Table 

Considerations & Limitations – Skinny Table

  • Contact Salesforce to enable it
  • Can contain only 100 fields
  • Change in field type used in Skinny table would make it invalid
  • Skinny Table is not copied to sandbox after refresh (Except Full Copy Sandbox)
  • Useful for read operations
  • Cannot contain fields from other object / Parent

Index Table

  • Traditional Indexing would not work for Salesforce
  • Maintains different table about data and its type – Index Table
  • Standard Database Index on Index Table
  • Contains Upper limit about how much record can be returned by Search
  • Lightning Query optimizer maintains statistics about Index Table
  • Statistics gathering process runs nightly
  • Few fields are already Indexed (Standard Index)
  • We can ask Salesforce to create Custom Index
  • Custom Index also created on External Id fields

Index Table – Upper Limits

Standard Index Field

Use Indexing only if – Max 1 Million ●Filter matches less than or equal to 30% of first Million record ●And less than or equal to 15% of additional records

Custom Index Field

Use Indexing only if ●Filter matches less than or equal to 10% total records upto max 333,333

Recording

If you are new in Salesforce. Please check our free Salesforce Admin and Salesforce Developer training. Subscribe to the channel if you haven’t already

Amit Chaudhary

Amit Chaudhary

Amit Chaudhary is Salesforce Application & System Architect and working on Salesforce Platform since 2010. He is Salesforce MVP since 2017 and have 17 Salesforce Certificates.

He is a active blogger and founder of Apex Hours.

Index Statistics, Indexing Tavke, large data volume salesforce, LDV, Query Plan Tool, Skinny Table

Sours: https://www.apexhours.com/how-salesforce-query-optimizer-works-for-ldv/

Index Tables

Index tables
The Salesforce multitenant architecture makes the underlying data table for custom fields unsuitable for indexing. To overcome this limitation, the platform creates an index table that contains a copy of the data, along with information about the data types.
By default, the index tables do not include records that are null (i.e., have empty values). You can work with Salesforce Customer Support to create custom indexes that include null rows. Even if you already have custom indexes on your custom fields, you must explicitly enable and rebuild them to get the empty-value rows indexed.
Standard and Custom Indexed Fields
The Force.com query optimizer maintains a table containing statistics about the distribution of data in each index. It uses this table to perform pre-queries to determine whether using the index can speed up the query.
For example, assume that the Account object has a field called Account_Type—which can take the value Large, Medium, or Small—and that the field has a custom index. 
Assume that Salesforce generates a query like: 
 SELECT * FROM Account WHERE Account_Type__c = 'Large'
 The Force.com query optimizer performs a pre-query to its internal statistics table to determine the number of records with Large in the Account_Type field. If this number exceeds 10% of the object’s total records or 333,333 records, the query does not use the custom index. The Force.com query optimizer determines if an index is used with: 
Standard Indexed Fields 
Used if the filter matches less than 30% of the total records, up to one million records. 
 • A query is executed against a table with two million records, and the filter matches 600,000 or fewer records.
 • A query is executed against a table with five million records, and the filter matches one million or fewer records
Used if the filter matches less than 10% of the total records, up to 333,333 records.
  • A query is executed against a table with 500,000 records, and the filter matches 50,000 or fewer records.
  • A query is executed against a table with five million records, and the filter matches 333,333 or fewer records
If the criteria for an indexed field are not met, only that index is excluded from the query—other indexes might still be used if they are in the WHERE clause and meet the thresholds for records. 
The Force.com query optimizer uses similar considerations to determine whether to use indexes when the WHERE clause contains AND, OR, or LIKE. 
  •  For AND, the Force.com query optimizer uses the indexes unless one of them will return more than 20% of the object’s records or 666,666 total records. 
  •  For OR, the Force.com query optimizer uses the indexes unless all of them will return more than 10% of the object’s records or 333,333 total records. 
    • Note: All fields in the OR clause must be indexed in order for any index to be used. 
  •  For LIKE, the Force.com query optimizer does not use its internal statistics table. Instead, it samples up to 100,000 records of actual data to decide whether to use the custom index.
Two-Column Custom Indexes
Two-column custom indexes are a specialized feature of the Salesforce platform. They are useful for list views and other situations in which you want to use one field to select the records to display and a second field to sort those records. For example, an Account list view that selects by State and sorts by City can use a two-column index with State in the first column and City in the second.
When a combination of two fields is a common filter in the query string, two-column indexes might help you sort and display records. For example, for the following SOQL, which appears in psuedo code, a two-column index on f1__c,f2__c is generally more efficient than single indexes on f1__c and f2__c.
SELECT Name FROM Account WHERE f1__c = 'foo' AND f2__c = 'bar'
Note: Two-column indexes are subject to the same restrictions as single-column indexes, with one exception. Two-column indexes can have nulls in the second column by default, whereas single-column indexes cannot, unless Salesforce Customer Support has explicitly enabled the option to include nulls. 
Sours: https://sites.google.com/site/sekarstacert/home/2-platform-architecture-concepts/large-data-voloume-ldv/index-tables
  1. Total wine district manager salary
  2. Blc fort bragg
  3. Sea moss trinidad
  4. Summit pump distributor
  5. Ruger p89 dao

I was recently doing some consulting for a company that works with Salesforce managed packages.  They’d come across a problem that had turned into one heckuva knot they were having trouble unraveling.  Turns out the issue had to do with some obscure details about Salesforce indexing of fields on its standard objects.  These details were only discovered via many emails and conversations with Salesforce support.

When Salesforce developers and admins write queries that are so broad that they bring back “too much” data, they often get an error message like this:

The following exception was encountered:  System.QueryException: Non-selective query against large object type (more than 100000 rows). Consider an indexed filter or contact salesforce.com about custom indexing.

The workaround is to try and create tighter queries by using a standard Salesforce field that has been indexed.  But even if a field is indexed, a filter might still not be selective when the filter value includes null (for instance binding with a list that contains null), or data skew exists whereby the number of matching rows is very large (for instance, filtering for a particular foreign key value that occurs many times).

If at all possible you want to construct your queries using the standard Salesforce indexed fields or work with Salesforce to index a column that you are going to use frequently in your lookups.

The standard Salesforce indexed fields are:

  •  Lead: Company, Email, Lead Owner, Name.
  • Contact: Account Name, Contact Owner, Email, Name, Reports To.
  •  Account: Account Name, Account Owner, Account Record Type, Parent Account,
  •  On top of these, system time fields (last modified, created date etc) and any lookup fields are also indexed.

 

What happens if I need more indexed fields in Salesforce?

By default your primary keys, forgeign keys, custom fields marked as External Ids/Unique along with the system audit dates (like last modified) are indexed. If you have a specific field that you need indexed, you can reach out to Salesforce support. Keep in mind though that there is a limit to how many you can add and they are going to weigh the pros and cons with you to see if this is going to help your situation.

Certain fields cannot be used as indexes. Examples include: multi-option picklists, currency fields if the org has multi-currency enabled, certain formula fields, binary fields (like a blob or file). In general any complex field type is not a candidate.

 

Updateon Tuesday, June 16, 2015 at 3:03PM by Registered Commentersfgeneral

Salesforce has a great resource on their engineering site related to indexed fields. I highly recommend it:

Know Thy Salesforce Field Indexes for Fast Reports, List Views, and SOQL

Sours: http://www.salesforcegeneral.com/salesforce-articles/standard-indexed-fields.html
30. SOQL Performance Series Part 2 - How Indexing in Salesforce Works

What standard and custom fields are indexed?

The platform automatically maintains indexes on the following fields for most objects.

  • RecordTypeId
  • Division
  • CreatedDate
  • Systemmodstamp (LastModifiedDate)
  • Name
  • Email (for contacts and leads)
  • Foreign key relationships (lookups and master-detail)
  • The unique Salesforce record ID, which is the primary key for each object

Salesforce also supports custom indexes on custom fields, with the exception of multi-select picklists, text area (long), text area (rich), non-deterministic formula fields, and encrypted text fields.

External IDs cause an index to be created on that field, which is then considered by the Force.com query optimizer.

External IDs can be created on only the following fields.

  • Auto Number
  • Email
  • Number
  • Text

To create custom indexes for other field types, including standard fields, contact salesforce.com Customer Support

Sours: https://salesforce.stackexchange.com/questions/218/what-standard-and-custom-fields-are-indexed

Indexing salesforce

Improving SOQL Query performance with Indexed fields

Created by: Nivedita Rajendiran

Modified on: Wed, Aug 10, 2016 at 2:48 PM

Salesforce Object Query Language (SOQL) queries can be very practical if written efficiently. However, some queries can have extremely long execution times, especially when run on non-indexed fields. These are called non-selective queries. They are typically terminated by the system to to avoid the lengthy execution times. A query that must check an object with over 100,000 records will return an error message. In order to get the best performance times and avoid such error messages, selective queries should be used.

A selective query has at least one query filter that is on an indexed field and reduces the number of rows returned below the system threshold. When a field is indexed, its values are stored in a more efficient data structure. This takes up more space but improves performance when at least two filters with indexed fields are used in a query.

Fields that are indexed by default include:

  • Primary keys: Id, Name, Owner, Email (contacts, leads)
  • Foreign keys: lookup or master-detail relationships
  • Audit dates: SystemModStamp, CreatedDate
  • Custom fields: External ID (Auto Number, Email, Number, Text), Unique

If you would like to have another field indexed, you can use a custom index. Custom indexes are supported on standard and custom fields [excluding multi-select picklists, currency fields in a multicurrency organization, text area (long), text area (rich), non-deterministic formula fields, and binary fields (blob, file, encrypted text)].

It is also important to note that index tables do not include null records by default, even on custom indexes. This feature must be explicitly requested and enabled when working with Salesforce Support.

Custom indexes can be requested on both standard and custom fields by creating a case with Salesforce Support. You will need to provide your SOQL query that contains the field to be indexed in the WHERE clause and any bind values. See Checklist for SOQL Custom Index Requests for a full list of information to include in your request.


For additional help, see Salesforce help/documentation:

Nivedita is the author of this solution article.

Did you find it helpful? Yes No

Send feedback

Sorry we couldn't be helpful. Help us improve this article with your feedback.

Still can't find your solution?

Visit our forums to search for answers, or post your own questions.

Sours: https://support.workato.com/en/support/solutions/articles/1000236530-improving-soql-query-performance-with-indexed-fields
Salesforce For Beginners - 1. Introduction To Salesforce - Salesforce CRM Developement Tutorials

Salesforce supports custom indexes to speed up queries, and you can create custom indexes by contacting Salesforce Customer Support.

Note: The custom indexes that Salesforce Customer Support creates in your production environment are copied to all sandboxes that you create from that production environment.

The platform maintains indexes on the following fields for most objects.
• RecordTypeId
• Division
• CreatedDate
• Systemmodstamp (LastModifiedDate)
• Name
• Email (for contacts and leads)
• Foreign key relationships (lookups and master-detail)
• The unique Salesforce record ID, which is the primary key for each object

Salesforce also supports custom indexes on custom fields, except for multi-select picklists, text areas (long), text areas (rich), non-deterministic formula fields, and encrypted text fields.

External IDs cause an index to be created on that field, which is then considered by the Force.com query optimizer.

You can create External IDs only on the following fields.
• Auto Number
• Email
• Number
• Text

To create custom indexes for other field types, including standard fields, contact Salesforce Customer Support.

By default, the index tables do not include records that are null (records with empty values). You can work with Salesforce Customer Support to create custom indexes that include null rows. Even if you already have custom indexes on your custom fields, you must explicitly enable and rebuild them to get the empty-value rows indexed.

Sours: https://www.infallibletechie.com/2017/06/indexing-in-salesforce.html

You will also be interested:

Configuring and Indexing a Salesforce Source for the Legacy Connector

Index Subfolders

Keep this check box selected (recommended). By doing so, all subfolders from the specified starting address are indexed.

Index the document's metadata

When selected, CES indexes all the document metadata, even metadata that are not associated with a field. The orphan metadata are added to the body of the document so that they can be searched using free text queries.

When cleared (default), only the values of system and custom fields that have the Free Text Queries attribute selected will be searchable without using a field query (see Adding a Field to Search On and What Are Field Queries and Free Text Queries?).

Example: A document has two metadata:

  • #LastEditedBy containing the value Hector Smith

  • Department containing the value RH

In CES, the custom field CorpDepartment is bound to the metadata Department and its Free Text Queries attribute is selected.

When the Index the document's metadata option is cleared, searching for RH returns the document because a field is indexing this value. Searching for hector does not return the document because no field is indexing this value.

When the Index the document's metadata option is selected, searching for hector also returns the document because CES indexed orphan metadata.

Document's addresses are case-sensitive

Leave the check box cleared. This parameter needs to be checked only in rare cases for systems in which distinct documents may have the same name but different casing.

Generate a cached HTML version of indexed documents

When you select this check box (recommended), at indexing time, CES creates HTML versions of indexed documents. In the search interfaces, users can then more rapidly review the content by clicking the Quick View link rather than opening the original document with the original application. Consider clearing this check box only when you do not want to use Quick View links or to save resources when building the source.

Open results with cached version

Leave this check box cleared (recommended) so that in the search interfaces, the main search result link opens the original document with the original application. Consider selecting this check box only when you do not want users to be able to open the original document but only see the HTML version of the document as a Quick View. In this case, you must also select Generate a cached HTML version of indexed documents.

Sours: https://onlinehelp.coveo.com/en/ces/7.0/administrator/configuring_and_indexing_a_salesforce_source_for_the_legacy_connector.htm


1243 1244 1245 1246 1247