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.
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.
In other words, there are several things to consider before performing updates to the platform through code.
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.
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.
#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
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
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.
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.
Subscribe to get all the news, info and tutorials you need to build better business apps and sites
Copyright © 2019 Progress Software Corporation and/or its subsidiaries or affiliates.
All Rights Reserved.
Progress, Telerik, Ipswitch, and certain product names used herein are trademarks or registered trademarks of Progress Software Corporation and/or one of its subsidiaries or affiliates in the U.S. and/or other countries. See Trademarks for appropriate markings.