Sitecore Tactics

Recently we came across a strange behaviour of Sitecore Media streaming in MediaCache that "Overwritten media files are not getting reflected after publish". Just to have clear idea, this is not a browser caching issue mentioned in Sitecore KB article.

What's the issue?

We created a media item on CM and published it, and was visible on live site. Now we overwritten a new media file to the same media item and published it again. (Either using Detach/Attach from Content Editor or using Overwrite existing media from Page Editor.) Surprisingly, we were still getting older media file! We published again, again and again, but newly published media not getting updated.

And yet, this is a very random issue and occurs very rarely.

How we tried to troubleshoot?

We have multiple servers in cluster with 2 target databases. We found that few servers of both target databases are serving older media file and rest of them serving latest one.
Then we thought there might be some Item Path Cache or Item Cache clearing issue (Which happens on Sitecore some times). So, we cleared both these caches for this media item using Sitecore API. But result was same.
Then we cleared whole Item Cache and Data Cache using Sitecore API. The result was same.
Then we cleared All Sitecore Caches using http:///sitecore/admin/cache.aspx page. The result was same.
Final option we had to clear all media cache physical files (Website\App_Data\MediaCache) so that Sitecore will create new media cache from database and can serve latest one. Even after deleting all files and folder from it, new files got generated but still were older one.

So, no solution at all after applying these many tricks!

How we fixed?

We had no other option but recycling the Application Pool. Finally, the master key worked for us. :)

What we concluded and what's the solution?

The only conclusion we had that Sitecore is storing media files somewhere in Server memory as well. Strange, right?

We raised to Sitecore Support for further investigation. Many thanks to Andrey Krupskiy from support who investigated and confirmed that Sitecore is really storing media files in RAM as well that might have caused this and provided below solution.

There is an internal media cache in RAM. This cache is used when media is not yet saved to the filesystem. Even, if you check code of Sitecore.Resources.Media.MediaCache class, in Reflector, it says the same. Sitecore serves media file RAM before its actual file cache gets generated on disk (might be to serve media faster), which is default behaviour of Sitecore. We can disable this behaviour by changing below configuration in Web.Config.

<setting name="Media.StreamPartiallyCachedFiles" value="false" />

We disabled the Media.StreamPartiallyCachedFiles setting as shown above on CM and CD servers.

Now it has more than a month now, we haven't faced the issue again.

Sitecore provides Item.GetDescendants() function to fetch all descendants of any item. Sitecore APIs always fetch items with their field values (whether we required them or not) either from database or from cache, so chances of more lengthy database query execution or heavy cache clearing or creation. That's the reason why Sitecore APIs like Item.GetDescendants() or database.GetItem() are slower.

