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

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

​

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.

Continue reading...