Sitecore Fast Query mechanism allows fetching items via direct SQL queries:
var query = $"fast://*[@@id = {ContainerItemId}]//*[@@id = {childId}]//ancestor::*[@@templateid = {base.TemplateId}]";
Item[] source = ItemDb.SelectItems(query);
...
fast:// query would be interpreted into SQL fetching results directly from database. Unfortunately, the mechanism is no longer widely used these days. Is there any way to bring it back to life?
Drawback from main power: Direct SQL query
Relational databases are not good at scaling, thereby a limited number of concurrent queries is possible. The more queries are executed at a time, the slower it gets. Adding additional physical database server for extra publishing target complicates solution and increases the cost.
Drawback: Fast Query SQL may be huge
The resulting SQL statement can be expensive for database engine to execute. The translator would usually produce a query with tons of joins. Should item axes (f.e. .// descendants syntax) be included, the price tag raises even further.
Fast Query performance drops as volume of data in database increases. Being ok-ish with little content during development phase could turn into a big trouble in a year.
Alternative: Content Search
Content Search is a first thing to replace fast queries in theory; there are limitations, though:
- Every index should cover only certain parts of content tree (5.8 Developer’s Guide to Item Buckets and Search), hence search among all buckets is impossible
- Index is a metadata built from actual data = not 100 % accurate all the time
Alternative: Sitecore Queries and Item API
Both Sitecore Queries and Item APIs are executed inside application layer = slow.
Firstly, inspecting items is time consumable and the needed one might not be even in results.
Secondly, caches are getting polluted with data flowing during query processing; useful bit might be kicked out from cache when maxSize is reached and scavenge starts.
Making Fast Queries great again!
Fast queries can be first-class API once performance problems are resolved. Could the volume of queries sent to database be reduced?
Why no cache for fast: query results?
Since data in publishing target (f.e.web) can be modified only by publishing, queries shall return same results in between. The results can be cached in memory and scavenged whenever publish:end occurs.
Implementation
We’ll create ReuseFastQueryResultsDatabase decorator on top of stock Sitecore database with caching layer for SelectItems API:
public sealed class ReuseFastQueryResultsDatabase : Database
{
private readonly Database _database;
private readonly ConcurrentDictionary<string, IReadOnlyCollection<Item>> _multipleItems = new ConcurrentDictionary<string, IReadOnlyCollection<Item>>(StringComparer.OrdinalIgnoreCase);
private readonly LockSet _multipleItemsLock = new LockSet();
public ReuseFastQueryResultsDatabase(Database database)
{
Assert.ArgumentNotNull(database, nameof(database));
_database = database;
}
public bool CacheFastQueryResults { get; private set; }
#region Useful code
public override Item[] SelectItems(string query)
{
if (!CacheFastQueryResults || !IsFast(query))
{
return _database.SelectItems(query);
}
if (!_multipleItems.TryGetValue(query, out var cached))
{
lock (_multipleItemsLock.GetLock(query))
{
if (!_multipleItems.TryGetValue(query, out cached))
{
using (new SecurityDisabler())
{
cached = _database.SelectItems(query);
}
_multipleItems.TryAdd(query, cached);
}
}
}
var results = from item in cached ?? Array.Empty<Item>()
where item.Access.CanRead()
select new Item(item.ID, item.InnerData, this);
return results.ToArray();
}
private static bool IsFast(string query) => query?.StartsWith("fast:/") == true;
protected override void OnConstructed(XmlNode configuration)
{
if (!CacheFastQueryResults)
{
return;
}
Event.Subscribe("publish:end", PublishEnd);
Event.Subscribe("publish:end:remote", PublishEnd);
}
private void PublishEnd(object sender, EventArgs e)
{
_singleItems.Clear();
}
A decorated database shall execute the query in case there has been no data in cache yet. A copy of the cached data is returned to a caller to avoid cache data corruption.
The decorator is placed into own fastQueryDatabases configuration node and referencing default database as constructor argument:
<configuration xmlns:patch="http://www.sitecore.net/xmlconfig/" xmlns:role="http://www.sitecore.net/xmlconfig/role/">
<sitecore role:require="Standalone">
<services>
<configurator type="SecondLife.For.FastQueries.DependencyInjection.CustomFactoryRegistration, SecondLife.For.FastQueries"/>
</services>
<fastQueryDatabases>
<database id="web" singleInstance="true" type="SecondLife.For.FastQueries.ReuseFastQueryResultsDatabase, SecondLife.For.FastQueries" >
<param ref="databases/database[@id='$(id)']" />
<CacheFastQueryResults>true</CacheFastQueryResults>
</database>
</fastQueryDatabases>
</sitecore>
</configuration>
Only thing left is to initialize database from fastQueryDatabases node whenever a database request arrives (via replacing stock factory implementation):
public sealed class DefaultFactoryForCacheableFastQuery : DefaultFactory
{
private static readonly char[] ForbiddenChars = "[\\\"*^';&></=]".ToCharArray();
private readonly ConcurrentDictionary<string, Database> _databases;
public DefaultFactoryForCacheableFastQuery(BaseComparerFactory comparerFactory, IServiceProvider serviceProvider)
: base(comparerFactory, serviceProvider)
{
_databases = new ConcurrentDictionary<string, Database>(StringComparer.OrdinalIgnoreCase);
}
public override Database GetDatabase(string name, bool assert)
{
Assert.ArgumentNotNull(name, nameof(name));
if (name.IndexOfAny(ForbiddenChars) >= 0)
{
Assert.IsFalse(assert, nameof(assert));
return null;
}
var database = _databases.GetOrAdd(name, dbName =>
{
var configPath = "fastQueryDatabases/database[@id='" + dbName + "']";
if (CreateObject(configPath, assert: false) is Database result)
{
return result;
}
return base.GetDatabase(dbName, assert: false);
});
if (assert && database == null)
{
throw new InvalidOperationException($"Could not create database: {name}");
}
return database;
}
}
Outcome
Direct SQL queries are no longer bombarding database engine making fast queries eligible to play leading roles even in highly loaded solutions.
Warning: Publishing Service does NOT support Fast Queries OOB
Publishing Service does not update tables vital for Fast Queries in evil manner: it does not change platform stock configuration leaving a broken promise behind: <setting name="FastQueryDescendantsDisabled" value="false" />
The platform code expects Descendants to be updated so that Fast Queries are executed. Due to table being out-of-date, wrong results are returned.
Making it run faster 
Described “fast cache” approach, will it lead to the performance troubles in case of frequent publishing, nullifying all the pros? So, are there any significant benefits using it versus the “Content Search”\”Sitecore Query”\”Item WebApi”.
LikeLike