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.
Sumit Sarkar is a Chief Data Evangelist at Progress, with over 10 years experience working in the data connectivity field. The world's leading consultant on open data standards connectivity with cloud data, Sumit's interests include performance tuning of the data access layer for which he has developed a patent pending technology for its analysis; business intelligence and data warehousing for SaaS platforms; and data connectivity for aPaaS environments, with a focus on standards such as ODBC, JDBC, ADO.NET and ODATA. He is an IBM Certified Consultant for IBM Cognos Business Intelligence and TDWI member. He has presented sessions on data connectivity at various conferences including Dreamforce, Oracle OpenWorld, Strata Hadoop, MongoDB World and SAP Analytics and Business Objects Conference, among many others.
Copyright © 2018 Progress Software Corporation and/or its subsidiaries or affiliates.
All Rights Reserved.
Progress, Telerik, 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.