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
wsmithwsmith 

Is there a best practice for allowing a web service to return more then 1000 objects?

Apex arrays have a Governor limit of 1000 elements.  If I have a web service that returns an array of objects, is there a best practice for allowing a web service to return more then 1000 objects/  Perhaps a query more type API where the array returns objects in a range specified by the user.

Is there a best practice for allowing a web service to return more then 1000 objects?
Ron HessRon Hess
1000 is the most

but you can process those 1000 records then get another batch.

is the service you are consuming flexible in how many records you can request?
wsmithwsmith
I was thinking of making an interface along the lines of
Obj[] getObj(Integer startIndex, Integer numberOfObjects); where numberOfObjects has a maximum of 1000.

However this leaves the questions of how do I sort the result set for the best results (by Name, Salesforce Id, Created date, etc.) and most importantly, what happens if the result set changes (by a record update, delete or insert) between web service calls?

Remember that a web service call is stateless and there is ao built in way to save the state or the full result set of the query between web service "query more" calls.

In the case when a record is inserted or deleted, I am concerned the user will either miss a record, when the user asks for the next batch or records, or get a duplicate record in the latter case.

One thought is to look at a "modifiedsince" timestamp in the Object's table but I don't believe the Describe Object (metadata) information contains this information.  If the table is modified based on a timestamp parameter, I can invalidate the query and tell the user to restart the query.

Is this a good way to approach the problem?

Cool_DevloperCool_Devloper

Hello Ron,

I have a question in the same regard for you.

What exactly does the webservice needs to have, to be able to process records in batches of 10,000?

I am currently facing this problem as my results are way above 10,000 in numbers which leads to exception. Would be obliged if you can throw some light on this.

Thank You,

Cool_D

philbophilbo
Hey,

Our strategy is to return lists of lists of objects.  Theoretically this allows a return set of up to 1,000,000 records, but practically you hit the 1 MB heap size governor limit long before that.

In the event where an iterative approach can't be avoided, we do a paged approach, limiting the result set to some maximum size (1,000 recs is convenient) and including a new field in the results to tell the caller where we left off.  This field is passed back into the same WebService method in subsequent calls, telling the method where to pick things up.

Clearly, sorting is required at some point - on the field being passed back + forth, as well as a conditional on the underlying field in the SOQL query sitting in the WebService method.  What field this is, is an implementation-specific decision.  You'd naturally think that the Id field would be the ideal candidate, as it's unique and ubiquitous, but unfortunately the following SOQL is illegal:

[ select Name from Object where Id > :startId order by Id limit 1000 ]

So you have to order by some other field, unfortunately.  (Anybody know why you can't do compare ops on the Id field in a SOQL where clause?)
Cool_DevloperCool_Devloper

Thanks a ton Phil for your splendid reply. That was very useful indeed.

Unfortunately, i still have one more issue. I have 4 SOQL queries inside my webservice method. So, when my code is executed, all of these queries are included in the context. The overall cumulative count thus goes beyond 10,000 giving me an exception within the code itself that number of records fetched is 10,001.

This basically means that within my code also i need to do batching. Am i correct here?

Is there a way to handle such kind of a scenario? Looking forward to you reply.

Thanks

philbophilbo
Well, it depends on the details of what you're trying to do, of course, but essentially you need to constrain the size of your SOQL result sets with appropriate limit clauses.  If you have multiple SOQL queries, then you probably need to have some method-specific logic to constrain each appropriately.  Not much of an answer for you - but again, it very much depends on the details of what's going on within your WebService method.
Cool_DevloperCool_Devloper
Ok Phil .... i understand your point here.
 
But in case, still the overall count goes above 10,000 ....  then is there any way out?
 
Can this be handled?
 
Thanks
philbophilbo
Only programmatically.  Keep track of how many records you've retrieved, or use the Limits.getQueryRows() and Limits.getLimitQueryRows() calls to quickly calculate how much room you've got left, and use that info to tune the 'limit X' clause in your subsequent SOQL queries.
Cool_DevloperCool_Devloper
Thanks Phil. That was really helpful.
 
So, what i understand is that i can keep a track of how much of that record count, i have already used.
 
But in such a case, what will happen to the records which have been left as the limit is reached? I am sorry for being so ignorant Phil, but just starting off with these SOQL limits and webservices :(
 
Thanks a lot for your help and clarifications. It really helps!!
 
Regards,
Cool_D
philbophilbo
Well - I guess you have to craft your method with limit clauses on all your SOQL queries, using those Limits() methods to guide what you set those SOQL limits to.  Presumably you're going to reach a point where you've reached the SOQL record limit - you've retrieved as many records as you're allowed to - but you haven't retrieved the entire data set you're interested in.  This is where you have to preserve state somehow - keep a 'cursor' (in VERY rough terms) so that the next invocation of the method can pick up where this one left off in its SOQL queries.  The way we preserve this state is to pass information back to the caller, so that it can 'seed' the next call with that info.

I hope that makes sense.  How exactly you would go about implementing this - is entirely dependent on the details of your method.

Remember, that 10,000-record limit is on a call-by-call basis.  Each invocation of your WebSvc method gets a brand new 10,000 records to play with.
HariPHariP
Hi,

I have similar issue.

I have webservice program to return data from 2-3 objects. I implemented this using LIMIT and OFFSET to give user an option to query different set of records. When I ran test query in developer console with LIMIT 400 and OFFSET 0, it works fine.

But when I run this webservice using SOAPUI, it returns only 200 records. Any idea?

Thanks
Hari