Created: 4 Oct 2021, last update: 30 Jan 2022
The end of Sitecore fast query, 2 things to know to fix issues
In Sitecore 10.1 Sitecore Fast Query has been deprecated and also starting from Sitecore 10.1, the default Sitecore content is stored outside of the Database which enables out of process updates. But what does that mean for Sitecore Fast Query?
First, you shouldn’t use fast query anymore, it does a direct query on the database and it is not scalable. Today you should be using the search index.
When you do a fast query in Sitecore 10.1, Sitecore do the query as before and also add the data stored outside Sitecore, so the end result should be the same in happy flow.
A query from Security reporting module is: "fast://*[@__Security != \"\"]"
Get all items with Security configured.
A very heavy query it could fail on low tier RDS/Azure SQL database. But anyway, it is onerous to refactor to search index because default, this field is not available in the search index and for this module you don’t want to change too much for somethings that run occasionally. Especially with also for Sitecore deprecated Azure search where you have limits. Beside that the code needs a Sitecore Item, and needs to run on all kinds of Sitecore versions. So all kinds of (non) arguments, I can image when you upgrade you first want to upgrade and after that project is done you remove the fast queries.
Let's look at the differences and fixes for Sitecore 10.1
#1 Query.MaxItems
In Sitecore 10.1 fast query depends on setting Query.MaxItems default it is 100, before there was no limit, so if your query needs more records to return. Adjust this, or set to 0 for no limit. Previous this setting was for the Sitecore query not including the fast query.
<setting name="Query.MaxItems" value="100"/>
#2 Fields not in Database.
The query "fast://*[@__Security != \"\"]" results in this database query:
exec sp_executesql N'SELECT DISTINCT [i].[ID] [ID], [i].[ParentID] [ParentID] FROM [Items] [i] WITH (NOLOCK)
LEFT OUTER JOIN (
SELECT [Fields].* from [Fields]
INNER JOIN [Items] ON [Fields].[FieldID] = [Items].[ID]
AND lower([Items].[Name]) = ''__security'')
[Fields1] ON [i].[ID] = [Fields1].[ItemId]
WHERE (coalesce([Fields1].[Value], '''')
NOT LIKE @value1)',N'@value1 nvarchar(4000)',@value1=N''
In a vanilla Sitecore 10.1 installation this will give 0 records because the Item __security is not present in the Database.
This can fixed by changing a field on the __Security item
database: master
path: /sitecore/templates/System/Templates/Sections/Security/Security/__Security
For example, just put a text in the “long description” on the __Security item.
With the 2 points above the security report tool works again, however, this tool is coming from Sitecore 6 it has a history soon or late in need to get rid of the Sitecore Fast query else it is at the end of life, (perhaps Sitecore PowerShell can do this job, at least the long running task will help to generate reports.)
