+ Start a Discussion
avimeiravimeir 

Optimize the SOQL query?

Hi All,

 

Any idea how the optimize the following query? It takes forever to return with high volume of objects

Map<Id, Room__c> rooms            = new Map<Id, Room__c>([SELECT Name, Room_Type__r.Name, Room_Type__r.Id,Cleaning_Status__c,id,                                        (SELECT Rooms__r.Check_In__c,                                                Rooms__r.Check_Out__c ,Rooms__r.Guest_Name__c,Id                                         FROM Reservation__r                                         WHERE Rooms__r.Check_In__c >= :frm                                               OR Rooms__r.Check_Out__c <= :to)                                   FROM Room__c                                   WHERE Name LIKE :('%' + keyword + '%')                                     OR Room_Type__r.Name LIKE :('%' + keyword + '%')]);

 

Jerun JoseJerun Jose

Your query looks fine to me. I can only think that maybe there are too many records in the DB which is causing the performance issue. In any case, I thought I could provide some insights

 

SELECT Name, Room_Type__r.Name, Room_Type__r.Id,Cleaning_Status__c,id, (SELECT Rooms__r.Check_In__c, Rooms__r.Check_Out__c ,Rooms__r.Guest_Name__c,Id FROM Reservation__r WHERE Rooms__r.Check_In__c >= :frm OR Rooms__r.Check_Out__c <= :to) FROM Room__c WHERE Name LIKE :('%' + keyword + '%') OR Room_Type__r.Name LIKE :('%' + keyword + '%')

 

Try to add more filters in the outer query if you can. You are already using the Name field which is indexed by default, but I have a feeling that like operator is not the quickest one, so if there is some checkbox level filtering that you can apply, that would help.

 

If 'frm' and 'to' are collections, then ensure that the value of 'frm' and 'to' do not contain too many values. If possible filter out nulls and use sets instead of lists.

 

Ensure that the fields Check_In__c and Check_Out__c are not formula fields. Formula fields are terrible for query performance. If you have to use those fields, then replace them with normal fields and try to populate that data using a workflow field update.

 

Lastly, you can try to break the query into 2 individual queries. I can see that the inner query actually filters records based on the parent record, so you could have one query which gives the Room__c details and use another query which gives the Reservation__r details. Of course you will need some custom logic to then map the Reservation__r records with the Room__c records.

 

sfdcfoxsfdcfox
LIKE operators that start with a wildcard will cause the SOQL query optimizer to perform a full-table scan. You may need to use SOSL instead (Salesforce Object Search Language), which can leverage search indexes, which are more powerful. As stated previously, the name field is indexed and also search indexed, so using SOSL will greatly improve performance.

You might also choose to break up the query into two parts, where the former searches for Room_Type__r records that match the keyword search, then query for Room__c records that have a Room_Type__c within a given range of ID values or have the keyword. Your exact performance will vary, but using SOSL may help alleviate the issue at the cost of a few extra SOQL statements.
GunnarGunnar
LIKE :('%' + keyword + '%') << U R Using...

I concur. In SQL Server or Oracle, this would cause a table scan.
You would do something like this only after you have done a major filtering of records already.

If you are looking for a particular 'phrase', consider adding a field, updating the field with the phrase. Then search on this field for the phrase.

Doing this change, I've seen up to 20x reduction in query response times with Oracle.