Although this was published over a year ago, I just happened to stumble on this today.  It’s definitely a good read for SharePoint developers, specifically those of us who use the Object Model to access list data.  The Working With Large Lists in Office SharePoint Server 2007 whitepaper available from Microsoft covers in gruesome details the following 8 different ways to retrieve list data, and even includes performance metrics for each different option:

• Through the browser
• SPList with For/Each
• SPList with SPQuery
• SPList with DataTable
• SPListItems with DataTable
• Lists Web Service
• Search
• PortalSiteMapProvider

One thing I did want to explicitly point out is the use of the PortalSiteMapProvider to retrieve list data.  Before reading this, I didn’t even know this was an option, but could be a very efficient option for retrieving list data in certain situations. Read the suggestions below to determine if this is the best option for your scenario.  The PortalSiteMapProvider was originally created to help cache content for navigation. However, it also provides a nice automatic caching infrastructure for retrieving list data. The class includes a method called GetCachedListItemsByQuery which first retrieves data from a list based on an SPQuery object that is provided as a parameter to the method call. The method then looks in its cache to see if the items already exist. If they do, the method returns the cached results, and if not, it queries the list, stores the results in cache and returns them from the method call. Here’s a quick example:

public void GetListItemsFromSiteMapProvider() {
    using (SPSite siteCollection = new SPSite("http://server")) {
        using (SPWeb site = siteCollection.RootWeb) {
            string listName = "ListName";
            string camlQuery = "<Where><Eq><FieldRef Name='Title'/><Value Type='Text'>Value</Value></Eq></Where>";

            SPList list = site.Lists[listName];
            SPQuery query = new SPQuery();
            query.Query = camlQuery;

            PortalSiteMapProvider provider = PortalSiteMapProvider.CurrentNavSiteMapProviderNoEncode;
            PortalWebSiteMapNode node = provider.FindSiteMapNode("/") as PortalWebSiteMapNode;                   
            SiteMapNodeCollection results = provider.GetCachedListItemsByQuery(node, listName, query, site);

            foreach (PortalListItemSiteMapNode item in results) {
                // do something for each item
            }

            site.Close();
        }
        siteCollection.Close();
    }
}

Considerations
Using PortalSiteMapProvider in this way can have some drawbacks. The first request for a particular web or page via FindSiteMapNode or GetChildNodes will take longer to fetch than a similar call using the traditional Object Model calls. This is because in this case the caching mechanism must perform a round trip to the database and will often fetch more data than it needs in an attempt to pre-fetch and optimize subsequent requests. Therefore it is not useful to fetch data which is accessed very infrequently via these methods. The benefit associated does not show up until multiple calls have been made for the same data.

In addition, using the above methods to fetch data which changes frequently can also be counter-productive. If the data changes frequently, it will be frequently invalidated and re-fetched from that database negating the benefits of caching. The invalidation mechanism is relatively coarse, with a change to a particular item invalidating any data in the same web as the item.

I bet you didn’t realize there were so many ways just to get list data, huh?  Before beginning a project where you will need to access data from a SharePoint list, read the whitepaper and please consider the different data access methods available to you and make sure you choose the right one, as certain methods are much better than others in various scenarios.