Sitecore's "Latest" Item

Question

A general architecture question in Sitecore 6...

Let’s say we have a situation where we have 10,000 items implementing a “Press Release” template. These items are stored in Sitecore at /sitecore/content/home/press/*. On our homepage, we’d like to display some information concerning the 3 most recent press releases.

We’re looking to construct something equivalent to SQL’s:

SELECT TOP 3 * FROM PressReleases ORDER BY ReleaseDate

Reading through the Sitecore query documentation, it sounds like the majority of this query must be handled in our C# application. Something like:

public Item[] GetRecentPressReleases()
{
   string query = "/sitecore/content/home/press/*";
   Item[] items = Sitecore.Context.Database.SelectItems(query);
   Array.Sort(items, new PressReleaseDateComparer());
   return items.Take(3).ToArray();
}

It would seem that loading 10,000 Sitecore items from the database into memory and then sorting them every time our homepage is hit would be unacceptable from a performance perspective.

Is there a more efficient way to express this query? Or should I be focused on output caching and/or pre-calculating?

Answer 1

Sitecore Query (or a fast query) does not support sorting or TOP constructs, so these things have to be expressed in code.

Focusing on caching is a good thing. Using standard Sitecore rendering caching is a simplest approach, I don't think you need anything more complex than that in this case.

It helps to understand that Sitecore query can be resolved either at the SQL or API levels, which does affect the performance and can sometimes be used to your advantage.

Fast query is built into Sitecore 6 and makes all queries execute at SQL level, dramatically increasing the performance. It also doesn't support sorting and TOP at the moment, but we're considering how this can be added.

Answer 2

One solution to the "10 latest news" problem is to use Lucene.

This is one way of doing it.