Iterating Lists in Small Chunks And Performance

A couple of months ago I created a post about it and in the conclusion I stated that it would probably be faster to act on the whole list by chunks than it is using the big query… It’s not.

I have done the test with a list of 100K items so you don’t have to and, depending on the size of the chunks it can take slightly longer or a lot longer.

If you are using a very small RowLimit (say 5) the number of times you have to perform the query in order to retrieve the whole list will make the process take twice as long or even longer. If you are using a bigger number (like 1000) the performance will be similar to the one achieved retrieving the whole list and processing it in just one query.

In summary, if you are looking for a small number of values and they are likely to be found in the first iterations of the paginated query then the performance boost will be obvious, in some cases I have noticed an average improvement of up to a 95%, but bear in mind that if you are using very small chunks and you are likely to go through the whole list to get the values back your query will be slower, up to an 400% slower or even more.