function readOnly(count){ }
Sign Up ›
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
Learn More

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
Damien_2Damien_2 

Database.query extremely slow as the iterator over a for loop

//I just executed the code like this in my dev env that has 5k accounts and it took about 30 seconds.

For (Account a: Database.query(query))

 

//I just executed the code like this in my dev env that has 5k accounts and it was instant.

List<Account> accs = Database.query(query);

For (Account a: accs)

 

Does anyone know if salesforce realizes the inefficiencies here and if this will be fixed?

sfdcfoxsfdcfox

Damien,

 

Are you sure it wasn't just a random hiccup? I just tried this in my database of ONE account, and it took 96ms on the very first query (Database.query in for loop), but took only 4-6ms on subsequent calls. The other way took 4-6ms on average, too. I presume there is a cache that keeps recent records and tables in memory, which can cause some first-time calls to be slower, the same as mysql. If it's repeatably poor performance, that might be a cause for concern. Also, is this in Summer 12 or Winter 13?
Damien_2Damien_2

It is in my Summer 12 dev org.  This is most definitely NOT a random hiccup.  I ran into this issue about a year ago, and wanted to make sure it was still the case when I responded to a document written by one of my managers.  About a year ago, doing this over a large dataset (I'm guessing tens of thousands) it took over two minutes repeatedly.  When I changed the code it finished in 2-5 seconds.
sfdcfoxsfdcfox

I'll do some further testing and see. Hopefully a mod will come along and pass this on to a product manager if it turns out that it is the case.
sfdcfoxsfdcfox

How complex is the query, or does it seem to matter? I want to know how to faithfully reproduce the event.
Damien_2Damien_2

Add more Accounts then try it.  One account would make no difference.

 

List<Account> accs = new List<Account>();
for (integer i = 0; i < 5000; i++)
  accs.add(new Account(Name = 'test' + i);
insert accs;

 

for (Account a: Database.query('SELECT Name FROM Account'))
  System.debug(a.Name);

 
sfdcfoxsfdcfox

Ironically, that just the code I wrote basically character for character... Great minds think alike, I suppose.
Damien_2Damien_2

Haha, I suppose so!
sfdcfoxsfdcfox
09:13:36:000 CUMULATIVE_PROFILING AnonymousBlock: line 1, column 1: database.query(String): executed 10035 times in 336 ms

 

09:15:38:000 CUMULATIVE_PROFILING AnonymousBlock: line 1, column 1: database.query(String): executed 1 time in 148 ms
09:15:38:000 CUMULATIVE_PROFILING AnonymousBlock: line 3, column 1: global public static void debug(ANY): executed 5016 times in 189 ms
09:15:38:000 CUMULATIVE_PROFILING AnonymousBlock: line 2, column 1: global public system.Iterator iterator(): executed 5018 times in 30 ms

Both runs were given 5016 records of input, tested in the order you've mentioned (for-database and for-array, respectively). Both are performing the same number of script counts (10035), and both are running in about 300ms to 500ms (if anything, the for-array is running slower). This is on na2, given about 10 runs each. Variations can be accounted for given normal fluctuations on server load.
Damien_2Damien_2

Timing the executions below, code a little dirty...

DateTime thisMoment = DateTime.now();
List<Account> accs = Database.query('SELECT Name FROM Account');
for (Account a: accs)
  System.debug(a.Name);
DateTime otherMoment = DateTime.now();
long mom = thisMoment.getTime();
long late = otherMoment.getTime();
System.debug('difference = ' + (late - mom));

This is about middle of the road for this one.  EVERY single one was much lower than the next.

DEBUG|difference = 864

 

 

 

 

DateTime thisMoment = DateTime.now();
//List<Account> accs = Database.query('SELECT Name FROM Account');
for (Account a: Database.query('SELECT Name FROM Account'))
  System.debug(a.Name);
DateTime otherMoment = DateTime.now();
long mom = thisMoment.getTime();
long late = otherMoment.getTime();
System.debug('difference = ' + (late - mom));

 This is sort of the middle of the road I got for this one.

DEBUG|difference = 1585