0

I currently have a function which looks like this:

public void getContacts() {
  try {
    contacts = Database.query(query + ' LIMIT 15');
  }
  catch (Exception e) {
    ApexPages.addMessage(new ApexPages.Message(ApexPages.Severity.ERROR, 'Oops!' + e));
  }
}

Super simple, query the database and it it to a list called contacts.

I also have a function like this:

public PageReference doSearch() {
  String firstName =  ApexPages.currentPage().getParameters().get('firstName');
  String lastName = ApexPages.currentPage().getParameters().get('lastName');
  String department =  ApexPages.currentPage().getParameters().get('department');
  String subDepartment =  ApexPages.currentPage().getParameters().get('subDepartment');
  String withTerms = ApexPages.currentPage().getParameters().get('withTerms');
  String jobTitle =  ApexPages.currentPage().getParameters().get('jobTitle');
  String address =  ApexPages.currentPage().getParameters().get('address');
  String longitude =  ApexPages.currentPage().getParameters().get('longitude');
  String latitude =  ApexPages.currentPage().getParameters().get('latitude');
  String radius =  ApexPages.currentPage().getParameters().get('radius');

  query = 'SELECT Id, FirstName, LastName, Email, Phone, MobilePhone, Account.Name, RecordType.Name, Current_JobTitle__c, Department__c, Sub_Department__c FROM Contact WHERE RecordType.Name = \'Contact\' AND FirstName != null AND LastName != null AND (Phone != null AND Email != null)';

  if (!firstName.equals(''))
    query += ' AND FirstName LIKE \'%' + firstName + '%\'';
  if (!lastName.equals(''))
    query += ' AND LastName LIKE \'%' + lastName + '%\'';
  if (!department.equals(''))
    query += ' AND Department__c = \'' + department + '\'';
  if (!subDepartment.equals(''))
    query += ' AND Sub_Department__c = \'' + subDepartment + '\'';
  if (!withTerms.equals(''))
    query += ' AND Account.Terms_Agreed_yes_no__c = \'' + withTerms + '\'';
  if (!jobTitle.equals(''))
    query += ' AND Current_JobTitle__c LIKE \'%' + jobTitle + '%\'';
  if (!address.equals('') && radius.equals(''))
    query += ' AND (MailingCity LIKE \'%' + address + '%\' OR OtherCity LIKE \'%' + address + '%\')';
  if (!address.equals('') && !longitude.equals('') && !longitude.equals('') && !radius.equals(''))
    query += ' AND DISTANCE(Location__c, GEOLOCATION(' + latitude + ', ' + longitude + '), \'mi\') < ' + radius;

  getContacts();

  return null;
}

This is where "query" is made and filtered. The parameters are passed in from the Visualforce page and the query is modified depending on what the user searches for.

The problem I've got it that it's incredibly slow. If, for example, the user searches just for a department (picklist in Salesforce, select option in the VF page), and the department is added to the filter, the application times out (even with the LIMIT 15!).

getContacts() is called when the page loads which is literally just a query for:

query = 'SELECT Id, FirstName, LastName, Email, Phone, MobilePhone, Account.Name, RecordType.Name, Current_JobTitle__c, Department__c, Sub_Department__c FROM Contact WHERE RecordType.Name = \'Contact\' AND FirstName != null AND LastName != null AND (Phone != null AND Email != null)';

This loads almost instantly.

I was hoping someone would be able to shine a light as to why the performance of this is so slow, and if there are any ways of potentially making it quicker? Why is a broader query quicker than a more specific one?

Edit I have found that by putting in the following:

Id rt = [SELECT Id FROM RecordType WHERE Name = 'Contact' AND SObjectType = 'Contact' LIMIT 1].Id;

String rec = String.valueOf(rt);

And using RecordTypeId = 'rec' has improved the performance of it. Using the Query Plan tool as @KeithC suggested caused the following:

Not considering filter for optimization because it is on a lookup to a record type table, and we may get too much data skew. Table: RecordType Fields: ["Name"]

I figured that might be indexed and that by getting an Id and searching for it, might improve performance. This does seem to have done some good.

This was from the first query:

enter image description here

And this was from the second query:

enter image description here

The cost has gone down and as per the docs Keith C linked:

Values above 1 mean that the query won’t be selective.

I'm not familiar with this tool, but some progress seems to have been made.

  • You can better understand what your queries are doing by using the Query Plan Tool. Perhaps the LIMIT clause is applied last after the expensive large query result set has already been created? – Keith C Nov 18 '15 at 10:19
  • @KeithC Thanks for that, I've updated my answer off the back of what you've written here. – Dan Jones Nov 18 '15 at 11:34
2

Technically your query is slow because you are using a lot of wildcard operators and I would assume the number of records is also quite large.

You should consider changing your approach if possible and use SOSL instead for this purpose.

For future reference there are a lot of interesting resources on how to optimise queries on Force.com, like this very useful cheatsheet.

I hope it helps.

  • The thing is, most of the wildcards aren't used anyway. For example, when I searched for a department which isn't a wildcard, the execution is slow. When invoking wild cards (and being more specific), the performance does seem a lot better strangely. You are right though, SOSL is just something I've been avoiding but may have to use it. – Dan Jones Nov 18 '15 at 11:37

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.