Probably you already know or maybe you have a notion of that working with large lists in SharePoint cannot be fast. BUT – it depends.
I have read the Microsoft whitepaper on working with large lists some time ago. But I was not fully aware of what this means. Ok, using GetItemById is slow – but if I cache the result afterwards it should be ok, right?
That depends. With a few items in the list it works fine. With large lists it does not. I have made some measurements on my dev image with 1200 list items and three different implementations:
1. GetItemById method
0.778081 s
0.653073 s
2. CAML query
0.039772 s
3.1 PortalSiteMapProvider 1. hit
0.160483 s
0.049452 s
0.045379 s
3.2 PortalSiteMapProvider consequetive hit
0.000576 s
0.000333 s
The code for approach (1.) took 800 ms to execute on a state-of-the-art WFE server under little load with 2400 items in the list. This is way too long. So I implemented the standard PortalSiteMapProvider approach instead:
PortalWebSiteMapNode webNode =
PortalSiteMapProvider.WebSiteMapProvider.FindSiteMapNode
("/", SPContext.Current.Web) as PortalWebSiteMapNode;
SPQuery query = new SPQuery();
query.Query = SOME_QUERY;
SiteMapNodeCollection items =
PortalSiteMapProvider.WebSiteMapProvider.GetCachedListItemsByQuery
(webNode, SOME_LIST, query,
SPContext.Current.Web);
foreach (PortalListItemSiteMapNode item in items)
{
if (item[SOME_FIELD] != null)
// Do some stuff.
}