Performance: Shall I use caml query or loop to get the item count

Question

I have a list which is use to store the users response from a polling app. The list saves response in the following format:

QuestionId | optionChosen

I want to show the users count against each option i.e how many users had chosen option A or B etc. For example, if a poll has 5 options namely A, B, C, D, E.

Currently, I am creating 5 Caml query and firing on the response list to get the count for each.

The other way to do is to get all the items for particular a QuestionId and then iterating the collection for each option to get its count.

Now my question is which one is better in terms of performance, assuming there are 5 options and 100 responses:

1. CamlQuery:
2. Loop:

P.S: List structure can not be change and list is in SharePoint online (does it matters)

Answer 1

IMO, the fastest would be to get all responses for a given QuestionId:

• In both cases, you'll have to retrieve all items for the QuestionId. The total number of items is the same in both cases. So the traffic on the network will finaly be the same, as well as the the memory consumption.
• However, in the latter case (get all items and do the loop yourself): you only have one SQL query, so only one query's "fixed cost" (translating CAML to SQL, reaching SQL, compiling/preparing the request, etc.). And, in case columns are not indexed, SQL will have to loop through rows only once. If columns are indexed, it will go through one index only (QuestionId).
• The latter case also has the benefice that you don't have to know in advance all possible answers: if new options are added later, you can dynamically detect it from your loop and create dynamic groups of answers. The same goes for free-text answers for instance. With the "5 CAML queries" option, you can't do that, since you have to know all possible answers to issue all correct queries.

Also: as this is SharePoint Online (as I've just noticed in your question), keeping network roundtrips as low as possible is another selling point! You're less sure about network latencies when it comes to SPO...

Answer 2

I am not sure about the performance, but my preferred approach would be to fire five CAML queries in parallel. That way you reduce the execution time by approx. 80%.

You should also think about the frequency with which you need to get this data. Although 100 responses might not make much of a difference, for a larger number of responses and a frequency of the query in range of, say, 50 in an hour, I would prefer to setup event receivers on ItemUpdated and ItemAdded to automatically keep track of the responses in a separate list. So when I need to display the data, I can directly read it from this list.