Supercharging Salesforce Phone Number Search: Achieving a 300% Performance Boost with Parameterized Search

When I was tasked with finding a matching account in Salesforce based on a phone number, I quickly ran into a significant latency issue—the query times ranged from 3.5 to 7 seconds, which was utterly unacceptable for the performance standards we needed. This was part of legacy code, which relied heavily on SOQL queries. I'd like to take you through the challenges we faced and how we ultimately achieved a 300% improvement in the query performance.

The Problem
The initial implementation used nested SOQL queries to search for accounts based on phone numbers. Here’s a simplified version of the original code:

String phone = '+1234567890';

// Query 1: Find match in contact
Account[] accounts = [SELECT Id FROM Account WHERE Id IN 
                      (SELECT Id FROM Contact 
                       WHERE phone =: phone)  
                     ];

// Query 2: Find match in account
accounts = [SELECT Id FROM Account WHERE Phone =: phone];

return accounts;

This approach had several critical flaws:

1. Sub-Queries: These sub-queries added unnecessary complexity and were vital to the high latency.
2. Nested If Statements: The reliance on nested if-else statements introduced multiple layers of conditional logic, further slowing down the process.
3. String Matching: In SOQL, the WHERE condition treats phone numbers as strings. This means that the phone number stored in Salesforce must match the exact format of the query string. For example, the query would return zero records if a phone number is stored as (123) 456-7890 but queried as `+1234567890`.

Initial Attempts to Fix the Issue

Solution 1: Standardize Phone Numbers in Salesforce
My first approach was to standardize the phone numbers stored in Salesforce. However, this was quickly deemed impractical. We were dealing with millions of records, and we needed a solution that would be scalable and future-proof.

Solution 2: Creating a Formula Field on the Phone Number
Next, I considered creating a formula field and querying it. This seemed promising initially, but I soon realized that formula fields cannot be indexed, leading to unacceptably long query times.

The Actual Fix: Parameterized Search in Rescue

It became clear that our SOQL-based approach needs to be revisited. We needed a different strategy. Instead of sticking with SOQL, I leveraged Salesforce’s Parameterized Search in the REST API endpoint. This change was a game-changer. It significantly reduced the query result time by avoiding the pitfalls of multiple queries, sub-queries, and the issues associated with string matching and unindexed fields.

Here’s how we implemented it:

ENDPOINT: https://<<your-org>>/services/data/v60.0/parameterizedSearch
HTTP_METHOD: POST
AUTHENTICATION: BEARER TOKEN
BODY: {
  // 'q' here is query dont change this field
  "q": "1234567890",
  // fields look for phone feleds in selected objects
  "fields": [
    "Phone"
  ],
  "sobjects": [
    // searching in Account Phone fields returning Id, Name from Account
    {
      "name": "Account",
      "fields": ["Id","Name"],
      "where": "Country = 'USA'"
    },
    // searching Phone fields in Contact returning Id, Name, AccountId from 
    // Contact
    {
      "name": "Contact",
      "fields": ["Id","Name", "AccountId"],
      "where": "Account.Country = 'USA'"
    }
  ]
}

By invoking this API from our external system, the results were nothing short of remarkable. This new approach reduced latency by 300%, bringing query times down to under 1 second. Our system became far more efficient and responsive, a crucial improvement for the user experience.

Conclusion

This experience reinforced an important lesson: sometimes, the best solution isn’t simply optimizing existing queries but rethinking the approach entirely. By moving to a parameterized search, I was able to achieve a significant performance boost, ensuring that our system can scale and handle future demands effectively.

Here is the official Salesforce documentation