It's time to put fast:/ query to rest

2018-10-21

It’s time to put `fast:/` query to rest

It’s been 8 good years. Well it’s been 8 years. It’s time - it’s way overdue in fact - that fast:/ is retired. I’m getting tired of debating and explaining the same thing over and over again on our Community Slack channels, so I figured I would write this post so as to - once and for all - try and rid the Sitecore community of this fast:/ pestilence.

It’s going to be a semi-long post. I apologise. Read it anyway.

TL;DWR: Stop using fast:/ query.

The history

Sitecore Information Architecture Anno 2005-2012ish

So I’ve been around long enough to remember, what fast:/ was introduced to solve. Join me on a trip down memory lane to the days of Sitecore 5 and 6.

Back then, the way we built Sitecore solutions was very different than what we see today. We were building page templates. Datasources was not commonly used and understood so we all, more or less, built our Sitecore sites in a way that every single field on a page would be represented on the page item. I know right? Unthinkable today. But we did. So we did use inheritance, we micro-managed template inheritance and often ended up with field names such as Section Subheading Bottom Right and Product Spot 3 Inner Subheading. I know, right?

Ignore for now the nightmare it was to refactor an Information Architecture such as this (which led to the also-obsolete practice of always addressing fields by their ID instead of their name - back then we had to change names quite often for the whole thing to make any sense to our content editor users). Anyway - ignore all this. We’ve all moved on, but this is how most of us did Sitecore Information Architecture from 2005 to somewhere around 2012/2013 or so. I know, because I vented about it in a blog series in 2013 called The Page Template Mistake.

The Content Editor Performance Problem

Other problems aside, there was a massive performance drawback from approaching Sitecore IA like this. And it came from the only editor available to us at the time - the good old trusted Sitecore Content Editor which stands, to this day, pretty much as it did back then. And then we had these Page Templates. 50 or so fields was not uncommon at all. And not just simple fields mind you, but Droplists, Droplinks, TreeLists and so on. All of them being populated by the source field of their respective field definition items on their respective templates.

And now we’re getting to it; the Content Editor simply could not cope. Switching between an item meant, Sitecore had to go execute all of these queries to fill the Droplists, TreeLists and so on, and both CPU and memory resources were a lot more limited at the time. Sitecore’s caches hadn’t quite evolved to what they are today, CPUs were single or dual-core (Core2 Duo was the latest and greatest). It just wasn’t really handling the job very well.

We needed a quicker and faster (pun sort of implied) way of being able to query these source definitions to fill our Droplists and TreeLists, and that should be easy enough right?

Since we only need the ID and the Item.Name in order for an item to be populated into one of these lists - surely a shortcut could be found. And a shortcut was found. It took a lot of shortcuts (since it needed only the above; the ID and the Name), scaled horribly (but that was alright, this was just to improve the Content Editing experience of a single CM user), ignored Versioning, Language, maybe even Workflows (I forget) - again, all of this was an acceptable trade-off for a faster Content Editing experience.

And thus, Sitecore fast:/ query was born. To my knowledge with Sitecore 6.2, but could have been slightly sooner than that. I can’t find the original blog posts that discussed all of this, nor the Sitecore Forum posts.

Here is the original documentation cookbook: Using Sitecore Fast Query

The Limitations of Sitecore `fast:/` query

Bypassing Sitecore’s Data Provider Architecture

So keeping in mind the above; fast:/ was designed to solve a single-user performance problem when the Content Editor had lots and lots of Droplists and TreeLists and so on to populate. I’ll extract some basic facts about fast:/ - all of which can be found in the cookbook documentation referenced above.

Under the heading: “Sitecore Fast Query has the following benefits compared to the standard Sitecore Query:”

Improved performance — queries are executed by the SQL engine and as a result the scalability and performance of the SQL engine is not limited by.NET or by Sitecore.

Let me translate that for you. “We’re pushing the load away from the web server and down onto the SQL server, so only the SQL server performance affects how fast your fast:/ query performs. Not the lack of caching in the Sitecore Content Editor, not the memory restraints of the web server”.

In other words; SQL now becomes your only bottleneck. But that’s not a problem right? Is just a single user editing some content, they don’t switch between items THAT often.

Consumes less memory — Sitecore Query loads every item that it touches into memory (cache) and this can fill the cache with unnecessary information. Sitecore Fast Query only loads the items from the result set and this minimizes the pressure on the cache.

Indeed. Using fast:/ bypasses such resource hogs as caches. Data caches, item caches. Because who needs them? It’s just a Content Editor user switching between an item once in a while, and since we’re constantly editing the content - caches don’t make any sense anyway.

Fundamentally, fast:/ works like this:

Sitecore Fast Query is translated to SQL queries that be executed by the database engine.

So it takes the query format and converts it into an SQL SELECT statement. Slightly simplified, but close enough. Cool right? Yea except this means bypassing the entire DataProvider architecture model of Sitecore, Item Cache, Data Cache and whatever else is required in a scalable solution then and today.

