Help get this topic noticed by sharing it on Twitter, Facebook, or email.

A definitive discussion of writing to SalesForce objects

I have not found a succinct discussion of the relative merits of the various means of writing to SalesForce objects.

Native T-SQL (insert, update, upsert, delete) is undesirable because an API call is issued for each record, slowing processing speed and quickly approaching contractual limits on API calls. It is the easiest to understand and requires no transaction file, but all the work is performed on the local machine.

SOQL's OpenQuery construct is slightly more desirable because the record access occurs on the server's side. It also makes an API call for each record, and its syntax is a little arcane. It facilitates parent/child related records without requiring an explicit JOIN clause.

The SF_BulkOps stored procedure is much more desirable because it batches the job, reducing the number of API calls. It requires a transaction table that contains at least output ID and error fields, plus the fields to be inserted or updated. It allows the reference to a foreign key, eliminating the need to retrieve the destination object's ID. The success or failure of the process is written to the error field of each record. The use of the BulkAPI switch can realize even greater performance gains.

The SF_TableLoader stored procedure is generally the most desirable. It realizes exponential performance improvement as the number of records increases. It, too, requires a transaction table, but the success or failure of a process is written to a separate _Result table. A sort (identity) column mitigates record locking. The SF_TableLoader procedure uses the restricted xp_cmdshell command; the procedure will determine whether to use the SOAP or BulkAPI call; BulkAPI2 can be specified. Otherwise, use the CmdExec feature of the SQL job step to invoke DBAmpNet2.exe directly; strict command-line parameters must be supplied.

For batches of fewer than 3000, the SF_BulkOps is the most efficient; for larger files (even numbering into the hundreds of thousands), SF_TableLoader using BulkAPI is by far most efficient.

Questions:
1. Is the diagram on page 59 of the documentation accurate? Must blank columns for ID and Error be included when SF_TableLoader is utilized?
2. Can either an ID field or a field named alias.ID be populated and that field be used to locate the record in an upsert or an update process under either SF_BulkOps or SF_TableLoader?
3. Must the data in a sort-ordered file under SF_TableLoader be loaded in AccountID order, or should it be in the order of the ID of that object?
4. Why would SF_BulkOps ever be used? Is it because of its simplicity, especially for smaller jobs of ten or fifteen thousand, where the performance hit is not so significant?
5. Am I correct that the execution of any (well-designed) trigger on an object to which any of these methods writes is not inhibited?
1 person has
this question
+1
Reply
  • 1. For SF_TableLoader the Error column does not need to be supplied in the input table. TableLoader will create and populate the Error column for you. The ID column is blank or Null because it is referencing that it will be an Insert operation, therefore an ID does not need to be supplied.

    2. It needs to be the actual column name up on Salesforce, which is ID, otherwise that field will be ignored. Both stored procs match on the actual Salesforce column names to push data to SF.

    3. If you are using the Sort column functionality, Salesforce recommends that you sort by the master record Id. If you are not using the Sort column functionality, it doesn't really matter what order the records are in. But, it always doesn't hurt to order them by the master record Id anyways.

    4. We recommend always using SF_TableLoader. SF_TableLoader is the replacement for SF_BulkOps.

    5. Any triggers on that object are enforced by Salesforce when pushing data to an object. If there is an insert trigger on the object and you insert records into that object, that trigger is enforced and will fire. Not sure is that answers your question or not?
  • (some HTML allowed)
    How does this make you feel?
    Add Image
    I'm

    e.g. sad, anxious, confused, frustrated kidding, amused, unsure, silly indifferent, undecided, unconcerned happy, confident, thankful, excited