Retrieving large datasets with SData
Posted By: nicocrm on April 5th, 2012 in Saleslogix
No Gravatar

Reading a very large recordset at once is always a good way to cause concurrency problems on the database, and streaming such a dataset over a WAN connection can be problematic as well. Fortunately
SData has built-in controls for paging which helps limit the amount of data returned. Query parameters allow one to control the number of records returned (count) as well as the starting record index (startIndex). In the returned result sets are properties that indicates this as well as the total number of records available: totalResults, startIndex, and itemsPerPage. If you are using the SData C# Client Lib, those properties can be retrieved via the methods of the feed:


feed.GetOpenSearchItemsPerPage();
feed.GetOpenSearchTotalResults();
feed.GetOpenSearchStartIndex();

This is perfect for a grid or any type of user interface where you won’t want the user to see all the data at once anyway. If you are writing a batch process that really does need to iterate over all the records, you need to write a method that will retrieve them page by page – setting the count parameter to a very high value is likely to either create a concurrency problem or simply fail. This is an example method that retrieves all responses for a campaign:

One tricky part I ran into: if the “orderby” parameter is not specified, the order of the returned records is not guaranteed. If it changes half way through the retrieval, eg because SQL Server decided to reorganizes some pages or optimize the query differently, then the retrieved pages will not contain all the data! It’s quite insidious since the method will appear to run fine during testing on small datasets but fail somewhat randomly when run against actual data.