What are the limitations of Fast query?

Question

After reviewing this question and answer you can see that the use of Fast query is the leading cause of migraines. (<- unverified claim)

What are limitations a developer should take into account when using Fast query?

Answer 1

Article by John West

I came across a nice article written by John West which provides important information for developers.

Known limitations:

• Fast query does not account for the context language (results include items with versions that match the query in any language)
• Fast query does not support sorting (sort results after querying)
• In a fast query, attributes should not appear at the beginning of a predicate, directly after the opening square bracket character ([)
• Fast query supports only the parent, child, ancestor, and descendant axes, the latter two only when the FastQueryDescendantsDisabled setting is false
• Fast query does not support standard values
• Fast query does not support functions, though it provides an alternative to the contains and therefore I assume the startswith() and endswith() functions
• Fast query does not support operators
• Fast query does not support subqueries, which are queries within predicates (square brackets – [] characters)
• Fast Query works for data providers that inherit from the SqlDataProvider class and that support SQL queries generated by the QueryToSqlTranslator class
• Fast query does not support query catenation (using pipe delimiter to separate paths)

The one about data providers could explain why you would not use Fast queries with Unicorn.

Here is a valuable piece of advice:

I would avoid [Sitecore] Fast query [unless] Sitecore query underperforms and you have very specific requirements met despite these limitations. I believe Sitecore query may be especially poor for queries that process small branches of items, as I assume it queries the entire database and then filters the results by branch.

I also found some articles that pointed out useful limitations.

• Fast query does not guarantee results are returned in the proper tree sort order. Link.
• Fast query performs best with fewer than 10 fields in each condition statement. Link.
• Dashes must be escaped in queries. Link.

Found in a Sitecore guide (Using Sitecore Fast Queries)

• Fast query does not return virtual items in the result set.

Limitations disguised as Benefits

These are some benefits from the document, but could also be treated like a limitation.

• 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.
• 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. Read more about it here (thanks @vandsh for the link).