The Cost of Getting Related Items in Sitefinity’s API

Back when Sitefinity 7 released, one of the biggest enhancements was the addition of related data fields to all content modules.  This had a huge impact on our existing  Sitefinity websites and we rarely build a site anymore that doesn’t use related data to some degree.  Custom slideshows, banners, news, staff directories, and many dynamic modules on our sites use custom data to relate to media and other content types.  Related data is a cornerstone for our Sitefinity websites.

We measured the time that it took Sitefinity’s API to pull related data out of the CMS as a result of assessing performance on a recently launched site’s homepage.  The homepage has a scrolling activity feed that loads more automatically when the user scrolls to the bottom of the page. We decided to build a dynamic module for managing this, since a feed item on a site could be a news article, event, photo gallery, or a video. Having a dynamic module that linked off those types allowed us to consolidate this activity feed into a single module.

To assess the time needed to get all these items and pull out some of their field values, we created a page that listed all the current feed items and logged some data:

As you can tell, there isn’t much going on here. We’re just reading the flat string values from these fields. There were a total of 19,775 items when running this code and it took 25.16 seconds to execute. Not too bad, but we’re also not dealing with related data fields either.  

Next, we added lines for pulling from a related image field and related data (dynamic module) field:

Total time to execute was 11 minutes 47 seconds. This is substantially slower than the previous method, and we’ve only added 2 related data fields!

At this point we determined that there has to be a more efficient way to get this data without having to cast everything into memory just to parse it. GetRelatedItems is a Sitefinity extension method and seemed like the obvious way to get related data, but we figured there must be a faster way. Sitefinity’s database is still just tables after all; we should be able to pull that number of records much faster.

We know that there are really just a few tables we care about. There’s a table for storing the custom fields for the dynamic module (feeditems), there’s a table for storing Dynamic Content specific fields (sf_dynamic_content), a table for the media data (sf_media_content), and a sf_content_link table that manages all of these relationships. Fortunately, there is a ContentLinksManager in the API that lets us see directly into that relationship table, and we can leverage that to speed up the process.

Here’s the equivalent snippet, but using ContentLinksManager for getting those related items. Before looping through anything, we grab IQueryables for the feedItems, sponsors, images, and contentlinks. 

At this point we know we can pull all the data we need, we just need to arrange it correctly. For each item we get, we look up its associated RelatedSponsor and FeaturedImage in the content links table, and then use that link to find the first instance in their associated data tables.   

This snippet has the same result as the earlier example, but finishes in 2 minutes and 47 seconds. While this is a ways off from 25 seconds, it’s quite a bit better than the GetRelatedItems method.  

This is definitely an improvement, but it is not a universal way for getting data out. We’re still just grabbing the first RelatedSponsor and Image, we’re not getting a list of relations, which would require some joining work with those IQueryables, so there’s more work needed for that scenario. It’s also possible to get all related content links straight from a Dynamic Content item, which might be a better way for what you’re trying to do. These are just a few items to keep in mind when dealing with relations and finding the quickest way for your solution.