Bulk data processing in Salesforce CRM -- How to Expedite?? Tips n Tricks \\ Part1

Objective :

The objective of this article is to highlight few smart ways that can help expedite processing of large volume of data in Salesforce. 

I am sure, some of our Salesforce Admins/Developers managing salesforce Orgs would have seen the instance evolve with more data and functionality. But on one fine day would hit performance glitches while uploading bulk data to salesforce. 

There can be many reasons why we would face slowness in throughput while uploading data to Salesforce. This article tries to unearth one such reason that might prove to be a gamechanger in answering below questions;

•  Have you faced slowness in uploading data to Salesforce?
•  Does the data upload face too many errors due to code OR Org Lock/Row Lock?
•  And does your data upload process take a long time that you get frustrated with the whole deal? 

With that, lets now take a look at Universal Container’s Salesforce instance where George is wearing the hat of Salesforce Administrator and Vidhya is our Solution Architect. Their Usecase is explained as below:

Usecase: 

George is trying to upload 100k Opportunities (.csv file) to Salesforce using Dataloader and batch size of Operation is 200. The overall process is snail-paced, throws a lot of error (mainly Row Lock) and George is frustrated.

Now, lets also consider that Universal Container is already hosting 3M Accounts and 5M Opportunities.

George would then conversate with Vidhya (Solution Architect) to seek her expertise on this matter.

Vidhya: Hey George. Good to see you here and You are looking a bit dull. What's the matter?

George: Hi Vidhya. You are right. I have a bulk data load request to process and it is taking forever to complete. I am afraid, I will not be able to finish today as per the deadline of the requestor.

Vidhya: No Worries, George. Walk me through the request and let’s see what can be done.

George shares the data he is trying to upload and walks Vidhya through the error messages thrown. Lets now look at how Vidhya helps George in resolving the issue.

Analysis:

As George explains, Vidhya Kicks off her analysis by looking at the type of errors thrown || Data analysis as second step, followed by Org Analysis as explained below;

1. Error Analysis: The data upload process threw 3 types of error. 70% of the error was due to ROW LOCK ("Unable to lock the row ...") || 20% of the error was due to Validation Rules in Opportunity Object and rest threw LONG RUNNING REQUEST error. Having understood the gist of error messages, Vidhya proceeded with data analysis as the next step.
2. Data Analysis: Vidhya analyzed the data and spotted some interesting aspects that proved to be the turning point of this analysis, which were.

                        i.         Data sorting: The Data upload file for Opportunity were not sorted based on associated "Account". Data Sorting prior to upload is one of the best practices to be followed in Salesforce. One should always sort the file based on the Object's Parent. To understand an object's Parent, we can make use of Salesforce Schema Builder, wherein one can load the Objects to understand its relationship. Please refer to the Salesforce Help Article for more details Salesforce Schema Builder.  

                       ii.         Data Skew - Ownership: Data Skew - Ownership is a very important concept of Salesforce and is defined by the Spread of Record Ownership among Owners. As Vidhya analyzed the file, she spotted that all the Opportunities were assigned to a single owner/user. 

A Salesforce Org reaches a "Data Skew - Ownership" situation if the Record Owner's within the Org happens to own more than 10K of records. For instance, we can have users in an Org owning 10K (and above) Opportunities OR Accounts OR Cases OR any Custom Objects. Impact of Data Skew - Ownership will be:

 

ü  "ROW LOCK" errors for not only the primary Object, but also for its associated Parent/Child Objects owned by the Single User. In our case, we have a single User being assigned ownership for the entire 100k Opportunities. As a result, "ROW LOCK" Errors can happen whenever one tries to 

 

Create/Update Opportunities owned by the User || Update the associated Account of the opportunity || Create/Update Opportunity Line Item associated with opportunity || Create/Update Case linked with Opportunity || Create/Update associated Child records of the Opportunity.

 

ü  "ROW LOCK" will slowly magnify to "Org Wide Lock" if we have the same user owning a large number of Accounts in the system. As a result, "Org Wide Lock" can happen whenever one tries to

 

Create/Update Opportunities linked with Account owned by single user || Create/Update Cases/Contacts/Contracts linked with Account owned by the single user || Create/Update Child Records linked with Accounts owned by Single user.

 

ü  Page Load Performance while saving records gets severely impacted and end users will be repeatedly faced with the typical Time Out message of Salesforce ("Time Limit Exceeded" error message). 

 

