+ Start a Discussion
Jean Grey 10Jean Grey 10 

How to shorten processing time on custom search page

I have a page with a class behind that searches the database for all related leads, contacts, accounts, opportunities, and tasks. The search term is the domain, which I match to the email address or the website. We have millions of records and the page works but it takes a really long time to load. What can I do to speed this up? Should I try paginating my queries? Any other ideas?
 
public class domainSearchPage {
    ApexPages.StandardSetController setCont;
    public domainSearchPage(ApexPages.StandardSetController controllerD) {
        setCont = controllerD;
    }
    public String domain {get;set;}
    transient List<Lead> leadList {get;set;}
    transient Set<Id> leadSet {get;set;}
    public Integer leadSize {get;set;}
    transient List<Lead> matchedLeads {get;set;}
    public List<Lead> leadList1K {get;set;}
    public List<Account> accList1K {get;set;}
    public List<Contact> conList1K {get;set;}
    public List<Task> existingTasks1K {get;set;}
    public List<Task> openTasks1K {get;set;}
    transient List<Account> accList {get;set;}
    transient Set<Id> accSet {get;set;}
    public Integer accSize {get;set;}
    transient List<Contact> conList {get;set;}
    transient Set<Id> conSet {get;set;}
    public Integer conSize {get;set;}
    transient List<Opportunity> oppList {get;set;}
    transient Set<Id> oppSet {get;set;}
    public Integer oppSize {get;set;}
    transient List<Task> taskList {get;set;}
    transient Set<Id> taskSet {get;set;}
    transient Set<Id> existingTaskSet {get;set;}
    transient Set<Id> openTaskSet {get;set;}
    public Integer openTaskSize {get;set;}
    public List<Task> openTasks {get;set;}
    public List<Task> existingTasks {get;set;}
    public Integer existingTaskSize {get;set;}
    transient Set<Id> whoSet {get;set;}
    transient Set<Id> whatSet {get;set;}
    transient Set<Id> repSet {get;set;}
    public Integer repSize {get;set;}
    public List<Lead> leadShortList {get;set;}
    public List<Contact> conShortList {get;set;}
    public List<Task> openTaskShortList {get;set;}
    public List<Task> existingTaskShortList {get;set;}
    public List<Account> accShortList {get;set;}
    public List<Opportunity> oppShortList {get;set;}
    public List<Opportunity> oppListTest {get;set;}
    public List<OpportunityContactRole> ocrList {get;set;}
    
