For working with large lists it’s good to use PortalSiteMapProvider class. This approach works especially well when you are retrieving list data that does not change significantly over time. When data sets change frequently, the class incurs the performance cost. PortalSiteMapProvider provides an automatic caching infrastructure for retrieving list data. The GetCachedListItemsByQuery method of PortalSiteMapProvider takes a SPQuery object as a parameter, and then checks its cache to determine whether the items already exist. If they do, the method returns the cached results. If not, it queries the list and stores the results in a cache. When data sets change frequently, the class incurs the performance cost of continually writing to the cache in addition to the costs of reading from the database. Consider that the PortalSiteMapProvider class uses the site collection object cache to store data. This cache has a default size of 100 MB. You can increase the size of this cache for each site collection on the object cache settings page for the site collection. But this memory is taken from the shared memory available to the application pool and can therefore affect the performance of other applications. Another significant limitation is that you cannot use the PortalSiteMapProvider class in applications based on Windows Forms. The following code example shows how to use this method.
SPSite objsite = new SPSite("URL")
SPWeb objWeb = objsite.OpenWeb())
// Create the query.
SPQuery curQry = new SPQuery();
curQry.Query = "<Where><Eq><FieldRef Name='Category'/><Value Type='Text'>Hotel</Value></Eq></Where>";
// Create an instance of PortalSiteMapProvider.
PortalSiteMapProvider ps = PortalSiteMapProvider.WebSiteMapProvider;
PortalWebSiteMapNode pNode = ps.FindSiteMapNode(curWeb.ServerRelativeUrl) as PortalWebSiteMapNode;
// Retrieve the items.
SiteMapNodeCollection pItems = ps.GetCachedListItemsByQuery(pNode, "myListName_NotID", curQry, curWeb);
// Enumerate through all of the matches.
foreach (PortalListItemSiteMapNode pItem in pItems)
// Do something with each match.