Optimize SOQL/Reports/List Views

sfdoncestop youtube over 70 videos - https://www.youtube.com/c/sfdconestop

Maximizing the Performance of Force.com SOQL, Reports, and List Views

If you have sales representatives closing opportunities, support representatives working through a list of cases, or even managers running reports, you’ll want to optimize query performance in your Force.com applications. In saleforce.com’s multitenant environment, the Force.com query optimizer does its own kind of optimization, generating the most efficient SQL from your SOQL, reports, and list views. This blog post explains the filter conditions and the Force.com query optimizer thresholds that determine the selectivity of your queries and affect your overall query performance.


If data is king, timely access is queen. If you have sales representatives closing opportunities, support representatives working through a list of cases, or even managers running reports, you’ll want to optimize query performance in your Force.com applications. In saleforce.com’s multitenant environment, the Force.com query optimizer does its own kind of optimization, generating the most efficient SQL from your SOQL, reports, and list views. This blog post explains the filter conditions and the Force.com query optimizer thresholds that determine the selectivity of your queries and affect your overall query performance.
The Force.com Query Optimizer


The Force.com query optimizer is an engine that sits between your SOQL, reports, and list views and the database itself. Because of salesforce.com’s multitenancy, the optimizer gathers its own statistics instead of relying on the underlying database statistics. Using both these statistics and pre-queries, the optimizer generates the most optimized SQL to fetch your data. It looks at each filter in your WHERE clause to determine which index, if any, should drive your query.


To determine if an index should be used to drive a query, the Force.com query optimizer checks the number of records targeted by the filter against selectivity thresholds. For a standard index, the threshold is 30 percent of the first million targeted records and 15 percent of all records after that first million. In addition, the selectivity threshold for a standard index maxes out at 1 million total targeted records, which you could reach only if you had more than 5.6 million total records.


So if you had 2.5 million accounts, and your SOQL contained a filter on a standard index, that index would drive your query if the filter targeted fewer than 525,000 accounts.


In these standard index and custom index examples, the Force.com query optimizer does use the standard and custom indexes, as each number of targeted records falls below the appropriate selectivity threshold. If, on the other hand, the number of targeted records exceeds an index’s selectivity threshold, the Force.com query optimizer does not use that index to drive the query.


The Inside the Force.com Query Optimizer webinar explains in more detail how you can create selective queries for the Force.com query optimizer.
Common Causes of Non-Selective SOQL Queries


There are several factors that can prevent your SOQL queries from being selective.
Having Too Much Data


Whether you’re displaying a list of records through a Visualforce page or through a list view, it’s important to consider the user experience. Pagination can help, but will your users really go through a list with thousands of records? You might not have this much data in your current implementation, but if you don’t have enough selective filters, these long lists can easily become an issue as your data grows. Design your SOQL, reports, and list views with large data volumes in mind.
Performing Large Data Loads


Large data loads and deletions can affect query performance. The Force.com query optimizer uses the total number of records as part of the calculation for its selectivity threshold.


This number takes into account your recently deleted records. A deleted record remains in the Recycle Bin for 15 days—or even less time if you exceed your storage limit, and the record has been in the Recycle Bin for at least two hours—and then that record is actually removed from the Recycle Bin or flagged for a physical delete. When the Force.com query optimizer judges returned records against its thresholds, all of the records that appear in the Recycle Bin or are marked for physical delete do still count against your total number of records.


From our earlier example of accounts and a custom indexed field, the selectivity threshold was 175,000, and the total number of records was 2.5 million.


Let’s say that a Bulk API job runs and deletes all records before January 1, 2013, and those records total 2.4 million. That leaves us with 100,000 non-deleted account records. If the deleted records are still in the Recycle Bin, the Force.com optimizer mistakenly thinks that the 100,000 non-deleted records fall under and meet a 2.5 million-record selectivity threshold, and it generates a query that isn’t optimized. In reality, the threshold is 10,000 targeted records (10 percent of 100,000 targeted records).


If the deleted records do not need to go to the Recycle Bin, use the hard delete option in the Bulk API or contact salesforce.com Customer Support to physically delete the records.


If your data loads cause the records targeted by your filter to exceed the selectivity threshold, you might need to include additional filters to make your queries selective again.
Using Leading % Wildcards

This is the type of query that would normally work better with SOSL. However, if you need real-time results, an alternative is to create a custom search page, which restricts leading % wildcards and adds governance on the search string(s).


Note: Within a report/list view, the CONTAINS clause translates into ‘%string%’.
Using NOT and !=


When your filter uses != or NOT—which includes using NOT EQUALS/CONTAINS for reports, even if the field is indexed—the Force.com query optimizer can’t use the index to drive the query. For better performance, filter using = or IN, and the reciprocal values.


Note: Using a filter on an indexed field such as CreatedDate is always recommended, but this field was not included in the original query so that we could make a point about the selectivity threshold.
Using Complex Joins


