2

Simple query, taking 1 minute in the VF page controller, and less than 1 second when executed in the Developer Console. There are more than 50,000 records that the query is searching through. Why such a dramatic difference in the query run time?

String s = '123456';
List<Registration__c> regs = 
    [select id, name 
     from Registration__c 
     where name =: s or speical_number__c =: s limit 1];

Here are the excerpts from the debug logs:

Developer Console:

12:22:39.063 (63557000)|SOQL_EXECUTE_BEGIN|[2]|Aggregations:0|select id, name from Registration__c where (name = :tmpVar1 or speical_number__c = :tmpVar2) limit 1

12:22:39.263 (263582000)|SOQL_EXECUTE_END|[2]|Rows:0

VF Page Controller:

12:17:08.148 (3148592000)|SOQL_EXECUTE_BEGIN|[633]|Aggregations:0|select id, name from Registration__c where (name = :tmpVar1 or speical_number__c = :tmpVar2) limit 1

12:18:07.350 (62350264000)|SOQL_EXECUTE_END|[633]|Rows:0

Kirill Yunussov
  • 1,955
  • 1
  • 21
  • 24
  • No idea :/ As stupid as it sounds - in same environment? Any batch jobs / heavy usage kicking off in the background? Can you try indexing the numeric column (mark it as external id)? Or even better - create a helper text field, use a workflow to fill it in with `Name + ' ' + TEXT(speical_number__c)` and try to use this field in the where clause? – eyescream Feb 12 '13 at 21:37

1 Answers1

0

I suspect this has to do with how VF/Apex batch-up replies. Although, the limit 1 would seem to indicate that would not be the case. But, just for kicks ... try this ...

String s = '123456';
List<Registration__c> regs = new List<Registration__c>();
for ( Registration__c reg : 
    [select id, name 
     from Registration__c 
     where name =: s or speical_number__c =: s 
     limit 1
    ] ) {
   regs.add(reg);
}

Is speical_number__c a good candidate for external id ? I ask because setting that field to an external id will guarantee Salesforce.com will create an index for your field. Without an index, the query would do a full table scan.

Eric Sexton
  • 496
  • 2
  • 6