3

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.

  • Are you using Tridion's cache? sdltridionworld.com/articles/… – Chris Morgan Feb 6 '14 at 11:35
  • We used to use caching, but since our heavy queries are result queries, these do not get cached anyway. – Para Jaco Feb 6 '14 at 13:22
  • 1
    Query results and not just items are cached in later versions. It might be worth revisiting cache with an upgrade. – Alvin Reyes Feb 7 '14 at 9:47
2

I don't think the queries themselves have changed (much) in SDL Tridion 2013, but the Query API model has changed a lot, which will result in different queries which shouldn't generate those inner joins anymore (which you could read as the queries have changed).

So definitely it will be worth looking into an upgrade to SDL Tridion 2013, but that does mean you will have to rewrite your Broker code, so maybe you should investigate further with a Proof Of Concept (which will give you an idea of the total work involved and performance gain to be achieved).

Now you are mentioning the inefficient SQL being generated with those nasty inner joins, that is typical R5.3 code which is indeed heavy on your database. However the database going down, I wouldn't expect that to happen, maybe you can elaborate more on that? (do you have Broker caching enabled, what is the Broker code you use that generates the mentioned SQL, is that code optimized, and what information can you give about the Broker database going down, was that a crash, or was it at 100% CPU or memory?)

  • In terms of query "changes" In 2009 or 2011 I believe Custom Queries became obsolete and we got the Taxonomy API along with Criteria filters. You can still kill a broker with the wrong query though (100% CPU). I wrote some gotchas for this question. An upgrade to at least 2011 is worth it for support both in terms of browsers and tickets with SDL. :-) – Alvin Reyes Feb 7 '14 at 9:51
  • @AlvinReyes a database server with 100 CPU usage isn't killed, it will just take its time to execute the query and return to normal usage. Or the query timeout might kick in, but all in all, you can configure your database server how to behave in these kind of situations. A crash is what I would really call a kill... – Bart Koopman Feb 7 '14 at 10:08
  • You're right. The setup I'm remembering had development CM and CD databases on the same server. A slow crawl was more like it. But give the "bad" query a try for fun sometime. :-) – Alvin Reyes Feb 7 '14 at 10:27
  • I remember now, @NunoLinhares used the phrase, "Don't try this at home (it kills your DB Server)." – Alvin Reyes Feb 8 '14 at 0:36
1

We also faced quite similar kind of issue while migrating our application from R5.3 to Tridion 2011 SP1. The way metadata is stored in broker is that multiple rows are created for a component (one row for each metadata field) resulting in multiple self join. There were some of the broker queries which were taking forever and hence we had to use raw sql queries on broker to resolve the issue (not a good practise as it wont be supported while upgrading to newer version). I would suggest you to revisit your design and see if it can be refactored in such a way that number of records returned is miniumum.(Assuming you are already using the filters in your broker queries)

1

Regarding the broad question of "Are queries that rely on categories/keywords more optimized in some way?" the answer is definitely so.

This was partially added in 2009, and further improved in 2011 and 2013. Tridion used to cache only the objects returned by a query result, while now it actually caches the query itself, so unless you published something, the queries should never hit the database (assuming you setup cache properly).

Given that caching queries is almost a dark art form, we took the approach to invalidate query cache everytime you publish anything, since it could change the query results. If you are constantly publishing, then this might not add much improvement, but if you only publish, say, 40-50 times per day, then you should see a noticeable performance improvement.

As was mentioned already, we also replaced and improved the query API and moved away from the "Search Filters" into a more structured "Criteria" API that does give you a lot more flexibility (and less dependencies on the database model).

Regarding alternate storages, with Tridion 2011 we introduced JPA extensibility, so you could use a different storage altogether and still use Tridion to control what gets written where and when. There's some good tutorials about this on SDL Tridion World and pretty good implementation of it on the SI4T extension (Search Integration for Tridion).

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.