Over the years I have worked on lots of websites that have performance issue caused by sitecore queries that iterate over too many items, usually searching through descendants.
In development where there are not too many items the query performs OK. The site goes live and they start to add content and soon there are 1000’s of items and the queries becomes slower and slower over time. A typical example is searching for the 10 latest news, articles, blog’s etc.
At this point a fix is made by changing the queries to use the “fast:” syntax. There are a lot of articles already explaining this in detail, so this is just a brief intro, the fast syntax translates the query directly into an SQL server database query, and therefore for some queries it can perform faster, use less memory and less CPU.
But a warning it bypasses all caching that sitecore provides and make a request directly to the database every time the query is executed; in development this tends to outperform the standard queries that would have to iterate over a lot of items.
In development you usually test the normal query against the fast syntax query to establish if it is quicker and if it’s quicker you use it and everybody is happy… but that is not the full story.
Let’s assume each page generates 10-20 queries that rely on fast syntax to retrieve their items. Therefore each page request generates 10-20 calls to the SQL database, I’ve seen sites that generate 100’s of SQL request per page😦
In development this would typically not be an issue and or not noticed, as only one page at a time is requested, but in a production on a website with lots of requests it can kill the SQL database and or slow the site down as sitecore itself cannot retrieve items form the database, as the SQL server is busy with all the fast queries.
Therefore you have to be VERY VERY CAREFUL with the use of fast queries.
I would say in 97% of cases – if your queries slow and it is caused by iterating over to many items the correct solution is to use Sitecore search i.e. lucene, Solr, Coveo or another indexer to retrieve the items.