Watch Out For SPListItemCollection.Count and Judicious Use of RowLimit
This seemingly innocuous call can be quite dangerous when used incorrectly.
The reason is that this property invocation actually executes the query.
This is OK if you plan on iterating the results because the results are cached, but costly if you don’t plan on iterating the results. The following code sample can be used to test this effect for yourself:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 |
static void Main(string[] args) { using(SPSite site = new SPSite("http://internal.dev.com/sites/oncology")) using (SPWeb web = site.OpenWeb()) { SPList list = web.Lists.TryGetList("General Tasks"); SPQuery query = new SPQuery(); query.RowLimit = 1; query.Query = @" <Where> <Contains> <FieldRef Name='Title'/> <Value Type='Text'>500KB_1x100_Type_I_R1</Value> </Contains> </Where>"; query.QueryThrottleMode = SPQueryThrottleOption.Override; SPListItemCollection items = list.GetItems(query); Stopwatch timer = new Stopwatch(); timer.Start(); Console.Out.WriteLine("{0} items match the criteria.", items.Count); var timeForCount = timer.ElapsedMilliseconds; Console.Out.WriteLine("{0} milliseconds elapsed for count.", timer.ElapsedMilliseconds); foreach (var i in items) { Console.Out.WriteLine("{0} milliseconds elapsed for start of iteration.", timer.ElapsedMilliseconds - timeForCount); break; } } } |
(And of course, you can check the implementation of Count in Reflector or dotPeek)
You will see that the start of iteration will be very fast once you’ve invoked Count once.
Now here is where it gets interesting:
- The total time it takes to execute the query is longer for invoking Count versus just iterating (~3000ms vs ~3200ms, about 5-10% in my tests).
- When I set the RowLimit to 1, I can reduce the time by roughly 40-50% (~1600ms vs ~3200ms for a resultset of 230 out of a list of 150,000 items).
Try it yourself by commenting and uncommenting the RowLimit line and commenting and uncommenting the line that invokes Count.
What does this mean for you? Well, if you don’t need the count, then don’t use it. It’s slower than just iterating the results. Where you plan on iterating the results anyways, don’t invoke Count. If you need the count, you are better off doing a counter yourself in the iteration.
And in a use case where you don’t plan on iterating the result set (for example, checking to see if there is at least one occurrence of a type of object), be sure to set the RowLimit in your query!
