1

We are trying to expose a service in Salesforce using Apex that external systems can call. This service is pretty simple. We will run a query against an object and create a response that returns an array of "coupon codes" stored on each account of a certain record type that has a coupon code.

basically it's just [SELECT Id, Coupon__c FROM Account WHERE Coupon__c != null]

We then will loop through the list and formulate a JSON array and return the coupon codes.

The issue here is we have more than 50,000 rows needing to be returned. So SOQL 101 is an issue. Also heap size could be an issue. We are expecting possibly up to 500,000+ accounts with coupons to be returned.

What are our options here? Should we tell our consumers to call a standard Salesforce API, similar to how you can execute queries in workbench? This would require subsequent calls to retrieve all 500,000 accounts/coupons though, right? Like a query more type of thing?

Also now that we are exposing APIs from Salesforce, I know we have a 24 hour API call limit. Is there a concurrent limit? If we are allowed say, 5,000,000 calls over a 24 hour period, what happens in 4,000,000 all come at the same time? How will the system respond?

Any help would be appreciated. Thanks!

4
  • 2
    Is there a reason you have to return all coupon codes instead of just the ones your client needs? In order to limit the result set being returned? Apr 19, 2022 at 20:53
  • 3
    Your options are documented in Data Integration decision guide. Look for rows where LDV/Bulk = 'Yes'
    – identigral
    Apr 19, 2022 at 21:10
  • @BryanAnderson We are still discussing options with the team that needs to consume this service. Their ask is to expose an endpoint which they can freely access our entire DB of coupon codes. They ask this because they have existing process built with other teams. My thinking is either way they'll need to make changes on their end, but we're trying to accomodate their exisitng build Apr 20, 2022 at 14:59
  • Understood @RyanWerner. Normally when I run into this issue, its usually trying to fit a JSON Schema, rather than trying to return a whole result set of data Apr 20, 2022 at 15:41

1 Answer 1

0

The long and the short of it is that your current approach will not work, full stop. You cannot construct and return a JSON array of 500,000 records in an Apex REST endpoint. You will exceed a wide variety of limits trying to do so (although not, in fact, the 100-SOQL-query limit).

Should we tell our consumers to call a standard Salesforce API, similar to how you can execute queries in workbench?

Possibly. That will ameliorate the 50,000 row limit and the Apex heap limit, but it won't get you around your second question, and as discussed below this integration pattern still doesn't quite make sense.

This would require subsequent calls to retrieve all 500,000 accounts/coupons though, right? Like a query more type of thing?

Yes, it will.


This whole objective doesn't sound quite right to me. If you have a list of a half-million coupon codes that's needed by an external system, making a single API call to retrieve the entire half-million-strong list whenever you need to look at it doesn't make sense and is incredibly inefficient.

Instead, consider:

  • Doing a scheduled sync process from the source-of-truth in Salesforce to the external system via the Bulk API, which is specifically designed to move record data at high volume. Then, have the remote system access it locally.
  • Have the remote system query for what it actually needs to use for a specific application (i.e., "Is coupon code X valid?" or "Does Account Y have an active coupon?"), not the entire data set.
  • Establish a real-time or near-real-time sync from Salesforce into another database system that can filter/aggregate/present the data more appropriately for the consumer system's needs.
  • Have the remote system call a middleware platform that uses the Bulk API to do a retrieval and transforms the data into the desired JSON format (but: also consider whether retrieving the entire data set makes sense).

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.