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.
Alan Coates - Sitecore/.NET blog 
This is a great article. On my first implementation I ever built in Sitecore, the main architect wanted to pull all data from Sitecore using Sitecore Query. At the time, even I was hesitant on this, because sure, if you are narrow minded, if you only have a hundred articles, this may not seem like it runs too slow. But I’ve learned in my life that even the best architectured sites will transform in ways you didn’t plan. I think if you have to query outside of just .Children, (not descendants), than you should always look at using Lucene to do so, even inside the CMS.
But an important thing I feel to note, is that with Html Caching, even if you are using Sitecore Query/Fast Query, these should help with those performance issues. Keep in mind that Lucene didn’t always exist inside Sitecore, and that’s where I think Html Caching shined. Although I don’t see nearly enough implementations out there using Html Caching, which they should also be doing.
“I don’t wanna”😩