I have not been actively hands-on with Sitecore lately. But once in a while I come across a question that sounds like a good puzzle to roll up my sleeves for, and then I just can’t help it.

Query

One of our engineers posted a question. Their client’s CM instance was running noticabely slow and the users were complaining. They quicky identified the bottleneck with the SQL profiler but the finding puzzled them:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24

IF EXISTS (SELECT NULL 
           FROM [SharedFields] WITH (NOLOCK) 
           WHERE [SharedFields].[Value] LIKE @blobId)
BEGIN
  SELECT 1
END
ELSE IF EXISTS (SELECT NULL 
                FROM [VersionedFields] WITH (NOLOCK) 
                WHERE [VersionedFields].[Value] LIKE @blobId)
BEGIN
  SELECT 1
END
ELSE IF EXISTS (SELECT NULL 
                FROM [ArchivedFields] WITH (NOLOCK) 
                WHERE [ArchivedFields].[Value] LIKE @blobId)
BEGIN
  SELECT 1
END
ELSE IF EXISTS (SELECT NULL 
                FROM [UnversionedFields] WITH (NOLOCK) 
                WHERE [UnversionedFields].[Value] LIKE @blobId)
BEGIN
  SELECT 1
END

Who Are You

I have once traversed basic item APIs all the way down to data providers and back so I just knew where to look. SqlServerDataProvider in Sitecore.Kernel has a method with a very telling name that runs this query.

The name of the method is - GetCheckIfBlobShouldBeDeletedSql(). Walking up the usages chain I found who runs it:

1
2
3
4
5
6
7
8
9

public override bool SaveItem(...)
{
  // ...
  if (Settings.RemoveUnusedBlobsOnSave)
  {
    ManagedThreadPool.QueueUserWorkItem((state => this.RemoveOldBlobs(changes, context)));
  }
  // ...
}

Every item save will call RemoveOldBlobs() that will end up running the mentioned SQL query if RemoveUnusedBlobsOnSave is set to true.

The method runs asynchronously so it doesn’t directly impact the executing thread, but it does put pressure onto the SQL server. Running LIKE logic looking for GUIDs (even without %) in a non-indexed nvarchar field across mutliple tables will take some cycles.

Recommendation

It’s good that this logic is protected with a feature toggle.

I suggested that the team turns off Settings.RemoveUnusedBlobsOnSave and contacts Sitecore Support.

This behavior was observed in 8.1 Update 2. I opened 8.0 Initial Release just out of curiosity and SaveItem() doesn’t go looking for old BLOBs. I didn’t go through more recent releases but it has got to be a relatively new addition. Probably added for a reason.

If we turn off running it on every item save, when should we run it? Maybe it’s missing the ID of the saved item in the WHERE to make it a lot more specific? Don’t know. I will update this post if/when we hear back from the support team.

In part 1 of this series I made an argument that Sitecore needs a new field type that would support workflow and versioning without adding language variance. Let’s see why.

Presentation

Presentation details were Shared in older versions of Sitecore . It means no versioning, no workflow support, and no language variance. A page item under workflow will not publish its latest version until the workflow reaches the final state. Too bad for the Shared fields though. Once modified, the change will be picked up by the smart or full publish. A threat is very much real - just like I hope you are using workflows, I also hope that you are using scheduled publishing agents and don’t let your authors work as admins.

Versioned Layouts

Sitecore 8 introduced versioned layouts. Presentation details can now be both Shared and Final and the end result is merged at run time. The promise of versioned layouts is to workflow-enable the presentation details and to also allow language variance. The problem is - you can’t get one without the other.

One Language

I stand corrected. You actually can have workflow support without language variance. Just don’t translate your content. If your site only supports one language, you’ll enjoy using versioned layouts. I would even suggest that you forego the good old __Renderings (Shared layout) altogether to ensure proper versioning and workflow support of your presentation. Rejoice!

Multilingual

There are more than one way to build a multilingual site in Sitecore. Previously, if your layout was the same across languages you could safely translate a single content tree. Now you can do even more with a single content tree thanks to version layouts that can easily accommodate certain language variances. If the content varies significantly and translated sites feel more like distinct online properties, you will probably build parallel content structures but let’s focus on a more common example.

Single content tree. Translated. Under a workflow. Scheduled publishing agents. Best practices. Right?

A change to the Shared portion of the layout is susceptible to the same accidental publishing as the entire layout in older version of Sitecore.

Understood. Can we use final layouts?

A change to the Versioned portion of the layout, while workflow controlled and versioned, only affects a single language.

Wait a minute. Can I or can I not safely and soundly workflow control my layout?

The answer is - it depends. If your presentation details are exactly the same across all translations, then you probably can. You will need language fallback and a little discipline. Language fallback will propagate the value from one language to another provided that the value in another language is null. An empty layout is not null (try resetting it and look at the raw value if you wonder what it is) so there goes the first wrinkle. Any accidental (or not) change to the layout in Experience Editor done not in context of the primary language will break the fallback chain.

Tough. And what if you have a layout variance in a given language?

The Missing Field Type

Well, like I said, Sitecore needs another field type - Workflowed. And I wouldn’t worry about migration issues to be honest. One of the recent updates changed the way clones are handled. A breaking change indeed. Migration instructions included a simple SQL script to upmigrate all clones. Easy, no big deal. Same could be done to legacy __Renderings if field type changed from Shared to a new Workflowed.

Call your congressman.

Something occurred to me while I was writing this small series. There is another best practice that has a very complicated relationship with the workflow. We embrace it and bet our content architectures on it and yet it gets in a way of a smooth and predictable editorial process. Datasorces. Why is it? Can something be done about it?

Next time on this blog. Stay tuned!

p.s. You can also account for language variance in a layout with personalization by language but I would probably advise against it. Assuming, of course, that you are using marketing automation capabilities of Sitecore and specifically the A/B content testing. Algorithms that generate multivariate permutations and track test performance can’t tell the difference between a functional personalization and a marketing-driven experience variance. Hopefully I get to write about it some time later.