SLAM_lockbuster

Overview

LockBuster is a specialized data loading procedure unique to Speediful. It eliminates lock issues commonly experienced with bulk loads while also optimizing for speed. These lock errors are a direct result of the relationships on the object.

LockBuster is as simple to use as other Speediful data loading procedure. It only requires the user to provide the relationships that may cause locking.

tip

When to use LockBuster

LockBuster can be used at any time, but excels in any of these situations:

• High data skew, OR
• Junction objects, OR
• Objects with master-detail relationships, OR
• When the object is included in roll-up style calculations on related records

We recommend trying LockBuster when you have roughly 100,000 or more records to load.

Data skew in Salesforce occurs when there are large quantities of records that look up to one parent. To see if you have data skew, look for counts of 6,000 or more in this query. Run this multiple times, replacing lookup_field_n for each lookup or master-detail relationship field that is prone to locking:

SELECT lookup_field_n, COUNT(*) AS count
FROM table_name
GROUP BY lookup_field_n
ORDER BY count(*) DESC

Parameters

Name	Type	Default	Description
@sObject	NVARCHAR	Required	Salesforce object name to load data into
@table	NVARCHAR	Required	Input SQL table containing data to load
@operation	NVARCHAR	Required	Operation type ('insert', 'update', 'upsert', 'delete', 'hardDelete')
@foreign_keys	NVARCHAR	Required	Comma-separated list of foreign key column names to analyze for dependencies. Can accept a list of 1, 2 or even more ids (2 is most common). See recommended recipes below
@batch_size	INT	NULL	Maximum batch size for processing. Note that lockbuster may create batches lower than this size
@externalId	NVARCHAR	NULL	External ID field name (required for upsert operations)

Recommended Recipes

In general, passing references to grandparent/great-grandparent objects is more conservative and more likely to lead to success than passing a parent object. For example, when loading OpportunityLineItems for a standard Salesforce org, passing the Product Id and Opportunity Id references would be successful. However, if the org has been customized with Opportunity roll-up summaries on the Account, then locking at the Account (grandparent object) could still occur because locks have only been prevented at the Opportunity (parent object) level. Passing the Account Id (grandparent) reference instead of the Opportunity Id (parent) reference will alleviate this issue.

The references passed to @foreign_keys do not need to be actual Salesforce fields or even Salesforce Ids. They could be external ids or other identifiers that describe the relationship of the current object to its predecessors.

Junction Object Recipe

Set @foreign_keys to

• The two lookup/master-detail lookup fields

Example:

EXEC dbo.SLAM_lockbuster
    @sObject = 'Junction_Object__c',
    @table = 'Junction_Object__c_insert',
    @operation = 'insert',
    @foreign_keys = 'Lookup_Field_1__c,Lookup_Field_2__c';

QuoteLineItem / SBQQ__QuoteLine__c Recipe

Set @foreign_keys to

• Product2Id
• QuoteId
• Pricebook2Id (if multiple pricebooks in use)

Example:

EXEC dbo.SLAM_lockbuster
    @sObject = 'QuoteLineItem',
    @table = 'QuoteLineItem_insert',
    @operation = 'insert',
    @foreign_keys = 'Product2Id,QuoteId,PricebookEntryId';

OpportunityLineItem Recipe

Set @foreign_keys to

• Product2Id
• OpportunityId (*)
• Pricebook2Id (if multiple pricebooks in use)
• Campaign Ultimate Parent Id (**) (if Opportunity Campaigns in use)

Footnotes

(*) CampaignId can be used instead of Campaign Ultimate Parent Id if it is permissible to temporarily flatten the Campaign hierarchy (by blanking out the ParentId). The built-in rollup summary in the Campaign hierarchy is prone to causing lock contention due to native Salesforce processing up the hierarchy

(*) If there are rollup summaries from Opportunities onto Accounts, replace the OpportuityId with the AccountId

Example:

EXEC dbo.SLAM_lockbuster
    @sObject = 'OppoortunityLineItem',
    @table = 'OppoortunityLineItem_insert',
    @operation = 'insert',
    @foreign_keys = 'Product2Id,OpportunityId,PricebookEntryId';

CampaignMember Recipe

Set @foreign_keys to

• Campaign Ultimate Parent Id (*)
• ContactId (if Contacts in use)
• LeadId (if Leads in use)
• AccountId (if Accounts in use)

Footnotes

(*) CampaignId can be used instead of Campaign Ultimate Parent Id if it is permissible to temporarily flatten the Campaign hierarchy (by blanking out the ParentId). The built-in rollup summary in the Campaign hierarchy is prone to causing lock contention due to native Salesforce processing up the hierarchy

Usage Examples

Load CampaignMember records (assuming flat hierarchy):

EXEC dbo.SLAM_lockbuster
    @sObject = 'CampaignMember',
    @table = 'CampaignMember_insert',
    @operation = 'insert',
    @foreign_keys = 'ContactId,CampaignId';

Load CampaignMember records (assuming nested hierarchy):

-- Create and Populate the Ultimate_Parent_Id field
ALTER TABLE CampaignMember_insert ADD COLUMN UlimateParentId NVARCHAR(18)

WITH CampaignHierarchy AS (
    SELECT Id, ParentId, Id AS OriginalId FROM CampaignMember_insert
    UNION ALL
    SELECT c.Id, c.ParentId, ch.OriginalId
    FROM CampaignMember_insert c
    INNER JOIN CampaignHierarchy ch ON c.Id = ch.ParentId
),
UltimateParents AS (
    -- Get the ultimate parent for each campaign
    SELECT
        OriginalId,
        Id AS Ultimate_Parent_Id
    FROM CampaignHierarchy
    WHERE ParentId IS NULL
)
UPDATE c
SET c.Ultimate_Parent_Id = up.Ultimate_Parent_Id
FROM CampaignMember_insert c
INNER JOIN UltimateParents up ON c.Id = up.OriginalId;

-- load
EXEC dbo.SLAM_lockbuster
    @sObject = 'CampaignMember',
    @table = 'CampaignMember_insert',
    @operation = 'insert',
    @foreign_keys = 'ContactId,Ultimate_Parent_Id';

Upsert QuoteLineItem records and create relationships on external ids:

EXEC dbo.SLAM_lockbuster
    @sObject = 'QuoteLineItem',
    @table = 'QuoteLineItem_upsert',
    @operation = 'upsert',
    @foreign_keys = 'Product2.External_Product2_Id__c,QuoteId.External_Quote_Id__c',
    @batch_size = 10000,
    @externalId = 'External_QuoteLineItem_Id__c';

Delete OpportunityLineItem records:

EXEC dbo.SLAM_lockbuster
    @sObject = 'OpportunityLineItem',
    @table = 'OpportunityLineItem_delete',
    @operation = 'delete',
    @foreign_keys = 'OpportunityId,Product2Id',
    @batch_size = 10000;