Good day everyone I am trying to insert a new account and contact base on a data from csv file. The concept is that, when an account already exist I will not insert it. What I did is that I add Map<String,Id> variable that will hold all the account.I use the account name as the Map key and its Id as value. Same concept with contact I use the email as Map Key and its Id as value. Everything is working fine. Until one day, I encounter this problem in production. Too many query rows: 50001 the thing that really confuse me is that this error only show in production but in my developer and sandbox, all is working well.what is the possible reason for this ? What is the best solution to avoid this problem in the near future ? Please help...
-
150,000 row is the bulk API limit, if you're hitting this you'll need to break your import into smaller chunks. But have you considered a different solution, such as "Upserting" the Accounts and Contacts using an External Id? If your accounts and contacts are genuinely unique, then you could use the Account Name and Email Address as a unique identifier. Ideally though you'd have another unique identifier in the CSV file you could use?– Alex McDonaldCommented Feb 5, 2016 at 2:54
-
@AlexMcDonald I use external Id at the moment. Do you think that the possible reason for this error is because there are so many accounts or contacts queried ?– HopeCommented Feb 5, 2016 at 2:56
-
If you're using External Id, why are you storing each row in the Map? The Upsert will take care of inserting or updating appropriately for you. According to the Limits (developer.salesforce.com/docs/atlas.en-us.apexcode.meta/…) a SOQL Query can only have 50K results - so it does look like that's the cause. That would explain why sandboxes aren't hitting it as well, just Production– Alex McDonaldCommented Feb 5, 2016 at 3:02
-
Just realised my first comment referred to bulk api limit. My mistake should have been soql result limit– Alex McDonaldCommented Feb 5, 2016 at 3:06
-
@AlexMcDonald The unique identifier is dynamic it is on the user to choose what field will be unique identifier/s. I use the Map to check if the new account to be inserted exist.– HopeCommented Feb 5, 2016 at 3:11
|
Show 1 more comment
1 Answer
Root cause
The root cause of this issue is very straight forward and that is you have query that is returning more than 50k rows. There is limit on how many rows SOQL can return for DML operation and that is 50k.
Why only on prod
Most probably you don't have enough data on your sandbox. Try it on full copy sandbox
How to avoid this error
1) Check where this error is and try to limit the number of rows returned. For ex. if you run query like
Select Id, Name from Contact where email = null
In most of production database above SOQL can return more than 50k rows and result into error
2) See if you can use @ReadOnly annotation
3) See if can use make this process asynchronous.
answered Feb 5, 2016 at 4:15