Complex AND/OR conditions and sub-queries require the Force.com query optimizer to produce a query that is optimized for the join, but might not perform as well as multiple issued queries would. This is especially true with the OR condition. For Force.com to use an index for an OR condition, all of the fields in the condition must be indexed and meet the selectivity threshold. If the fields in the OR condition are in multiple objects, and one or more of those fields does not meet a selectivity threshold, the query can be expensive.


For more information on AND/OR conditions, refer to the Inside the Force.com Query Optimizer webinar.


Filters on formula fields that are non-deterministic can’t be indexed and result in additional joins. Common formula field practices include transforming a numeric value in a related object into a text string or using a complex transformation involving multiple related objects. In both cases, if you filter on this formula field, the Force.com query optimizer must join the related objects.


If you have large data volumes and are planning to use this formula field in several queries, creating a separate field to hold the value will perform better than following either of the previous common practices. You’ll need to create a workflow rule or trigger to update this second field, have this new field indexed, and use it in your queries.

 

To find if report is in private folder

SELECT Id, DashboardId, CustomReportId, dashboard.developername, dashboard.title, dashboard.foldername, dashboard.folderid, dashboard.createdby.name FROM DashboardComponent USING SCOPE allPrivate WHERE CustomReportId=''

 

Select Id, CreatedDate from AsyncApexJob where jobtype='ApexToken' AND Status = 'Queued' . 

CreatedDate > 2019-09-04T00:00:00Z order by CreatedDate desc

SELECT ApexClassId,CreatedDate,Id,JobItemsProcessed,NumberOfErrors,ParentJobId,Status,TotalJobItems
FROM AsyncApexJob where Status='Processing' and JobType='BatchApex'  

Concurrent Apex Limit - Unable to Process Request. Concurrent requests limit exceeded.

