In our current project, still running on Tridion R5.3, we publish many components with metadata (category/keywords). Our server-side Java code queries the Broker API for published content, relying heavily on this attached component metadata. The Broker API queries translate into quite inefficient SQL statements with many nested (INNER) JOINs, which take a relatively long time to execute. On several occasions, this has even led to the Broker database going down, thereby bringing down the entire website. To find a solution for this, we are looking into 2 solutions directions:
Migrate asap to Tridion 2013: The question we face is if the Broker performance in Tridion 2013 is better than compared to Tridion 5.3? Are queries that rely on categories/keywords more optimized in some way?
Push component metadata at publish-time into another storage: To relieve the Broker from executing complicated metadata queries, we could perform those queries on another datastore like a search engine or a NoSQL document store. We are wondering if any clients use this type of solution to store and query for component metadata?
Ideas for other solution directions would of course be most welcome.