0

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?

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.

0

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);
}
  • Thanks for the reply! How bad would it be if I then did an SPQuery on that SPListItemCollection on page load to build a List<CustomItem> for each user? Would it still security trim the items for the current user? – heilch Oct 8 '14 at 21:10
  • You can do that! Just insert the Generic list instead of the SPListItemCollection. It will still security trim unless you are running under elevated privileges – Michael Colbs Oct 8 '14 at 21:39
  • Edit: Whoops, mind wasn't working, I'll start over. Would this method be very taxing on the server? – heilch Oct 8 '14 at 22:21
  • The caching will increase the performance and only be taxing on the insert – Michael Colbs Oct 8 '14 at 22:56
0
  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.

  • 1. Its set to refresh the cache every time a user adds an item, and clear every morning. This mitigates it but you are right, this is still not ideal. 2.That's a great idea, definitely worth considering. Would each user have to have their own HTML cache since its security trimmed? Seems like I would still have to query the DB for each user then, but at least the data stored is much smaller that it would be otherwise. 3. I am a consultant coming to do a quick clean up, not a re-write unfortunately. At least I'm getting the SPListItemCollection out of Session (yeah.. still makes me cringe) – heilch Oct 8 '14 at 22:17

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.