SOQL / DML / Loops Performance - El Toro - Find articles about Visualforce, Apex, Force.com and Salesforce in general

Print Preview

SOQL / DML / Loops Performance

I recently saw an interesting video by Paul Battison discussing the different Apex loop performances (watch the video here: http://buff.ly/1G4dW3Z), and I wanted to go one step forward and compare the performance for updating a collection child records.
 
So for the test, I created one account with 10K contact records, then query the contacts, loop through each contact updating a field and saving the data back. I came up with 10 different ways to perform this operation  and after running these 10 methods several times I average the times.
 
This chart compares the performance among the different methods:
 

These are the 10 methods I came up with, sorted by fastest to slowest:

// 3.83 seconds
public void Method10(ID AccountID) {
	List<Contact> contacts = [SELECT ID 
                              FROM Contact WHERE AccountID = :AccountID];
	Integer contactsSize = contacts.size();
	for (Integer i = 0; i < contactsSize; i++) {
		contacts[i].DoNotCall = true;
	}
	update contacts;
}

// 3.93 seconds
public void Method09(ID AccountID) {
	List<Contact> contacts = [SELECT ID 
                              FROM Contact WHERE AccountID = :AccountID];
	for (Contact c : contacts){
		c.DoNotCall = true;
	}        
	update contacts;
}

// 3.93 seconds
public void Method03(ID AccountID) {
	Account act = [SELECT ID, (SELECT ID FROM Contacts) 
                   FROM Account WHERE ID = :AccountID];
	List<Contact> contacts = act.contacts;
	Integer contactsSize = contacts.size();
	for (Integer i = 0; i < contactsSize; i++) {
		contacts[i].DoNotCall = true;
	}
	update contacts;
}

// 4.05 seconds
public void Method05(ID AccountID) {
	Account act = [SELECT ID, (SELECT ID FROM Contacts) 
                   FROM Account WHERE ID = :AccountID];
	List<Contact> contacts = act.contacts;
	for (Contact c : contacts) {
		c.DoNotCall = true;
	}
	update contacts;
}

// 4.08 seconds
public void Method07(ID AccountID) {
	for (List<Contact> contacts : [SELECT ID 
                                   FROM Contact WHERE AccountID = :AccountID]){
		for (Contact c : contacts) {
			c.DoNotCall = true;
		}
		update contacts;
	}
}

// 4.09 seconds
public void Method06(ID AccountID) {
	for (List<Account> acts : [SELECT ID, (SELECT ID FROM Contacts) 
                               FROM Account WHERE ID = :AccountID]) {
		for (Account act : acts) {
			for (List<Contact> contacts : act.contacts) {
				for (Contact c : contacts) {
					c.DoNotCall = true;
				}
				update contacts;
			}
		}
	}
}

// 4.10 seconds
public void Method08(ID AccountID) {
	List<Contact> contacts = new List<Contact>();
	for (Contact c : [SELECT ID 
                      FROM Contact WHERE AccountID = :AccountID]){
		c.DoNotCall = true;
		contacts.add(c);
	}        
	update contacts;
}

// 4.19 seconds
public void Method04(ID AccountID) {
	Account act = [SELECT ID, (SELECT ID FROM Contacts) 
                   FROM Account WHERE ID = :AccountID];
	for (Contact c : act.contacts) {
		c.DoNotCall = true;
	}
	update act.contacts;
}

// 4.92 seconds
public void Method02(ID AccountID) {
	Account act = [SELECT ID, (SELECT ID FROM Contacts) 
                   FROM Account WHERE ID = :AccountID];
	Integer contactsSize = act.contacts.size();
	for (Integer i = 0; i < contactsSize; i++) {
		act.contacts[i].DoNotCall = true;
	}
	update act.contacts;
}

// 5.82 seconds
public void Method01(ID AccountID) {
	Account act = [SELECT ID, (SELECT ID FROM Contacts) 
                   FROM Account WHERE ID = :AccountID];
	for (Integer i = 0; i < act.contacts.size(); i++) {
		act.contacts[i].DoNotCall = true;
	}
	update act.contacts;
}

Lessons Learned:

  • Relationship queries do not add too much of an overhead.
  • Create variables to hold the information that will be used in the loops, rather than calculating the same value inside the loops!

comments powered by Disqus

© El Toro . IT @ 2013
Andrés Pérez