BULK UPDATES to Salesforce objects are 16X faster with 11X fewer web service calls

BULK UPDATES to Salesforce objects are 16X faster with 11X fewer web service calls

Posted on October 24, 2013 3 Comments

After a dozen conversations with shops about mass data updates to the Salesforce platform, I decided to tackle this basic topic being called "one of the hardest easy things to do" by those who know SQL. The information in this article can be applied to INSERTS,DELETES,UPSERTS, etc; but I'll focus on UPDATES to keep things simple.

Why UPDATES are the hardest easy thing to do?

Salesforce.com has an API which is very nice for force.com developers to manipulate data using APEX, but what about the rest who know SQL?  While SOQL is a subset of SQL, it supports DML such as updates very differently than SQL.

Here is a write-up on best practices for the Salesforce Platform for writing apex code related to UPDATES.

  • Best Practice #1: Bulkify your Code
  • Best Practice #2: Avoid SOQL Queries or DML statements inside FOR Loops
  • Best Practice #7: Use of the Limits Apex Methods to Avoid Hitting Governor Limits
  • Best Practices #10: Avoid Hardcoding IDs

In other words, there are several things to consider before performing updates to the platform through code.

How did DataDirect ODBC/JDBC drivers make this universally easy?

DataDirect Connect XE for ODBC and JDBC drivers make Salesforce.com look and feel like a relational database, so you can use SQL (or SQL based tools).  Since we communicate through the Salesforce published web service APIs, there is a significant amount of magic built into the drivers to provide a relational database like experience.

Thanks to the drivers, the only question you really need to ask is, "how many records am I trying to update using a single operation?".  If the number is very large, consider setting the connection property EnableBulkLoad=true.  The connection property name may be misleading, but EnableBulkLoad applies to any bulk operation the Salesforce.com API can support including, DELETE,INSERT,UPDATE.  Further, we support these operations, as well as UPSERT, through DataDirect bulk interfaces for ODBC and JDBC that provide expanded functionality.

Test drive for UPDATE - 16X faster with 11X fewer web service calls

Environment Details:

  • Salesforce Enterprise Account
  • Connect XE for JDBC Salesforce driver v5.1
  • O/S: Windows 7
  • Application: DB Visualizer

Steps:

  • Connect to Salesforce.com from DBVisualizer using JDBC
  • Execute the query against Opportunity table with 174965 rows: UPDATE OPPORTUNITY SET AMOUNT=AMOUNT*1.1
  • Determine # of web service calls used for the session with following query: select WS_CALL_COUNT FROM INFORMATION_SCHEMA.SYSTEM_REMOTE_SESSIONS WHERE SESSION_ID = CURSESSIONID() AND SCHEMA = 'SFORCE'
  • Retest with by setting connection property, EnableBulkLoad=true

Note: Performance can be further tuned with several supporting connection properties.  Test results are independent of the world famous DataDirect performance lab, and it is recommended to perform testing in a production like environment.

Results:

#EnableBulkLoad=false (default property value that works great for updating fewer records at a time)

SQL> UPDATE OPPORTUNITY SET AMOUNT=AMOUNT*1.1

12:09:05 [UPDATE - 174965 row(s), 3090.588 secs] Command processed ... 1 statement(s) executed, 174965 row(s) affected, exec/fetch time: 3090.588/0.000 sec [1 successful, 0 warnings, 0 errors]

Web Service Calls: 964

Time: 3090.588 secs

#EnableBulkLoad=true

SQL> UPDATE OPPORTUNITY SET AMOUNT=AMOUNT*1.1

13:32:05 [UPDATE - 174965 row(s), 186.319 secs] Command processed ... 1 statement(s) executed, 174965 row(s) affected, exec/fetch time: 186.319/0.000 sec [1 successful, 0 warnings, 0 errors]

Web Service Calls: 89

Time: 186.319 secs

sfdc-updates

Go for a SQL test drive against the Salesforce Platform

1. Download free 15 day trial of the DataDirect Connect XE for ODBC or JDBC Salesforce drivers to write SQL directly against Salesforce Platform.

2. Or call 1-800-876-3101 to speak live with a Systems Engineer to learn more.

Sumit Sakar

Sumit Sarkar

Technology researcher, thought leader and speaker working to enable enterprises to rapidly adopt new technologies that are adaptive, connected and cognitive. Sumit has been working in the data access infrastructure field for over 10 years servicing web/mobile developers, data engineers and data scientists. His primary areas of focus include cross platform app development, serverless architectures, and hybrid enterprise data management that supports open standards such as ODBC, JDBC, ADO.NET, GraphQL, OData/REST. He has presented dozens of technology sessions at conferences such as Dreamforce, Oracle OpenWorld, Strata Hadoop World, API World, Microstrategy World, MongoDB World, etc.

Comments

Comments are disabled in preview mode.
Topics

Sitefinity Training and Certification Now Available.

Let our experts teach you how to use Sitefinity's best-in-class features to deliver compelling digital experiences.

Learn More
Latest Stories
in Your Inbox

Subscribe to get all the news, info and tutorials you need to build better business apps and sites

Loading animation