How does insert/update statement works internally to DB?

Question

I know that we can insert/update a list of objects at once. But my question is how does it work internally? Does Salesforce internally uses any ORM model to map objects with any DB like MySQL or Oracle etc.? For example :

List<myCustomObj> myList = new List<myCustomObj>();
myList = [Select Name, ID, myCustomField from myCustomObj];

for(myCustomObj obj : myList )
{
    obj.myCustomField = obj.myCustomField + 1;
}
if(myList.size() > 0)
{
    update myList;
}

I know all the records inside myList gets updated at once but how is it working? Does it generate any SQL/SOQL statement internally?

I have a similar example for SQL.

Suppose I have this table:

id     a1     a2
================
1      22     5
2      89     24
3      4      32
4      44     43

and I want to change the data like below in one query:

id     a1    a2
===============
1      2     44
2      39    88
3      40    32
4      13    20

I have a couple of approaches to do so.

Approach1:

UPDATE table SET a1=CASE
      WHEN id=id[1] THEN 2
      WHEN id=id[2] THEN 39
      ...
      ELSE a1 END, a2 = CASE ... END
 WHERE id IN (id[1], id[2], id[3]...);

My question is does SF internally works something similar to above approach?

score 3 · Accepted Answer · answered Oct 23 '17 at 16:39

Salesforce does use Oracle, but does not directly use standard SQL statements (e.g. insert/update/etc). Instead, all of the actions performed are done through stored procedures. This allows the internal developers to have a consistent approach when they add new features.

Fun trivia: the Oracle modules are named after the Seven Dwarfs. Typically, when a rare unhandled exception occurs, you'll see an ORA error code and one of the seven dwarfs (example from link above is GRUMPY). In ordinary cases, you'll never see them, because they are meant to be hidden away from the outside world, but it does give some insight to how the code is internally structured.

If you're interested in how CRMs in general do this, consider researching SugarCRM, which has an Open Source Community Edition that you can poke around in. Note that SugarCRM is based on MySQL, not Oracle, but the principles involved would be similar.

How does insert/update statement works internally to DB?

1 Answers1