0

I am trying to run a SOQL query to the Salesforce database. The database is very large, the target table Contact contains more than 3 million records.

The goal is to read all the records that match the certain criteria (below in code sample).

The issue is when I try to use the LIMIT keyword to perform batch reading, it returns less records than specified in LIMIT, although further records definitely exist. Here is the code sample:

SELECT
    Id,
    UserCode,
    Email
FROM Contact
WHERE Id > 'some_ID_which_is_the_last_in_previous_set' AND UserCode != null
ORDER BY Id ASC
LIMIT 1000

The condition Id > 'some_ID_which_is_the_last_in_previous_set' is used in order to perform "chain requests" - read batches one-by-one, because the OFFSET command support max. 3000 records, which is obviously less than 3 million.

So now the issue itself. If I specify LIMIT 1000 and perform 3 queries, I get only approximately 300 records (~120-140 per request). If I specify LIMIT 100 and perform 30 requests, I get approx. 2900 records. If I specify LIMIT 10 and perform 300 requests, I get approx. 2970 records.

Does anyone know what can cause such weird behavior?

8
  • 1
    Silly question. Did you try to use batch processing instead of this? Jun 16, 2017 at 16:35
  • Are you executing these queries in code? If so, try using the Developer Console query editor or workbench so you can verify if it's an issue with the query or the code it's from.
    – dth02134
    Jun 16, 2017 at 18:15
  • An alternative to using LIMIT could be to use the BulkAPI with PK Chunking: developer.salesforce.com/blogs/engineering/2015/03/… Jun 16, 2017 at 19:12
  • @SebastianKessel, man with silly questions doesn't know that there are other ways of fetching data. There is a knowledge article on Salesforce which makes him think he was going in right direction doing this.
    – Eadel
    Jun 19, 2017 at 12:34
  • @Dan This query is executed from the C# code. The root cause of this "issue" I post under Abhishek's answer. Thank you.
    – Eadel
    Jun 19, 2017 at 12:37

1 Answer 1

1

I would prefer batch class to query and process this large data sets. Not sure about your use case but you can write a batch class, perform the query in start method and use a QueryLocator object, the governor limit for the total number of records retrieved by SOQL queries is bypassed (You can query and process upto 50 million records using Batch Apex). Additionally, you can specify the scope size which will send the chunks of records to execute method in that numbers (by default its 200).

you can find more details about Batch apex here

1
  • Thank you for the answer. I have a client in C# code, and I tried to use the query locator from there. It works fine, but has the same "issue" as the above limit implementation. However, I put "issue" in quotes because this was not a real issue. In fact, I have more complex query which I cut in the question to be brief. And actually my query has dependencies on other tables, so the amount of records in LIMIT was the total amount of records read, not only for my Contact entity. Accept your answer since you pointed me to using query locator.
    – Eadel
    Jun 19, 2017 at 12:42

You must log in to answer this question.

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