Best way to cache large SPList for users with security trimming

Question

The scenario:

There are performance problems around viewing a large list with several thousand items. I want to cache it to reduce the query to the database. Users need to be able to see this list, but with the security levels they need it security trimmed to only see items they should. The ASPX page GridView takes a data source of List. The requirements want all items displayed on the page so pagination is not an option.

Would it be inefficient to store an SPListItemCollection in cache, then use an SPQuery on it to create a List each time they load the page? I thought about storing this list in Session to reduce the time to make the list, but if the SPList gets updated by another user then the Session list will not be updated. Thoughts?

Answer 1

To start of, caching the SPListItemCollection is a bad practice and you should not implement it at all. You will find all the necessary information at Best Practices: Common Coding Issues When Using the SharePoint Object Model. You should store the data in a DataTable and cache the DataTable instead.

Apart from that, the entire scenario sounds like a really bad idea. In your post you mentioned "performance problems around viewing a large list with several thousand items". If you try to display several thousands of items on a single page, you will have insurmountable problems on the server AND the client.

Even if your server manages to compile the page without slowing down the performance to a crawl, the generated webpage will be so big (in MBs) that IE cannot handle it. Depending on the total size the other browsers will not be able to render such a webpage, but IE is especially bad at it (as with almost everything else).

Moreover, storing MBs of data in cache doesn't mean you will accelerate things! You might actually get the opposite results. Caching is expensive and when you try to cast back to SPListItemCollection the operation becomes expensive memory-wise and CPU-wise. Not to mention of course that even if all of this was not a problem, it would require so much memory on the server that the application pool would have to recycle (and thus empty the cache) every few hours.

I don't know how much data you wish to cache, but I would reconsider the scenario.

Answer 2

You're on the right track! There are no issues with caching an SPListItemCollection. Here's how:

SPListItemCollection oListItems;
string cacheKey = "CachedList";
oListItems = (SPListItemCollection)Cache[cacheKey];
if (oListItems == null)
{
    oListItems = oList.GetItems(query);
    //Insert the result set into the cache for quicker loading
    Cache.Insert(cacheKey, oListItems);
}

Answer 3

1. Any cache solution will have the staleness issue you mention. You cannot have real-time display and cache.

2. Caching the entire collection seems wasteful. I would use the ASP.NET output cache to store the html by user. (User control might actually help here.)

3. ** Teach your users that the no-pagination requirement is killing performance!! ** There is a reason that every search engine shows pages, that is the way it should work. An alternative may be "endless scrolling" as is implemented on FB/Twitter.