This article comprises the FAQs related to salesforce internal platform Query Optimizer.and provides valuable information that will help understand how to improve the performance of SOQL queries. For more information, you may also check this Dreamforce Session.
1. Query Optimizer
Q: Does Salesforce have its own SQL optimization logic instead of Oracle's built-in?
A: Force.com is a multi-tenant platform. To consider multitenant statistics, the platform has its own SOQL query optimizer that determines optimal SQL to satisfy a specific tenant's request.
Q: How frequently generated are the statistics used by the query optimizer?
A: Statistics are gathered weekly. Where statistics are not calculated, the optimizer also performs dynamic pre-queries, whose results are cached for 1 hour
Q: Is there any way to flush the cache when doing your performance testing so your results are not cached biased?
A: Unfortunately not. Queries with selective filters will perform more consistently with less performance variation due to caching, however.
2. Archiving
Q: Does salesforce.com provide any archiving options?
A: The best option for archiving large amounts of org data is the Force.com Bulk API. Many tools provide nice UIs for this API, including many third-party tools in the AppExchange.
Q: What archiving strategies should be used?
A: Archiving strategies are often time-based. For example, archive all Opportunities closed for more than six months. You could use the Force.com Bulk API to query and copy this data to an external system, then use the Bulk API to delete the records you just archived.
3. Tools
Q: Will there be a different tool than the Developer Console is available in the next releases to make query-testing easier? If so, will it be able to advise on how to improve the performance of the query?
A: (Safe Harbor) we are considering enhancements that will help you profile the performance of queries, but keep in mind that providing suggestions on how to improve the performance is a very hard problem in computer science. I.e. showing that a query performs poorly is easy, but rewriting is hard.
4. SOQL
Q: Can SFDC change to use ANSI standard SQL?
A: Force.com's query language, SOQL, is not ANSI standard SQL. It is, however, similar in many ways and should be easy to learn for most SQL-literate developers. If you really must use SQL with Force.com, consider third-party data access drivers (ODBC and JDBC), such as those from Progress Data Direct (http://www.datadirect.com/products/odbc/salesforce_crm_odbc_driver/index.html).
Q: Why is salesforce.com biased on threshold? I know there is some overhead, but most of the time customers are interested in custom indexes and not standard indexes.
A: The difference in thresholds between standard and custom indexes is driven by the database architecture and our multi-tenancy model.
Q: What will happen if our query hits the threshold?
A: When the optimizer determines that a given filter condition exceeds the corresponding selectivity threshold for an index, the optimizer does not use the index because the overhead of using the index is not optimal.
Q: Will an index be used if there are null values in indexed field?
A: If a filter condition specifically searches for nulls and the index supports nulls, then the optimizer will consider the index -- it can use the index if the number of nulls in the index is below the selectivity threshold. Starting with winter '13, you have been able to create custom indexes that include null rows by working with salesforce.com Customer Support. All standard indexes automatically include nulls.
Q: What happens with record visibility when we have the class without sharing? Would it search the complete database?
A: The optimizer considers record visibility when determining a query execution plan. If sharing rules do not limit the visibility of data for the user executing a query, then the optimizer can only consider other approaches for record selectivity (indexes, skinny tables, etc.).
Q: Do System Administrator queries get handled quicker than other profiles?
A: Profiles which have view all rights don't go through sharing evaluation and SQLs run will be different compared to profiles with limited view of data. Depending on selectivity we drive from Sharing or selective filters, whichever is most efficient.
Q: Is there a limit to how many values you can have inside in IN() clause?
A: There is no limit on the number of values inside the IN clause. The only limit would be that you cannot exceed the max size of SOQL query (10,000 characters in Spring ‘13 and 20,000 characters in Summer ‘13).
Q: Do cross object formula fields have performance implications?
A: Several impact possibilities: We do not support indexes for formulae as detailed by the following Wiki article:http://blogs.developerforce.com/engineering/2013/02/force-com-soql-best-practices-nulls-and-formula-fields.html
Formulae are also expanded into more elaborate SQL and, based on their complexity, can throw a SQL query limit exception. They are generally slower to filter by when not indexed.
Q: If we need to display 1M rows by setting a Visualforce page as read only, could indexes play any part in such a scenario?
A: Within the controller / extension class, yes: any SOQL that retrieves records to be displayed is subject to the issues discussed in this article. Performance for those records to be rendered on the page is based on your Visualforce markup.
Q: Is there any tool to check the SOQL Query in UI?
A: Yes, we can check the SOQL query in Query Plan Tool.
https://help.salesforce.com/articleView?id=000199003&type=1
5. SOSL
Q: What's the meaning of SOSL?
A: SOSL stands for Salesforce Object Search Language. It is the search language that you use to search for text across one or more objects. The language closely resembles the search language you use with Apache Lucene. Please see the following link for more information:http://www.salesforce.com/us/developer/docs/soql_sosl/index_Left.htm#StartTopic=Content/sforce_api_calls_sosl.htm
Q: Should we prefer SOQL over SOSL?
A: SOQL is Force.com's database query language, similar to SQL. SOSL is Force.com's full-text search language. If you need to retrieve less than 200 records and you are searching over text fields by using an OR clause, SOSL should be used instead. Please notice in summer ‘13 SOSL will retrieve up to 2000 records. Read more about each language.
Q: When should we decide if SOSL is going to perform better than SOQL?
A: Generally speaking, SOSL is optimal in these scenarios:
- Searching, across multiple objects, text-oriented fields that are not indexed
- When you need "fuzzy" text matching
- When support of Chinese, Japanese, and Korean (CJK) languages is required.
- Indexed/Indexable fields
Q: What is the requirement to exercise the benefit of standard index? What fields are part of standard index?
A: Standard Fields that are indexed: Id, Name, OwnerId, CreatedDate, SystemModstamp are indexed for both standard and custom objects
RecordType (indexed for all standard objects that feature it).
Master-detail fields (for standard fields only).
Lookup fields (for standard fields only) .
If you use such fields in a filter condition, the optimizer considers using the index.
Q: When will custom indexes be considered by Salesforce?
A: Presence of indexes is checked by our query optimizer when the query is run. Custom indexing a field will improve performance only when the filter that uses this field is selective. If you need assistance with a particular query, please raise a case with Salesforce Support.
Q: Is there a way to know what fields are indexes?
A: Please check the "Database Query & Search Optimization Cheat Sheet" that you will find at http://resources.docs.salesforce.com/194/0/en-us/sfdc/pdf/salesforce_query_search_optimization_developer_cheatsheet.pdf.
At present the setup UI does not display custom indexes, but we are working to enhance the user interface in the near future to display which fields have an index (Safe Harbor).
Q: Is External Id a standard or custom index?
A: Fields marked as External Id are always custom indexed.
Q: Are all fields marked as unique indexed automatically?
A: That's right, Unique and ExternalId and foreign keys are automatically indexed whenever possible.
Q: Are there any plans to make datetime field "indexable"?
A: With the help of Salesforce Support, you can request an index for Date/Time fields.
Q: Are the contact emails and phone fields indexed or only if they are requested?
A: Contact Email field is standard indexed, but phone field is not indexed by default.
Q: Is there any limit on number of custom indexes?
A: Yes, the platform supports a limited number of indexes per object. Generally speaking though, it is best practice to put in place custom indexes only when it will help performance of queries, but not so many that it detracts from the performance of DML operations (INSERT, UPDATE, DELETE) and bulk data loads.
Q: Can custom indexes be packaged?
A: Custom indexes associated to fields marked as External Id will be packaged.
Q: Will the index roll over to customers installing the app? Let’s say I built an appexchange app and ask Customer Support to create an index. Will the indexes hold true in my customer’s org?
A: Please refer to http://blogs.developerforce.com/engineering/2013/02/force-com-batch-apex-and-large-data-volumes.html
Awesome Content
ReplyDelete