6

I am dealing with some huge data and while querying I need to by pass many of them. So I am keeping ID in one List and passing them in the SOQL. Example:

List<Id> idlist = new List<Id>();
[Select Id, Name from object1__c where id not in : idList]

idList will have huge set of IDs which I dont want to query.

So i was wondering is there any limit for number of Id which we can use in where clause ? I mean how big my idList can have values? please suggest. I have not received any errors yet but I am being more cautious before putting this code in action.

4 Answers 4

8

Using inline binding circumvents the normal character restrictions of an SOQL statement; as long as you don't exceed the heap size limit, you can include as many values as you like in the list. Here's an execute anonymous script that will query for 161,448 (non-existent) unique Id values:

id[] idlist = new id[0];
String[] keys = 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789'.split('');
for(integer a = 0; a < 42; a++) {
    for(integer b = 0; b < 62; b++) {
        for(integer c = 0; c < 62; c++) {
            idlist.add('001000000000'+keys[a]+keys[b]+keys[c]);
        }
    }
}
// Following query will run fine with 161k Id values
Account[] accounts1 = [select id from account where id = :idlist];
// As will this one
Account[] accounts2 = database.query('select id from account where id = :idlist');

This code essentially generates a query that would require 3,500,000 SOQL characters (minimum), yet doesn't run into any particular limits.

Eventually, depending on what you're doing, you'll run into either heap limits, maximum payload size limits, etc.

When practical, sort your results by Id or CreatedDate, and use that as a simple filter instead. You can read more about doing that in this answer.

4
  • When does the query character limit matter? Is it just for raw text queries, ie, if you ran Database.query() with the text from your query, would it fail? Apr 13, 2018 at 13:43
  • 1
    @battery.cord If you do not use binding, yes, the query character limit matters. database.query('select id from account where id = :idlist') (will not exceed character limits) --- database.query('select id from account where id IN (\''+string.join(idlist,'\',\'')+'\')'); (normal 20k/4k rules apply).
    – sfdcfox
    Apr 13, 2018 at 13:46
  • @battery.cord Actually... the code above worked, though I'm not sure why. I wouldn't rely on it, though.
    – sfdcfox
    Apr 13, 2018 at 13:54
  • I think it really only matters in API contexts? Trying to run a really large query from the developer console gives me the error "Bad Message: URI too long", which seems like a valid reason to limit the size of queries. Apr 13, 2018 at 14:14
4

Here you have 2 important limits:

  1. The maximum characters allowed in a SOQL query is 20K.
  2. The maximum characters allowed in the where clause is 4K.

You can see the reference here: https://developer.salesforce.com/docs/atlas.en-us.salesforce_app_limits_cheatsheet.meta/salesforce_app_limits_cheatsheet/salesforce_app_limits_platform_soslsoql.htm

1
  • This answer is unfortunately incorrect.
    – sfdcfox
    Apr 13, 2018 at 12:57
1

Actually there is no particular limit on the List size it depends on the heap size allocation for synchronous traction you can have upto 6MB where as for asynchronous it is 12MB. So if the heap size limit exceeds then you might have issues otherwise it will work.

But make sure if that list is used for DML operations then not more 10K records should be there because it might throw the governor limit issue for DML operation.

4
  • The SOQL character limit is much more likely to be an issue.
    – David Reed
    Apr 13, 2018 at 12:04
  • Yes and max SOQL character is also an issue.
    – RCS
    Apr 13, 2018 at 12:06
  • 1
    @DavidReed No, the SOQL character limit will not be an issue.
    – sfdcfox
    Apr 13, 2018 at 12:57
  • I stand corrected and learned something new - thanks!
    – David Reed
    Apr 13, 2018 at 13:06
0

That is great information @sfdcfox. I took that information and discovered a limitation in the SOAP API.

If anyone is executing this query through SOAP API, the query method has a 20,000 character limit on the query that you can execute. This just means that you'll need to split up your list before you query and then add each result to your final list of objects.

1
  • 2
    The question is specifically about dynamic binding, a feature not available through the SOAP API. As such, this answer doesn't really directly address the originally posted question.
    – Adrian Larson
    Jun 22, 2018 at 1:24

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .