18 04 2011
SPList.Items versus the SPQuery
So you’ve embarked on writing a bit of code that must enumerate a SharePoint List’s Items one by one and do some stuff with them. You’ve (at some point) came across the Best Practices page on MSDN on Handling Large Folders and Lists, and seen that using SPList.Items is bad. It’s bad because you could potentially have a very large (tens of thousands to hundreds of thousands of list items) list which could be queried and loaded into server memory potentially several times per second in a busy environment.
So you decide to use the much-loved (or loathed) SPQuery object to store a CAML query and use SPList.GetItems(), passing in your SPQuery. But you still want to iterate over all the items and use all the fields in the list, so you basically pass in the SPQuery with empty properties. All good right? Still follows the best practices document outlined above, right?
What Microsoft have slightly glossed over and neglected to highlight in their documentation is the necessity to use filters (the Query, RowLimit, ViewFields properties) in your SPQuery object. When you call SPList.Items, the object model creates an empty SPQuery object, populates with minimal properties (it only actually sets the Scope to Recursive, such that it gets all items in all folders) and calls GetItems upon itself. By creating yourself an empty SPQuery object, you’re doing the same as SPList.Items.
The documentation only states to apply filers “if appropriate”. This sentence should really emphasise the importance of using Filters, because if you don’t, you’re getting all items.
That page also has some good examples of paginating your results using a combination of RowLimit and ListItemCollectionPosition properties, to ensure you only query to return a small amount of data at a time. This will be particularly important when your web application has Query Throttling enabled.