SLAM_lockbuster

Specialized data loading procedure that eliminates lock issues associated with bulk loading while also optimizing for speed.

When to use LockBuster

LockBuster can be used at any time, but excels in situations with high data skew, junction objects, master-detail relationships and roll-up style calculations on related records. We recommend trying LockBuster when you have roughly 100,000 or more records to load

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	Source 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. In fact, they don't even have to be Salesforce Ids. They could be external ids or other identifiers that describe the relationship of the current object to its predecessors.

Junction Objects

Set @foreign_keys to

• Use the two lookup/master-detail lookup fields

QuoteLineItem / SBQQ_QuoteLine__c

Set @foreign_keys to

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

OpportunityLineItem

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

CampaignMember

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