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
LogeshLogesh 

Reg: Custom Indexing and querying large number of records

Hi Friends,

I'm using an indexed custom field in my soql query to avoid non - selective query error. The query is structured as below

sObjectType customObj_name = [SELECT Id,customfield1__c from customObj WHERE Indexed-customfield__c LIKE 'value%'ORDER BY customfield1__c DESC LIMIT 1];

It worked fine for 7 months. Then suddenly non - selective query error started occuring.

Later I went through the BEST PRACTICES for Handling Large Data Volumes and found that indexed custom field stops working and throws error when it exceeds the threshold limit for 'LIKE' operator in 'WHERE' Clause. That is, the threshold for 'LIKE' operator is 100,000 records of the total records it process. In my scenario, it crosses the threshold and custom indexing stops working and throws error - non selective error.

But My question is when I use the same query in the SOQL for Loop like below

for(customObj__c co: [SELECT Id,customfield1__c from customObj WHERE Indexed-customfield__c LIKE 'value%' ORDER BY customfield1__c DESC LIMIT 1] ){
//code to process
}

Will this process without throwing error?
I learnt that it process records in multiples of batches through internal calls to query() and querymore().
What is the maximum number of records processed by query() calls and querymore() calls, that is called from SOQL - for Loop?
Is there any other way to process the maximum number of records efficiently from a single object?
Just in curiosity, I want to know - What is the maximum number of records, a custom object can hold in Salesforce Unlimited Edition? And is there a way to query all the records efficiently from that kind of custom object?

Thanks,
Logesh

steve456steve456

This happened to me last week.

 

 

First go that object and make sure give that customfield as an external id it should resolve.

 

 

 

 

If the limit of External Ids is reached on that object

 

Call to Salesforce and ask them to do indexing on a field.Theyy are going to do the custom indexing

 

 

 

sfdcfoxsfdcfox

Your "for" loop won't get around the problem, because it's the same as the original query, just passed through an iterator. Talking to support about a custom index is a good idea, but you might also have to figure out a way to be more selective, possibly by the use of a second field that holds a portion of the original string so you can filter based on that smaller sub-string to limit the results.

 

The actual "limit" for the number of records in an object is 52^11 (over 7,500,000,000,000,000,000) per server. The limit in an org for the number of records is the total storage of the organization divided by 2kb. For example, if you have a 1GB limit, you can store 500,000 records at most. Queries start becoming painfully slow if you have too many non-indexed values and you're trying to filter against them (hence the 100k limit). You can query against millions of records with a 10 second or less response time if your query is index-filterable. Queries have a hard limit of 50,000,000 records returned, so any query that exceeds this limit will automatically fail.

 

In all, as long as your filters are okay, you should be fine.

 

Edit: I just re-read your post. In addition, I should mention that the FOR loop does solve one problem: it uses QueryMore to break the query into batches. This is important to know, because it means that you won't use as much Heap at once, reducing the liklihood of breaking that governor limit. However, in Apex Code, except Batch Apex, your limit will still be far lower, typically 10,000 records. So, make sure your filters are okay. And remember, FIELD LIKE "%VALUE" can't use indexes, because indexes start from the front of the field value, so you'll be more likely to receive the non-selective query error if you use a leading wildcard character. If you get stuck, contact Technical Support, and they should be able to help you optimize your specific query.

LogeshLogesh

Thanks friends!! 

I'm using the custom field which is set as external Id in the where condition. I agree with your initial post Fox!! But I went through best practices to handle large data volumes doc and found that, a custom field which is set as external Id act as an indexed field and also we can use LIKE with the indexed field in the 'WHERE' clause. 

But the fact is, 'LIKE' also has threshold limit when using with indexed field, i.e., the threshold limit is 100000 records for LIKE.

 

 

But I found the solution for my problem. The ultimate aim is to set the unique Id similar to Auto number format for my records.

 

The unique Id will need to be in the format like 'NAME - 000000'. So, i need the count of records to set the ID.This is the reason for querying the large data sets.

But I did with creating a list custom setting and I set the initial count value manually by creating a record in the custom setting.

In the code, I query the custom setting to get the current count and assign to the member variable of integer type.

Increment the variable and made the unique Id. Finally update the custom setting record with current counter.

 

I'm going to try in another approach, I'm going to remove the external Id from the custom field and reframe the query as

for(CusObj co: [select count_field__c from cusObj__c where (index removed) custom field__c != null ORDER BY count_field__c LIMIT 1]){}. Because a normal soql query in normal apex would return 50000 records. Here, I need only one record, but the only concern is, will it process more than 50000 records to get a single record. If it works, I'll update the post.

Thanks Fox!! Your post is very informative!!

LogeshLogesh

The reason to set the unique Id through custom logic is, because same object is used to store two different type of records (record type) for two different group of user. Each need their unique ID in their own pattern and only when their record is created.

LogeshLogesh

Hi Friends,

 

I accomplished my scenario using custom settings with an integer field. I'll set the record count in integer field. So, that whenever the record of particular type is inserted, the count is updated in the custom settings.

* Retrieve the count from the custom setting.

*Increment the count and set the unique name like Auto number(Name - 0000) to track the records.

*Advantage: It doesnt involves the cost of querying the server and hitting the limits.

 

Thanks & Regards,

Logesh