ü  LONG RUNNING REQUEST Error of salesforce is also a byproduct of Skew situation, if we cannot manage or properly define the record ownership. Long Running Request can result from simple search operation, Report load, List View Load OR while trying to query such type of data via API.

 

And, above issues get magnified if the “skewed” user is assigned a Role/Territory that is at the middle/lower section of the Hierarchy.

 

In Short, Data Skew-Ownership not only results in slowness on Data Upload operations but also impacts the Page load Performance in an Org.

 

3.     Org Analysis: Lastly, Vidhya checks on the "Org Wide Default" settings in the Org for Opportunity and it was defined as Private. Organization-Wide Defaults, or OWDs, are the baseline security one has for Salesforce instance. Organizational Wide Defaults are used to restrict access and one can define 4 settings per Object.

 

Private (Implies the records associated with the Object can only be Edited/Read by the Record Owner OR anyone who is above the Record Owner as per the Role Hierarchy), 

Public Read-Only (Implies the records associated with the Object can the Read by all the users in the Org), 

Public Read/Write (Implies the records associated with the Object can be Read/Edited by all the users in the Org). 

Controlled By Parent (Implies the OWD of the Object is the same as of its Parent Object. This is mostly applicable on a Master-Detail Relationship Objects).

 

Details about the OWD can be found in the Salesforce Help Article (Salesforce OWD settings)

 

And to provide additional record visibility, one can leverage Salesforce Sharing Rules as explained in the Knowledge Article (Salesforce Sharing Rules)

 

Vidhya thus completes her Analysis and provides below Recommendations that really expedited George's task. The highlight of these recommendations is that they don’t need any Deployments or Code Changes.

 

Aren't you Curious what they are??  Lets cut to the chase

 

Recommendations:

1. Sort the Opportunity Data File based on Account. This will reduce LOCK errors on Account to a certain Degree.
2. Define alternate owner for Opportunities so that we don’t have Users owning more than 10K of records.

IF the record owners can’t be changed due to business process/restrictions, the next best action is to

                        i.         UPDATE the Role of the user to blank or null. Implies, blank the role of the user so that impact on the Role Hierarchy of the Org ceases to exist and so does BULK of the "ROW LOCK" errors.

                       ii.         Since the User is skewed and owns a lot of records, there are possibilities of hitting “Org Administration Lock” while performing Role update. In such situations, its better to perform the User’s Role update with Defer sharing Enabled OR at a window where the system is Idle and no Admin related activities are being performed.

                     iii.         Create a Sharing Rule between the Skewed User and User’s who would like to see the data as desired. This is required so that Overall data visibility does not get compromised due to the skewed user’s Role update to Blank.

3. In case of Validation Rules, either

                        i.         Work with the requestor to correct the data so that Validation rules can be prevented, OR

                       ii.         Upload Opportunities at a time when the Org is Idle, OR

                     iii.         Modify the validation Rules so that they get skipped as part of data loading process. (A gist of it is explained in the article Skip VR

George followed above recommendations to see his data uploading performance scale up and successfully met requestor's deadline. George was so relieved and thanked Vidhya for her support. 

 Conclusion:

Aren’t we all relieved and happy for George. Lets give a big cheer for him.  

** Author’s note: In my experience, at certain times I have seen performance scale up 4x times based on the recommendations. It actually depends on the size of data in the Org and associated Sharing Rules, Role Hierarchy. But, one will definitely see spike in performance with these recommendations.

 

And so, we conclude Part 1 of this Blog series. I hope this article helps you to resolve similar issues at your end. Am eager to hear your feedback and inputs.

 

Our Next article will focus on improving Batch Job’s performance in updating data.

 

Article Links/References:

 

1. Salesforce Schema Builder.  https://help.salesforce.com/articleView?id=schema_builder.htm&type=5
2. Salesforce OWD settings   https://help.salesforce.com/articleView?id=admin_sharing.htm&type=5
3. Salesforce Sharing Rules https://help.salesforce.com/articleView?id=security_about_sharing_rules.htm&type=5
4. Skip VR https://www.forcetalks.com/salesforce-topic/can-we-bypass-validation-rules/
5. Data skew https://developer.salesforce.com/docs/atlas.en-us.draes.meta/draes/draes_group_membership_data_skew.htm