You need to sign in to do that
Don't have an account?
avimeir
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 + '%')]);
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
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.
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.