Please do continue to like, share and subscribe sfdconestop Youtube channel, if you find the Salesforce tutorials(100+ Video's) to be informative!!
▶️Subscribe:  https://www.youtube.com/c/sfdconestop
👉 https://sfdconestop.blogspot.com/
👀 Follow us on Linkedin : https://www.linkedin.com/company/sfdconestop

 

This is one of the Governor limit you may hit if you do not follow the best practices during implementation/code development. Below is the limit where for most of the orgs the limit is 10 which means you cannot have 10 synchronous transactions running concurrently for more than 5 second as for 11th request you hit the governor limit error  as below -



Governor limit:
Number of synchronous concurrent requests for long-running requests that last longer than 5 seconds for each organization.*

Error:
"Unable to Process Request. Concurrent requests limit exceeded.
To protect all customers from excessive usage and Denial of Service attacks, we limit the number of long-running requests that are processed at the same time by an organization. Your request has been denied because this limit has been exceeded by your organization. Please try your request again later."

https://help.salesforce.com/articleView?id=admin_web_limits.htm&type=5

What Counts Against the Limit

A single synchronous Apex request could include Apex code, SOQL, callouts, and triggers. You might need to tune these and other common components because the duration of their transactions counts toward the request limit.

Apex	Classes/controllers, triggers
SOQL
Web Services	External and Apex Web Services
Visualforce	ActionPoller, Ajax/ActionFunctions, JavaScript Remoting
API	Calls to an Apex Class

How to Design to Avoid the Limit

Take the concurrent request limit into consideration as you design your application around your business processes. Does the business process need to be synchronous? Is batch processing possible? Can you use the Streaming API?
Web Services

The most common causes of limit errors are synchronous Web service callouts. When one of your application’s users submits an order, that business process depends upon one or more external Web services, which must finish running for your application to actually create the order. If these external Web services cannot scale to your expected volumes, consider alternative solutions, such as using a callback instead of waiting for the callouts to complete.

To use a callback, just continue to make the synchronous callout. The following steps complete automatically after that.
The external Web service immediately returns an acknowledgement, saying that it received your request.
After the external Web service processing completes, it calls an Apex Web service to update the appropriate data.
The Streaming API publishes that updated data.


SOQL

The performance of your queries and DML operations is another big contributor to long-running requests. As your data grows, inefficient SOQL affects Visualforce pages, detail pages, list views and reports. If you’re querying for large amounts of data, you’ll incur additional processing time both when querying and rendering the data.

Refer to the Force.com Query Optimizer webinar for more information.
Data Skew

Data skew can also contribute to concurrent request limit errors. Consider the following scenario. You have a parent object with 10,000 or more child objects. Ordinarily, when you insert or change an owner of a child object, the Force.com platform automatically locks the parent for a certain amount of time. However, because you have data skew, the platform holds this lock even longer while determining the appropriate record access. The wait time for your lock is included in your total request time, and it causes your request to run for more than 5 seconds.

To avoid data skew so that you can also avoid ending up in this situation, read Reducing Lock Contention by Avoiding Data Skew.
Visualforce

With the ActionPoller component, you can poll by calling an Apex class. Unfortunately, you can’t dynamically change the polling interval or condition. This can result in a large number of unneeded requests. If the polling operation is expensive and takes longer than 5 seconds, you’ll quickly hit the limit. For more scalable requests, use the Streaming API, not polling, to subscribe to near real-time notifications of changes to data.

The <Apex:ActionFunction> component provides support for invoking controller action methods directly from JavaScript code using an AJAX request. JavaScript remoting extends this capability and allows you to create pages with complex, dynamic behavior that isn’t possible with the standard Visualforce AJAX components. However, from an Apex perspective, both of these components are still synchronous requests that are similar to standard Visualforce requests, and they count toward your concurrency limit.
Summary

The concurrent request limit attempts to ensure a positive user experience by limiting the number of synchronous Apex requests running for more than 5 seconds. Once the limit is reached, new synchronous Apex requests are denied. This behavior can be disruptive to your work. Therefore, it is easier to avoid this limit when designing new applications than it is when tuning live applications. You can accomplish this goal by ensuring that your users’ workflows do not include synchronous requests that take longer than 5 seconds.

Some useful tips:
Convert synchronous processes to asynchronous processes. Batch Apex might be a viable alternative. Limit synchronous Web service callouts.
Use the Streaming API instead of polling
Tune SOQL and DML operations. Make sure that your queries are selective. Limit the number of records in your list views. Avoid data skew.

Understand usage of fields in an object using field trip

In a huge organization where we have many custom and standard objects and when we create various fields as per the requirements, there are chances that we end up creating multiple fields and at times might not be using those fields as needed or there may be instances where in we end up the Salesforce limit of field creation per object i.e. 1000 and then we have to look out for options which fields can we delete to accommodate new fields.Eliminating unnecessary fields will help with user adoption and efficiency and makes your org clean. So how can we achieve this i.e. underutilized fields?

We have an application called as Field trip that does this job for us, we have to install this manage package and this app gives us a very clear picture of the percentage utilization of fields across all the objects whether its standard object or custom object. This app can be downloaded from an App exchange for free!!!!

Field Trip lets you analyze the fields of any object, giving you instant insight into what percentage of your records (or a subset) have that specific field populated. Run reports on the standard and custom fields you have in Salesforce for a better understanding of which field are important to your organization.

With a simple install, an intuitive user interface, and an easy-to-export report, Field Trip has made analyzing your fields quick and painless.

Once installed, simply name your trip, select an object (e.g. Accounts) and, optionally add a filter (for record subsets). Then you will receive a report detailing field usage (or lack thereof), available for simple export.

For more details visit : https://appexchange.salesforce.com/listingDetail?listingId=a0N30000003HSXEEA4

Using this link you can download this app for your org and check for the utilization of fields in an object.

Understand your code for a new org

Examining your code base can seem daunting. Especially if you’re new to an org or if your org has lots of code. But understanding how the different pieces of your org relate to one another is an essential, necessary part of identifying how to begin managing your org in more precise, meaningful units.

Type of Code	What to look for	Questions to ask
Triggers	1. Trigger patterns 2. Trigger logic	Does your org have one trigger per object? Is there business or application logic written directly in a trigger? Do triggers “hand off” logic or functionality to other classes (aka trigger handlers)?
Apex Classes	1. Naming conventions 2. Comments 3. API Version	Do Apex classes use common prefixes or even namespaces to group units of code? Do classes have similar names, based on functionality? Is the purpose and authorship of code documented in comments? Do classes have comments that help clarify function? What API versions do classes use?
Apex Tests	1. Test patterns/units 2. Code coverage 3. Test data handling	How do tests relate to other code? Does each class have its own test? Are your tests organized into functional groups? Are there parts of your code base not covered by tests? Do your tests depend on common data factories or static resources? Do any of your tests use the 'seeAllData=True' annotation, or run on an API version earlier than 24?
Lightning Components and Events	1. Naming conventions 2. Comments 3. Apex controllers 4. API Version	Do components use common prefixes or even namespaces to create groups? Do components have clear names, related to functionality? Are Lightning events scoped to be application events or component events? Are the purpose and authorship of components and events clearly documented in comments or Aura documentation files? Do components use Apex controllers? What API versions do components and events use?
Visualforce	1. Naming conventions 2. Comments 3. Apex controllers 4. API Version	Do Visualforce pages and components use common prefixes or even namespaces to create groups? Do pages have clear names, related to functionality? Do pages use Apex controllers? What API versions do pages use? Are pages used with any email templates?

These pieces help you identify patterns within the code in your org. But these techniques may not help you understand every piece of your code base. Or, if your code base doesn’t seem to be consistently organized, you may need to try other ways to discover how your org’s code is connected.

This is where the new Dependency API can help.