After many years of doing a lot of reviews on Sitecore I would conclude that it more or less always comes down to one single thing when pages perform badly: bad code. And when I mean bad code, I mean code iterating an excessive amount of items.
Iterating A LOT of items
Previously we used to see a lot of calls to descendants when solutions were using axis, xpath or Sitecore Query, but luckily this is a disappearing trend with the introduction of Sitecore Search in Sitecore 7. This does not mean that there are less bad code – maybe even on the contrary. The use of ORM and abstractions in many solutions means that layers of code can easily hide item iterations. Neither of the two patterns or technologies are inherently bad, but they do provide means to hide “bad code”.
The ARGH! Example
The example in the diagram below is from a customer here in Australia which had a problem with a very slow running product search. The diagram breaks down the different parts of the search page and describes the functionality and number of items hit by each part. It was part of the presentation to the client to highlight where the performance bottleneck was (and that Sitecore itself was not cause of the problem).
After looking through layers and layers of code (3000+ lines to be more specific), we discovered that the code in the different layers were searching Lucene and iterating search results, loading the items individually and recursively running business logic.
A simple search resulting in a page of 50 links would easily take up to 5 minutes, simply because the code was iterating in excess of 1.7 mio items from the Sitecore database – which would take time in any solution 🙂
To cut the very long story short: always keep an eye on number of items being iterated – and never rely on caching to do the trick.