How can I profile the performance of Salesforce queries?

Question

In my last question ( Implementing a Keyword Search ) , I described some of the complex queries I've been working on in a new app. In determining my approach, I'd like to consider the performance and speed of different queries, but I don't know how to profile the queries to understand how they're doing and what is causing bottlenecks or performance hits.

In SQL, I'd use EXPLAIN. What tools are available for SOQL, Salesforce, etc.?

Answer 1

You can gather some profiling information from your debug log. If you switch your log level to finest on Profiling, you can gain information such as this in the log which will at least give you execution time for each query:

 07:06:23.980|CUMULATIVE_PROFILING_BEGIN
07:06:23.980|CUMULATIVE_PROFILING|SOQL operations|
AnonymousBlock: line 8, column 1: [
Select 
    PricebookEntry.Product2Id,OpportunityId,ffps_prpm__Invoices_Created__c,ffps_prpm__Invoiced_Date__c,id 
From 
   OpportunityLineItem 
Where
    OpportunityId in :oppIds
    ]: executed 1 time in 972 ms
AnonymousBlock: line 1, column 1: [ select Id, c2g__Product__c, Opportunity__c from c2g__codaInvoiceLineItem__c where Opportunity__c != null and c2g__Invoice__r.c2g__InvoiceStatus__C = 'In Progress']: executed 1 time in 401 ms

07:06:23.980|CUMULATIVE_PROFILING|No profiling information for SOSL operations
07:06:23.980|CUMULATIVE_PROFILING|No profiling information for DML operations
07:06:23.980|CUMULATIVE_PROFILING|method invocations|
External entry point: public static void execute(): executed 1 time in 2128 ms
AnonymousBlock: line 3, column 1: global public system.Iterator iterator(): executed 7074 times in 37 ms
AnonymousBlock: line 5, column 1: global public Boolean add(Object): executed 7072 times in 16 ms
AnonymousBlock: line 2, column 1: global public SET<Id>(Integer): executed 1 time in 0 ms

07:06:23.980|CUMULATIVE_PROFILING_END

You can do this under developer console by opening workspace manager and Change(ing) Log Levels.