Query Optimisation

Question

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:

And this was from the second query:

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.

Answer 1

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.