While analyzing why adding items to the publish queue is taking so long, I discovered that the stored procedure EDA_PUBLICATION_TARGETS_READ is called for every item added to the queue, and can sometimes take 4-6 seconds to execute. I took a look at the execution plan, and 98% of the query cost is being spent on another stored procedure being called, EDA_PUB_TARGET_ASSOCIATIONS_READ. Since our implementation has over 800 publications, sending an item to all child publications can become extremely time consuming. Things have also gotten worse over time as we've added publications. I feel that the performance of this stored procedure is unacceptable. We run sp_updatestats regularly and make sure that the indexes aren't too fragmented. Any suggestions for fixing this stored procedure?
4 Answers
The stored procedures are part of black box code and should not be modified by anyone other than R&D and Support. Hence it would be most appropriate to address this question to SDL Tridion Support, not folks on this forum. My suggestion is for you to open up a ticket and give them as much detail about your configuration and DB spec as possible. If you convince Support that the code is indeed inefficient, then they are very likely issue you a hotfix.
I would say that it is great you did some performance metrics on the database and suggest to send this data to Tridion Customer Support. You have a very large dataset, maybe one of the largest, who knows. In the past it's not just the amount of Publications you have, but also the complexity of parent-child relationships that adds to the amount of time needed for publishing. So, by sending your data to customer support and working closely with them, there's a good chance the performance would be improved for all customers.
If you modify your database, the biggest risk is that something breaks and customer support and R&D won't be able to reproduce your error message or offer a fix. Officially, they would say you are not supported, since your DB is a modified one.
However, if it is a very isolated change, and is possible to rollback to the original state very quickly (like if something goes wrong and CS needs to help) then I would say go ahead and make a very special exception for this case. I would also review and test the rollback to the original SPs and make sure it is easy and possible.
It is also important that this is well documented on your end and your DBs are very familiar with how to put the old SP back in case an issue is reported to Customer Support. But, sounds like the potential gain is worth it. And, you're not doing the worst thing, which is modifying the data itself in the DB. That is really really bad, since the data could become corrupt, etc. But, improving a read-only Stored Procedure has fewer side effects, especially in the scope of the publish queue.
For an Oracle database, you will very likely be asked by Support to supply the AWR report including execution plans for that and associated procedures.
For a SQL Server database, you will be asked to supply the SQL Server trace generated during the time you were adding items to the queue and noticed this behavior.
Your DBAs should also take a look and note anything unusual they want to point out.
Well one obvious, but perhaps unattractive, answer is to reduce the number of publications. The big question here, of course is do you use them all, and are they all in the same blueprint?
800 is a lot. Let's see - there's only 200 or so countries in the world. Maybe you are running several big blueprint master chains all in the same Tridion instance. If you are, you might consider splitting them out to two or more instances. There'd probably be other benefits from doing this too.
