Sitecore Fast query: performance problem

Sitecore Fast Query is designed for retrieving and filtering items from the Sitecore database. Sitecore
Fast Query uses the database engine to execute queries.

Original article with detailed information about the Sitecore Fast query you can find here.

Looks good, right?

The feature is cool, especially when you need to find all items with a specific name or field value which is not possible to do via Database API.
In Sitecore 7+ version one can use Content Search feature for this purpose but... sometimes the index is not up to date or field data might be stored in a different way than you expected, etc.

Getting data using Fast query could be very useful in some cases and do not introduce additional dependencies.

Let's have a look at the performance, is it good enough?

Sitecore Fast Query converts all the query conditions into SQL statements, and this places certain limitations on the use of queries.

It is more or less easy to generate an SQL statement for getting data for the statement that defines item fields and properties conditions (e.g. find items by some field value, by template ID or name and other combinations). But, what is happening when we need to search for items under the specific location?
For example: fast:/sitecore/content/Home//*[@@templatename='Sample Item']

Since Sitecore database structure does not keep item paths in tables and the Sitecore content tree is build basing on PrentID reference, we should create an SQL statement that should join Items table a number of times. 

To improve the performance of searching in the hierarchy and simplify the SQL statement, Sitecore databases have a special table that keeps item-parent relations.  This table updated on every item save \ move \ delete operations. This slows doewn item operations a little bit but allows to use item hierarchy search in Fast Query.

Let's have a look at the queries, corresponding SQL statements and execution time.
Test Data:
800k items in Items table
6M records in Descendants table
3M records in Versioned fields table

Fast Query
fast://*[@@templateid = '{0}']

This query is translated into the simple SQL that searches for all items with a specified template.
The execution time of such request is 0.47 ms

If we change this query a little bit and try to search items with a specific template in a specific location (under content item), the picture will be a little bit different.

Fast Query
fast:/sitecore/content//*[@@templateid = '{0}']

This request is executed ~1500 ms


I have got a little bit worth result with searching by field.

Fast Query
fast://*[@__lock='%\"" + Context.User.Name + "\"%'] is executing ~820 ms

while the same query but under specified location is executing ~3200ms

Fast Query
fast:/sitecore/content//*[@__lock='%\"" + Context.User.Name + "\"%']

Summary

Sitecore fast query can be very effective and useful for cases when one needs to select items using conditions that do not involve item hierarchies.

Some could say that Fast Query does not use cache and always perform requests to SQL server. Well, yes, this is true but fast query execution is not the common type of the requests to the database and spending resources on caching and invalidating the data in this cache might appear more expecnsive then just request the data from database. Also, the data constructed from the fast query request is taken from the cache. Thus, it might be not so bad.

As for the hierarchical data (queries that use item paths, I would recommend to review the query and search for all data that fits your conditions using fast query and then filter this data by location on the Web server side. For huge solutions with a lot of content, this approach will work much better. To test this I have replaced query
fast:/sitecore/templates/System/Analytics/External/Matchers/Client Matchers/* with just Database.GetItem(<item_path>).GetChildren() call and got 10 times faster execution.

Thanks.