Use SPQuery.RowLimit also on large lists with small query results?

Question

I frequently read one should use RowLimit on SPQuerys on a large list, especially with more than 2000 items.

Best Practices: Common Coding Issues When Using the SharePoint Object Model:

An SPQuery object without a value for RowLimit will perform poorly and fail on large lists. Specify a RowLimit between 1 and 2000 and, if necessary, page through the list.

That doesn't make sense to me, as I think it should be "if you have a query with a huge result".

Can someone explain to me why one should use RowLimit even if the query returns no more than about 10 items? (eg on a list with thousand tasks and filtering for open tasks of the current user) Or are all the Best Practices telling wrong informations due to imprecise phrases?

Answer 1

Use SPList.GetItems(SPQuery query) instead. Apply filters, if appropriate, and specify only the fields you need to make the query more efficient. If the list contains more than 2,000 items, you will need to paginate the list in increments of no more than 2,000 items. The following code example shows how to paginate a large list.

This implies that you need to paginate if you have a large list. It says this in the link provided. This rowlimit is only for pagination, and doesn't make sense for smaller lists.

Answer 2

Even if you write a query which will fetch 10 items from a list with large number of items, the Query will have to move through all those items and then determine which items pass your filtering criteria.

So fetching 10 items from a list with total 100 items will always be faster than fetching 10 items from a list with total 1000 items.

In your case, the query will have to move through all the tasks to determine which are open and which are closed right?