    public void search(){
        if(Test.isRunningTest()){domain='test';}
        leadSet= new Set<Id>();
        conSet = new Set<Id>();
        accSet = new Set<Id>();
        oppSet = new Set<Id>();
        taskSet = new Set<Id>();
        whoSet = new Set<Id>();
        whatSet = new Set<Id>();
        existingTaskSet = new Set<Id>();
        openTaskSet = new Set<Id>();
        repSet = new Set<Id>();
        
        leadList = new List<Lead>();
        matchedLeads = new List<Lead>();
        conList = new List<Contact>();
        oppList = new List<Opportunity>();
        ocrList = new List<OpportunityContactRole>();
        oppShortList= new List<Opportunity>();
        conShortList = new List<Contact>();
        accShortList = new List<Account>();
        leadShortList = new List<Lead>();
        existingTaskShortList = new List<Task>();
        openTaskShortList = new List<Task>();
        existingTasks= new List<Task>();
        openTasks= new List<Task>();

        leadList = new List<Lead>();
        conList=new List<Contact>();
        existingTasks=new List<Task>();
        openTasks=new List<Task>();
        accList=new List<Account>();
        oppList=new List<Opportunity>();
        oppShortList=new List<Opportunity>();
        
        //get list of users for task search later
        List<User> repList = new List<User>([SELECT Id FROM User WHERE Forecast_User__c=TRUE]);
        List<User> notSales = new List<User>([SELECT Id FROM User WHERE Forecast_User__c=FALSE]);
		Set<Id> notSalesSet = new Set<Id>();
        for(User u :notSales){
            notSalesSet.add(u.Id);
        }
        Set<Id> repSet = new Set<Id>();
        Set<Id> salesReps = new Set<Id>();
        for(User u :repList){
            salesReps.add(u.Id);
        }
        String leadQuery = 'SELECT Id,Name,Domain__c,Part_of_Prospecting_List__c,Territory_ID__c,Company_Size__c,Matched_Account__c,Matched_Account__r.Name,Matched_Account__r.OwnerId,Website,OwnerId,Owner.Name,Owner.UserRole.Name,Company FROM Lead where IsConverted=FALSE AND (Part_of_Prospecting_List__c=TRUE OR Synced_with_HubSpot__c=TRUE) AND Domain__c LIKE \''+domain+'%\'';
        leadList = Database.query(leadQuery);
        system.debug('leadList '+leadList.size());
        for(Lead l :leadList){
            leadSet.add(l.Id);
            whoSet.add(l.Id);
            repSet.add(l.OwnerId);
        }
        String leadsWebsite = 'SELECT Id,Name,Domain__c,Part_of_Prospecting_List__c,Territory_ID__c,Company_Size__c,Matched_Account__c,Matched_Account__r.Name,Matched_Account__r.OwnerId,Website,OwnerId,Owner.Name,Owner.UserRole.Name,Company FROM Lead where IsConverted=FALSE AND (Part_of_Prospecting_List__c=TRUE OR Synced_with_HubSpot__c=TRUE) AND Website LIKE \''+domain+'%\'';
        List<Lead> leadListWebsite = new List<Lead>();
        leadListWebsite = Database.query(leadsWebsite);
        system.debug('leadListWebsite '+leadListWebsite.size());
        for(Lead l :leadListWebsite){
            leadSet.add(l.Id);
            whoSet.add(l.Id);
            repSet.add(l.OwnerId);
        }
        system.debug('repSet '+repSet);
        String leadsLMdomain = 'SELECT Id,Name,Domain__c,Part_of_Prospecting_List__c,Territory_ID__c,Company_Size__c,Matched_Account__c,Matched_Account__r.Name,Matched_Account__r.OwnerId,Website,OwnerId,Owner.Name,Owner.UserRole.Name,Company FROM Lead where IsConverted=FALSE AND (Part_of_Prospecting_List__c=TRUE OR Synced_with_HubSpot__c=TRUE) AND Matched_Account__r.Domain_for_lead_conversion__c LIKE \''+domain+'%\'';
        List<Lead> leadListLMdomain = new List<Lead>();
        leadListLMdomain = Database.query(leadsLMdomain);
        system.debug('leadListLMdomain '+leadListLMdomain.size());
        for(Lead l :leadListLMdomain){
            leadSet.add(l.Id);
            whoSet.add(l.Id);
            repSet.add(l.OwnerId);
        }
        String leadsLMwebsite = 'SELECT Id,Name,Domain__c,Part_of_Prospecting_List__c,Territory_ID__c,Company_Size__c,Matched_Account__c,Matched_Account__r.Name,Matched_Account__r.OwnerId,Website,OwnerId,Owner.Name,Owner.UserRole.Name,Company FROM Lead where IsConverted=FALSE AND (Part_of_Prospecting_List__c=TRUE OR Synced_with_HubSpot__c=TRUE) AND Matched_Account__r.Website LIKE \''+domain+'%\'';
        List<Lead> leadListLMwebsite = new List<Lead>();
        leadListWebsite = Database.query(leadsLMwebsite);
        system.debug('leadListLMWebsite '+leadListLMWebsite.size());
        for(Lead l :leadListLMwebsite){
            leadSet.add(l.Id);
            whoSet.add(l.Id);
            repSet.add(l.OwnerId);
        }
        system.debug('repSet '+repSet);
        List<Lead> getRelated = new List<Lead>([SELECT Id, Matched_Account__c,Matched_Account__r.Name,Matched_Account__r.OwnerId FROM Lead WHERE Id IN :leadSet]);
        for(Lead l :getRelated){
            repSet.add(l.Matched_Account__r.OwnerId);
            accSet.add(l.Matched_Account__c);
        }

        leadSize=leadSet.size();

        String accQuery = 'SELECT Id,OwnerId,Name,Owner.UserRole.Name,Owner.Name,Territory_ID__c,Company_Size__c,Website,Domain_for_lead_conversion__c FROM Account WHERE Domain_for_lead_conversion__c LIKE \''+domain+'%\'';
        accList = Database.query(accQuery);
        for(Account a :accList){
            accSet.add(a.Id);
            repSet.add(a.OwnerId);
        }

        String accWebsite = 'SELECT Id,OwnerId,Name,Owner.UserRole.Name,Owner.Name,Territory_ID__c,Company_Size__c,Website,Domain_for_lead_conversion__c FROM Account WHERE Website_Domain__c LIKE \''+domain+'%\'';
        List<Account> accWebsiteList = Database.query(accWebsite);
        for(Account a :accWebsiteList){
            accSet.add(a.Id);
            repSet.add(a.OwnerId);
        }
        accSize=accSet.size();

        String conQuery = 'SELECT Id,AccountId,OwnerId,Account.Name,Name,Owner.Name,Owner.UserRole.Name,Domain__c FROM Contact WHERE Domain__c LIKE \'%'+domain+'%\'';
        conList = Database.query(conQuery);
        for(Contact c :conList){
            conSet.add(c.Id);
            whoSet.add(c.Id);
        }

        ocrList = new List<OpportunityContactRole>([SELECT Id,ContactId,Contact.OwnerId,Opportunity.OwnerId,OpportunityId FROM OpportunityContactRole WHERE ContactId IN :accSet]);
            for(OpportunityContactRole ocr :ocrList){
                conSet.add(ocr.ContactId);
                whoSet.add(ocr.ContactId);
                whatSet.add(ocr.OpportunityId);
                repSet.add(ocr.Opportunity.OwnerId);
            }
        conSize=conSet.size();
            oppList = new List<Opportunity>([SELECT Id,Name,Account.Name,AccountId,Amount,CloseDate,Territory_ID__c,Company_Size__c,OwnerId,Owner.Name,Owner.UserRole.Name,Type FROM Opportunity 
                                             WHERE CloseDate>=TODAY AND IsClosed=FALSE AND AccountId IN :accSet]);
            for(Opportunity o :oppList){
                oppSet.add(o.Id);
                whatSet.add(o.Id);
                repSet.add(o.OwnerId);
            }

        //find related tasks
        openTasks = new List<Task>([SELECT Id,OwnerId,Owner.Name,Owner.UserRole.Name,ActivityDate,Type,Subject,Who.Name,What.Name,WhoId,WhatId FROM Task 
            WHERE OwnerId IN :repSet AND (WhoId IN :whoSet OR WhatId IN :whatSet) AND ActivityDate>=TODAY AND Status !='Completed' AND (NOT Subject LIKE '%Automated%') AND (Type LIKE 'Call%' OR Type LIKE 'Email%' OR Type = 'Follow up') ORDER BY ActivityDate LIMIT 50]);

        for(Task t :openTasks){
            repSet.add(t.OwnerId);
            openTaskSet.add(t.Id);
        }
        existingTasks = new List<Task>([SELECT Id,OwnerId,Owner.Name,Owner.UserRole.Name,ActivityDate,Type,Subject,Who.Name,What.Name,WhoId,WhatId FROM Task 
                                        WHERE OwnerId IN :repSet AND (WhoId IN :whoSet OR WhatId IN :whatSet) AND ActivityDate<=TODAY AND ActivityDate>=LAST_N_DAYS:120 AND Status ='Completed' AND (Type LIKE 'Call%' OR Type LIKE 'Email%' OR Type = 'Follow up') ORDER BY ActivityDate LIMIT 50]);

        if(existingTasks.size()!=0){
            for(Task t :existingTasks){
                repSet.add(t.OwnerId);
                existingTaskSet.add(t.Id);
            }
        }
        
        accSet.remove(null);
        repSet.remove(null);
        repSet.removeAll(notSalesSet);
        repSize=repSet.size();
        oppSize=oppSet.size();
        accSize=accSet.size();


        //short lists
        conShortList = new List<Contact>([SELECT Id,AccountId,OwnerId,Account.Name,Name,Owner.Name,Owner.UserRole.Name,Domain__c FROM Contact WHERE ID IN :conSet ORDER BY LastModifiedDate DESC LIMIT 5]);
        conList1K = new List<Contact>([SELECT Id,AccountId,OwnerId,Account.Name,Name,Owner.Name,Owner.UserRole.Name,Domain__c FROM Contact WHERE ID IN :conSet ORDER BY LastModifiedDate DESC LIMIT 999]);
        oppShortList = new List<Opportunity>([SELECT Id,Name,Account.Name,AccountId,Amount,CloseDate,Territory_ID__c,Company_Size__c,OwnerId,Owner.Name,Owner.UserRole.Name,Type FROM Opportunity WHERE ID IN :oppSet ORDER BY CloseDate ASC LIMIT 5]);
        existingTaskShortList = new List<Task>([SELECT Id,OwnerId,Owner.Name,Owner.UserRole.Name,ActivityDate,Type,Subject,Who.Name,What.Name,WhoId,WhatId FROM Task 
                                                WHERE OwnerId IN :repSet AND (WhoId IN :whoSet OR WhatId IN :whatSet) AND ActivityDate<=TODAY AND ActivityDate>=LAST_N_DAYS:120 AND Status ='Completed' AND (Type LIKE 'Call%' OR Type LIKE 'Email%' OR Type = 'Follow up') ORDER BY ActivityDate DESC LIMIT 5]);
        existingTasks1K = new List<Task>([SELECT Id,OwnerId,Owner.Name,Owner.UserRole.Name,ActivityDate,Type,Subject,Who.Name,What.Name,WhoId,WhatId FROM Task 
                                          WHERE OwnerId IN :repSet AND (WhoId IN :whoSet OR WhatId IN :whatSet) AND ActivityDate<=TODAY AND ActivityDate>=LAST_N_DAYS:120 AND Status ='Completed' AND (Type LIKE 'Call%' OR Type LIKE 'Email%' OR Type = 'Follow up') ORDER BY ActivityDate DESC LIMIT 999]);
        openTaskShortList = new List<Task>([SELECT Id,OwnerId,Owner.Name,Owner.UserRole.Name,ActivityDate,Type,Subject,Who.Name,What.Name,WhoId,WhatId FROM Task 
                                            WHERE OwnerId IN :repSet AND (WhoId IN :whoSet OR WhatId IN :whatSet) AND ActivityDate>=TODAY AND Status !='Completed' AND (NOT Subject LIKE '%Automated%') AND (Type LIKE 'Call%' OR Type LIKE 'Email%' OR Type = 'Follow up') ORDER BY LastModifiedDate DESC LIMIT 5]);
        openTasks1K = new List<Task>([SELECT Id,OwnerId,Owner.Name,Owner.UserRole.Name,ActivityDate,Type,Subject,Who.Name,What.Name,WhoId,WhatId FROM Task 
                                      WHERE OwnerId IN :repSet AND (WhoId IN :whoSet OR WhatId IN :whatSet) AND ActivityDate>=TODAY AND Status !='Completed' AND (NOT Subject LIKE '%Automated%') AND (Type LIKE 'Call%' OR Type LIKE 'Email%' OR Type = 'Follow up') ORDER BY ActivityDate DESC LIMIT 999]);
        leadShortList = new List<Lead>([SELECT Id,Name,Company,Domain__c,Website,Territory_ID__c,Company_Size__c,Part_of_Prospecting_List__c,Matched_Account__c,Matched_Account__r.Name,OwnerId,Owner.Name,Owner.UserRole.Name FROM Lead WHERE Id IN :leadSet ORDER BY LastModifiedDate DESC LIMIT 5]);
        leadList1K = new List<Lead>([SELECT Id,Name,Company,Domain__c,Website,Territory_ID__c,Company_Size__c,Part_of_Prospecting_List__c,Matched_Account__c,Matched_Account__r.Name,OwnerId,Owner.Name,Owner.UserRole.Name FROM Lead WHERE Id IN :leadSet ORDER BY LastModifiedDate DESC LIMIT 999]);
        accShortList = new List<Account>([SELECT Id,OwnerId,Name,Owner.UserRole.Name,Owner.Name,Territory_ID__c,Company_Size__c,Website,Domain_for_lead_conversion__c FROM Account WHERE ID IN :accSet ORDER BY LastModifiedDate DESC LIMIT 5]);
        accList1K = new List<Account>([SELECT Id,OwnerId,Name,Owner.UserRole.Name,Owner.Name,Territory_ID__c,Company_Size__c,Website,Domain_for_lead_conversion__c FROM Account WHERE ID IN :accSet ORDER BY LastModifiedDate DESC LIMIT 999]);
        existingTaskSize=existingTasks1K.size();
        openTaskSize=openTasks1K.size();
    }
        public pageReference clear(){
        leadList.clear();
            return null;
    }
    