But that’s ok because it’s just a single Content Editor user switching between items once in a while. Right?

Because of this SQL conversion, only certain attributes are supported. And any complex operations that involve field values of any kind - very quickly degenerate into SQL queries so terrible your SQL Performance Profiler will have nightmares about them for years to come.

There are further limitations not called out in the original document.

No support for Language Fallback
In fact no Language support at all
Sort Order not respected
Versions not respected
Add more here if you like, the blog accepts PRs

The Scalability Problems of Sitecore `fast:/` Query

So fast:/ was never designed to scale. It remains as I have stated numerous times already, a technology that was meant to be utilised by a single Content Editor user switching between items in the Content Editor to (d’uh) edit content.

So what do you think happens, when you put fast:/ queries in your reqular runtime code that executes your website? Remember what I quoted above?

queries are executed by the SQL engine and as a result the scalability and performance of the SQL engine is not limited by.NET or by Sitecore

And here’s the kicker. Almost every blog I’ve ever read, that in any way deals with query performance (including some of these from my References section below) - only measure performance of various query types in a single-user environment. And if there is one thing you cannot do with fast:/ query, is get a handle on it’s performance in that kind of setup. Your live website will be running 50? 100? 200? 400? 800? concurrent sessions. And not only that; this will not be users “switching from item to item once in a while, while editing content”. No they will click and click and mercilessly request new pages, new content, all the time. And expect to get it instantly, too.

So I ask of you this. A technology introduced to improve the Content Editor performance of a single user switching items from time to time while editing content; now being used say… twice per component on your page, 15 or so average components per page, 800 concurrent sessions each requesting a new page every 10 seconds on average. 2400 fast:/ queries per second, bypassing any and all caching and going straight to your SQL server as inline SQL (no stored procedures, no prepared SQL Execution Plan) - 2400 of those per second - how well do you think that is going to work out?

This is NOT what fast:/ was designed to do. Not ever. Neither was your SQL server, for that matter.

And if you don’t want to believe me, ask your SQL server. Also don’t forget to include a mention for your hatred for Item Cache and Data Cache when you submit your next request to your CTO or whoever, for an upgrade in your Azure storage tier. MOAR SSDs right? Solves any problem.

Sitecore fast:/ does not scale. Be VERY aware of this. Even when you’re “performance testing” on your local machine, it might actually come out looking “allright” performance wise. But it just isn’t “allright”. Never. You have to believe me; 13 years I’ve been doing this (Sitecore stuff) - never once have I seen a benefit from a fast:/ query. Nor have I ever ever used one myself; which at least goes to argue that they by no means are essential.

But Then What?

You know what. Since Sitecore 7.0, Sitecore ContentSearch has been built-in to the product. There is nothing you can fast:/ query that you cannot also query using Sitecore ContentSearch - only you’ll get your results about 10 times quicker (notice how I didn’t say faster…) that way. And it scales.

Ignore made-up problems

Ignore made-up problems such as:

“I need a real-time view of my data for this operation”. You don’t - this is an architecture problem/fail.
“I really need a fast way to find all items of template XXX under this subtree”. Yes. ContentSearch them. And make a better Sitecore Information Architecture.
“Calling up an index to get just a few items is overkill”. It isn’t. It just isn’t. It is by many orders of magnitude quicker than selecting just 1 field from 1 row in your SQL server, bypassing all caches.
And if you insist, use the LinkDatabase. Most fast:/ I see anyway, is “Get me all items of template XXX at this location”, and LinkDatabase outperforms fast:/ by 10x or more for this operation.
“I don’t want to update my local indexes all the time”. Then why do it? I don’t. My indexes update normally, so do yours. It’s only when you’re changing index configuration such as adding new computed fields and so on that a full rebuild of your index would be required.

I Don’t Believe You

Well then don’t. I have no problem being called on-site as a “Super Hotshot Sitecore Performance Troubleshooter”, fixing a few of your broken fast:/ queries and billing your boss a months salary for it. We all got to make a living somehow.

That said; I also want to put proof behind all of this. Thing is - to well and truly set up a test that measures the real impact of fast:/ query under load, is NOT as simple as it may seem. I will try and see if I can get one of Sitecore’s hosting provider partners to help set up a test rig, fill it with appropriate content, and then query the night away. Send me your favourite fast:/ queries if you like, I’ll be happy to include them in the test.

Any volunteers, feel free to reach out. Otherwise I’ll come knocking.

And Help Spread The Word

fast:/ is like the pestilence that just won’t go away. It is so deeply ingrained in the consciousness of many Sitecore Developers (and even - sigh - Sitecore Trainers) and it comes with a flashy fast:/ prefix. Must mean it’s… fast, right? Wrong.

Help spread the word by retweeting this post; add a few words of your own. If nothing else, just do something like “I’m name and I approve of this message”. Or whatever. But let’s help each other out, yea? :-)

I will embed your tweets below.

It’s time to put fast:/ query to rest