It is recommended to use Sitecore APIs for development purposes, but still Database Queries can serve better than APIs to get what exactly we need (item's children/descendants and their field values). Database Query should be used only when we need to fetch lots of items in a single call or we need to read few field values for these items.

In such cases, we can make direct queries on database to get performance improvement and get speedy results.

Different SQL Queries

1. Select all children
2. Select all descendants
3. Get path of item
4. Get any field's value
5. Get item's full details with field types

Below queries are performed on Sitecore Training Sample Site.

Select all children

Below query gets children of item '/sitecore/content/Home'. It's GUID is {4E7AB8B1-6A39-4C8C-BF5B-816F8105BFFD}

SELECT ID, [Name], Created from  [dbo].[Items]
 WHERE [ParentID] = '{4E7AB8B1-6A39-4C8C-BF5B-816F8105BFFD}'

Select all descendants

Below query gets descendatns of item '/sitecore/content/Home'. It's GUID is {4E7AB8B1-6A39-4C8C-BF5B-816F8105BFFD}

WITH [Content_Items] AS 
 (
  SELECT [ID], [Name], Created
   FROM [dbo].[Items]
   WHERE ID='{4E7AB8B1-6A39-4C8C-BF5B-816F8105BFFD}'
  UNION ALL
  SELECT  i.[ID], i.[Name], i.Created
   FROM [dbo].[Items] i
   INNER JOIN [Content_Items] ci ON ci.ID = i.[ParentID]
 )
 SELECT ID, Created
 FROM [Content_Items]

 GO

Get path of item

We can also get full path of item. Suppose, we need to get full path of Home or we need to create SiteMap frequently, this query will be a great option to generate FullPath/URL of each item.

Below is the function we can create to get Full/Relative path of any item. Here, SC_GetPath has two parameters. First parameter is ID which is the item's ID. Second parameter is to get relative path, means from this ID, the path will be generated.

CREATE FUNCTION [dbo].[SC_GetPath] 
(
 @ItemID [uniqueidentifier],
 @RootPath [uniqueidentifier]
)
RETURNS varchar(MAX)
AS
BEGIN
 DECLARE @Result varchar(MAX);

 with scpath(Name, ParentID, id)
 as
 (
  select Cast(a.Name as varchar(MAX)), a.ParentID, a.ID
  from [Items] a
  where a.ID = @ItemID
  union all
  select Cast(b.Name + '/' + c.Name as varchar(MAX)), b.ParentID, b.ID
  from [Items] b
    inner join scpath c on b.ID = c.ParentID
  where c.ParentID is not null
 )

 select top 1 @Result = '/' + d.Name  from scpath d
  where d.ID = @RootPath /*'11111111-1111-1111-1111-111111111111'*/

 RETURN @Result

END

Get full item path

Suppose we need to get full path of item Jobs.
ID of Jobs item: {6C897D6C-CE0C-422D-8955-7113A0E1B8EF}
ID of sitecore item: {11111111-1111-1111-1111-111111111111}

Sitecore, we can make query as below to get path /sitecore/content/Home/Jobs: Here,

SELECT [Name], [dbo].[SC_GetPath] (ID,'{11111111-1111-1111-1111-111111111111}') AS ItemPath 
 FROM [dbo].[Items]
 WHERE [ID] = '{6C897D6C-CE0C-422D-8955-7113A0E1B8EF}'

Get relative item path

Now, to get relative path of item Jobs starting from /Home.
ID of Jobs item: {6C897D6C-CE0C-422D-8955-7113A0E1B8EF}
ID of Home item: {4E7AB8B1-6A39-4C8C-BF5B-816F8105BFFD}

Sitecore, we can make query as below to get path /Home/Jobs: Here,

SELECT [Name], [dbo].[SC_GetPath] (ID,'{4E7AB8B1-6A39-4C8C-BF5B-816F8105BFFD}') AS ItemPath 
 FROM [dbo].[Items]
 WHERE [ID] = '{6C897D6C-CE0C-422D-8955-7113A0E1B8EF}'

Get any field's value

Getting item's field value depends on its type of field means it is Versioned, Unversioned or SharedField. Each type of field and its values are stored in different table. So, before getting any field's value, it is good to know its field type and based on it we can get execute query.

See how below function helps to return a shared field's value of an item, similarly we can write function for Versioned and Unversioned fields too:

CREATE FUNCTION [dbo].[SC_GetSharedFieldValue] 
(
 @ItemID [uniqueidentifier],
 @FieldID  [uniqueidentifier]
)
RETURNS nvarchar(max)
AS
BEGIN
 DECLARE @Result  nvarchar(max)
 SELECT @Result = value 
  FROM [dbo].[sharedFields]
  where ItemId=@ItemID
  and fieldid=@FieldID
 RETURN @Result
END

Here, Size field's ID is:{6954B7C7-2487-423F-8600-436CB3B6DC0E}. So, for getting media item's size, we can use above function as below. Here, quert list down all child media media of /sitecore/media library/Images/Banners and its ID is: {1D02B586-0B55-4C2F-AED4-A3172B81B0DA}

SELECT ID, [Name], [dbo].[SC_GetSharedFieldValue] (ID, '{6954B7C7-2487-423F-8600-436CB3B6DC0E}') AS FileSize
 FROM [dbo].[Items]
 WHERE [ParentID] = '{1D02B586-0B55-4C2F-AED4-A3172B81B0DA}'

Get item's full details

Below query will help to get all fields' values and their field types like below:

All Shared field values
All Versioned field values
All Unversioned field values
All Field Types (Like Single-Line Text, Checkbox, Multilist, etc.)

For getting item's all fields values, we have to make a UNION query on all three types of tables of three different field types. See below query to get item's all details:


DECLARE @ItemId uniqueidentifier 
SET @ItemId = '{0DE95AE4-41AB-4D01-9EB0-67441B7C2450}'
DECLARE @FieldTypeId uniqueidentifier 
SET @FieldTypeId = '{AB162CC0-DC80-4ABF-8871-998EE5D7BA32}' -- Item Id of Field Type in each item

SELECT I.Name, [dbo].[SC_GetSharedFieldValue](I.Id, @FieldTypeId) AS [Field Type],
  S.Value, '' AS [Language], '' AS [Version] FROM SharedFields S, Items I 
 WHERE S.itemid=@ItemId
 AND S.FieldID = I.ID
UNION
SELECT I.Name, [dbo].[SC_GetSharedFieldValue](I.Id, @FieldTypeId) AS [Field Type],
  U.Value, U.Language, '' AS Version FROM UnversionedFields U, Items I 
 WHERE U.itemid=@ItemId
 AND U.FieldID = I.ID
UNION
SELECT I.Name, [dbo].[SC_GetSharedFieldValue](I.Id, @FieldTypeId) AS [Field Type], 
  V.Value, V.Language, V.Version FROM VersionedFields V, Items I 
 WHERE V.itemid=@ItemId
 AND V.FieldID = I.ID

This will return all fields' values as below.
- Shared Fields do not contain Language and Version fields, so we have returned blank.
- Unversioned Fields do not contain Version, so have returned blank as Version.

SQL Database queries faster than Sitecore APIs and better than Sitecore fast query, isn't it?