    //method for Show More (leads)
    public pageReference showMore(){
        PageReference leadPage = new PageReference('/apex/showMore');
        return leadPage;
    }
        //method for Show More (Contacts)
    public pageReference showMoreContacts(){
        PageReference conPage = new PageReference('/apex/showMoreContacts');
        return conPage;
    }
        //method for Show More (Accounts)
    public pageReference showMoreAccounts(){
        PageReference conPage = new PageReference('/apex/showMoreAccounts');
        return conPage;
    }
            //method for Show More (open tasks)
    public pageReference showMoreOpenTasks(){
        PageReference openPage = new PageReference('/apex/showMoreOpenTasks');
        return openPage;
    }
            //method for Show More (existing tasks)
    public pageReference showMoreExistingTasks(){
        PageReference exPage = new PageReference('/apex/showMoreExistingTasks');
        return exPage;
    }    
}

 
Khan AnasKhan Anas (Salesforce Developers) 
Hi Jean,

Please refer to the below link with a similar discussion which might help you with the above issue.

https://salesforce.stackexchange.com/questions/15763/advice-on-speeding-up-visualforce-pages

Also, please refer to this doc provided by Salesforce to improve the performance of visualforce page:

https://developer.salesforce.com/docs/atlas.en-us.pages.meta/pages/pages_best_practices_performance.htm


I hope it helps you.

Kindly let me inform if it helps you and close your query by marking it as solved so that it can help others in future.

Thanks and Regards,
Khan Anas​