function readOnly(count){ }
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
+ Start a Discussion
Rajendra Prasad 44Rajendra Prasad 44 

how to escape soql limits in trigger

hey, I need a small help. I'm working on a lead duplication trigger. there I need to fetch all the existing person accounts and leads to check duplicate. but I have 69k records in-person account. it will through soql exception. so how can I achieve this? I need to show error message on duplication
ANUTEJANUTEJ (Salesforce Developers) 
Hi Rajendra Prasad, 

I think instead of getting all the records wouldn't it be possible to get only those records with matching field value that you can add to a list to use it as a filter in soql?

Looking forward for your response.

AnudeepAnudeep (Salesforce Developers) 
There are few options

Batch Apex

In general, the best way to query and process large data sets in the platform is to do it asynchronously in batches. You can query and process up to 50 million records using Batch Apex.

Batch Apex doesn't work in all use cases (for example, if you have a synchronous use like as a Visualforce page that needs to query more than 50,000 records), but it’s a great tool to have in your toolkit.

Bulk Queries

Another strategy to efficiently query large data sets is to use bulk queries. A bulk query can retrieve up to 15 GB of data, divided into fifteen 1 GB files.

Bulk API query supports both query and queryAll operations. The queryAll operation returns records that have been deleted because of a merge or delete. The queryAll operation also returns information about archived Task and Event records.

When adding a batch to a bulk query job, the Content-Type in the header for the request must be either text/csv, application/xml, or application/json, depending on the content type specified when the job was created. The actual SOQL statement supplied for the batch is in plain text format.

The Query Optimizer

Because Salesforce uses multitenant architecture, the database system’s optimizer can’t effectively optimize Salesforce queries unaided. So the Salesforce platform includes its own query optimizer that takes advantage of indexed fields to create the most efficient execution plan for a given query, helping the database system’s optimizer produce effective execution plans for Salesforce queries.

The query optimizer maintains a table of statistics about the distribution of data in each index. It uses this table to perform pre-queries to determine whether using the index can speed up the query. It works on the queries that are automatically generated to handle reports, list views, and both SOQL queries and the other queries that piggyback on them.

I recommend reviewing this document to learn more