Tuesday, 3 May 2011

SharePoint 2007 performance issue caused by too many event receivers

If you have a huge number of webs in a SharePoint 2007 site collection with a large number of content types you might have come across some of the more esoteric performance limitations of SharePoint. Regardless of whether you've followed the best practice guidelines for number of sites, lists, list items etc you could fall foul of some rather dodgy SQL in the SharePoint content database.
A few stored procedures are using the wrong data types and perform implicit type conversions resulting in index scans rather than index seeks. In particular slowdowns can occur when workflows are completed and the workflow specific event receivers are being deleted from the EventReceivers table. It is probably worth mentioning at this point that you will not see these issues before your event receivers table has a row count of about a million records, so these particular circumstances will not affect too many. When they do occur though it has the potential to wreak havoc with response times all over the site collection.

The particular installation where I came across this issue had many custom content types inheriting from a base content type to enable a variety of document templates to be used from the New menu in document libraries. Each content type had event receivers defined for a number of events such as ItemAdded, ItemUpdated, ItemCheckedOut, ItemDeleting and so on. All for valid reasons like creating or updating tasks etc. As they all inherited from a single base type and the event receivers ran for all the types using metadata from the base there was no need for each event receiver to be associated with each content type, it could be moved up to the web site level instead. (In SharePoint 2010 you can have site collection event receivers as well so the situation would be even better.) In addition there were custom task types with event receivers as well.

Moving the event receiver associations from content types to web dramatically reduced the row count in the event receiver table. 10 content types with 5 event receivers each means 50 rows per web, moving them up leaves only 5 per web (in 2010 we could get this down to 5 for the whole collection). The total row count for these receivers is reduced from 1 000 000 to 100 000 in this example, and the performance issue would be history.

Moving the event receivers is straight forward using the SharePoint object model.

A console app to run on the app server and iterates through all webs would do the job, so one was created doing all the boilerplate stuff. The interesting bit happens for each web. First all event receivers of the appropriate types will be removed from the document library and task list, before being added back in at the SPWeb level.

private static void ProcessEventReceivers(SPWeb webSite)
//delete all relevant event receivers from document library
 SPDocumentLibrary docList = (SPDocumentLibrary)webSite.Lists["Documents"];   
 for (int i = docList.EventReceivers.Count - 1; i >= 0; i--)
  if (!string.IsNullOrEmpty(docList.EventReceivers[i].Assembly) && docList.EventReceivers[i].Assembly.StartsWith("VS.Sample.SharePoint.Features", StringComparison.InvariantCultureIgnoreCase))
//do the same for task list
 SPList taskList = webSite.Lists["Tasks"]; 
 for (int i = taskList.EventReceivers.Count - 1; i >= 0; i--)
  if (!string.IsNullOrEmpty(taskList.EventReceivers[i].Assembly) && taskList.EventReceivers[i].Assembly.StartsWith("VS.Sample.SharePoint.Features", StringComparison.InvariantCultureIgnoreCase))
//then for each event receiver add it back in
 "VS.Sample.SharePoint.Features.ContentType, Version=, Culture=neutral, PublicKeyToken=58da0e9724238ba7",

//remaining event receivers AddEventReceiver(...) etc
private static void AddEventReceiver(SPWeb site, string className, string assemblyName, SPEventReceiverType eventReceiverType)
 SPEventReceiverDefinition eventReceiverDefinition = site.EventReceivers.Add();
 eventReceiverDefinition.Class = className;
 eventReceiverDefinition.Assembly = assemblyName;
 eventReceiverDefinition.Type = eventReceiverType;
That is pretty much all that was required to move the event receivers up, apart from minor changes in the event receiver code itself to check what type of list the current item belongs to (no point running task specific event receivers for documents and vice versa).

No comments:

Post a Comment