Server Performance going down while we trying to get some data from Tridion 2009 broker LINK_INFO table using C# 2.0

Question

Actually I tried to implement Google Markup on our pages, so that our usercontrol will render below type of HTML on the page header section

<link rel="alternate" hreflang="en-GB" href="http://www.mysite.com/english/index.aspx" />

<link rel="alternate" hreflang="de-DE" href="http://www.mysite.com/de/german/index.aspx" />

<link rel="alternate" hreflang="en-DE" href="http://www.mysite.com/de/english/index.aspx" />

<link rel="alternate" hreflang="ru-RU" href="http://www.mysite.com/ru/russian/index.aspx" />

<link rel="alternate" hreflang="en-RU" href="http://www.mysite.com/ru/english/index.aspx" />

<link rel="alternate" hreflang="fr-FR" href="http://www.mysite.com/fr/french/index.aspx" />

<link rel="alternate" hreflang="it-IT" href="http://www.mysite.com/it/italian/index.aspx" />

<link rel="alternate" hreflang="ja-JP" href="http://www.mysite.com/jp/japanese/index.aspx" />

<link rel="alternate" hreflang="ko-KR" href="http://www.mysite.com/kr/korean/index.aspx" />

<link rel="alternate" hreflang="pt-BR" href="http://www.mysite.com/br/portuguese/index.aspx" />

<link rel="alternate" hreflang="zh-Hans-CN" href="http://www.mysite.com/cn/chinese/index.aspx" />

<link rel="alternate" hreflang="en-US" href="http://www.mysite.com/us/english/index.aspx" />

<link rel="alternate" hreflang="en-GB" href="http://www.mysite.com/uk/english/index.aspx" />

<link rel="alternate" hreflang="en-AU" href="http://www.mysite.com/au/english/index.aspx" />

<link rel="alternate" hreflang="en-AE" href="http://www.mysite.com/ae/english/index.aspx" />

In above html you can find this part of HTML "/ae/english/index.aspx, /au/english/index.aspx etc" from Broker LINK_INFO table, this implementation worked fine till we went LIVE website with LIVE broker database, and when we enable this functionality on LIVE our server performance got killed due to the hits on broker database and it seems locking of LINK_INFO table as our website has got 1.5 million per day hits, above functionality works like as below:

1. Whenever any website page is loaded it calls our proxy and proxy calls our webservice and webservice calls our SQL Procedure which goes to LINK_INFO table and takes out a list of result on the basis of PageID passed to SQL Procedure.
2. The SQL Procedure returned xml result is then passed to my control where my XSLT uses it and rendered out above full HTML.

It seems something is getting wrong, please suggest is there can be other way around to achieve this above functionality without touching broker database. Writing Page EVENT or Customizing Deployer would help?

Please suggest!!

Note: We are using Tridion 2009

EDIT: Broker SQL Procedure is as below:

ALTER PROCEDURE [dbo].[GETDataFromLinkInfo] 
-- Add the parameters for the stored procedure here 
(@PageID INT) 
AS 
  BEGIN 
      -- SET NOCOUNT ON added to prevent extra result sets from 
      -- interfering with SELECT statements. 
      SET NOCOUNT ON; 

      -- Insert statements for procedure here 
      SELECT DISTINCT [PUBLICATION_ID] AS n, 
                      [URL]            AS u 
      FROM   [LINK_INFO] WITH(NOLOCK) 
      WHERE  Page_ID = @PageID 
             AND Component_Template_Priority > 0 
             AND PUBLICATION_ID NOT IN( 232, 481 ) 
      ORDER  BY URL 
      FOR XML RAW ('p'), ROOT ('ps'); 

      RETURN 
  END

Felic

Is there a reason you are querying the Broker DB with SQL rather than using the cachable linking API? – Jaret

Hi Chris thanks for your reply, actually we are going to have different data for every page and we are passing pageID to broker, and on the basis of pageID it returns a XML of 130 lines (in our case) as we have got 130 publications in tridion. How can we use cachable linking api on this..Please suggest – Felic

Any reasons why this question is rated minus – Felic

Stackoverflow is not a site to get free consultancy, even though the people here are typically a friendly and helpful enough bunch. It is for concrete programming problems, stuff that we can answer with code/configuration samples. I fail to see the concrete programming question in yours. Even though we may be able to help you, this help will likely not be very helpful to others. – Artimas

Frank, I am facing the performance issue only, else my implementation is working fine, I am here to get some good solution from Tridion experts who can guide me for improving the implementation., However thanks and still can you please suggest any better approach on above implementation – Felic

If you remove the [tridion] tags from the question and add [mssql] you may have a chance that somebody can help you figure out why querying SQL content becomes slower when you use it against a bigger database. I'd guess an XML index might have something to do with it, but I am a Tridion expert and not an MSSQL expert. – Artimas

Answer 1

I hope you have some standard code in your implementation, which you might be able to search in for some proper Tridion API linking. Obviously, as has been stated already before, querying the Tridion Broker directly is not supported, but it also makes no sense for this Tridon Core Linking feature.

Anyway, look for code that looks like this:

<tridion:ComponentLink runat="server" PageURI='tcm:12-1234-64'
                TemplateURI="tcm:0-0-0" ComponentURI="tcm:12-1233"
                LinkText="proper Tridion Linking in .NET" TextOnFail="true"/>

Get your hand on some Tridon documentation ASAP. That is a must when working with Tridion!

Good luck!

---

EDIT: An untested code sample which should be able to write out your Google Markup MultiLingual link in the head when the method id called with the pageID (without TCM):

using System;
using Tridion.ContentManager;
using Tridion.ContentManager.CommunicationManagement;
using Tridion.ContentManager.ContentManagement;
using Tridion.ContentManager.Templating;

namespace Website.TridionTBBs.Utilities
{
    /// <summary>
    /// Class exposing utility methods for frequent Tridion item methods.
    /// </summary>
    public static class TridionCustomUtilities
    {
        #region Constants

        public const string PageLinkGoogleMarkup = "<link rel=\"alternate\" hreflang=\"{0}\" href=\"{1}\" />\r\n";

        #endregion

        #region PageLinks
        /// <summary>
        /// This method will return the MultiLingual Google Markup link
        /// Relies on two important Webconfig entries where the publication and culture information is located
        /// <add key="publications" value="26,27,28,29,30,31,32,33,34" />
        /// <add key="tcm:0-26-1" value="en-GB" />
        /// <add key="tcm:0-27-1" value="de-DE" />
        /// etc...
        /// </summary>
        /// <param name="pageID">The PageId is provided from the page</param>
        static void GoogleMarkupPageLink(int pageID)
        {

            string[] publicationIDs = ConfigurationManager.AppSettings["publications"].Split(',');

            StringWriter s = new StringWriter();

            using (PageLink pageLink = new PageLink())
            {
                for (int i = 0; i < publicationIDs.Count; i++)
                {
                    Link link = pageLink.GetLink(String.Format("tcm:{0}-{1}", publicationIDs[i], pageID.ToString()));

                    if (link != null && link.IsResolved)
                    {
                        string linkUrl = link.Url;

                    }
                    string culture = ConfigurationManager.AppSettings[String.Format("tcm:0-{0}-1", publicationIDs[i])];

                    Response.Write(String.Format(PageLinkGoogleMarkup, culture, linkUrl));
                }
            }
        }
        #endregion
    }
}

This would require you to store the publications and the culture string that belongs with each publication in the web.config. Of course, you can store this somewhere else as well, but this would seem to be the quickest and least stressful for the webservers. Of course proper caching needs to be in place.

This would avoid you having to write custom deploy scripts or other complicated non-standard Tridion methods.

Clein

Hi Hendrik..Thanks, I know very well Tridion Linking API, my question is that, is that is going to help me for above implementation as my current implementation passes pageid (center part) to my GoogleSEO Database and sent the result back to me and further it is passed to my XSLT which renders above HTML. Now I am not able to understand how to get this data using Tridion Linking API...Please suggest on this..thanks – Felic

I am planning to customize deployer, please see my comments below in Fran's answer, would appreciate if you can throw some light for that implementation. – Felic

What is it you guys are actually trying to achieve here? Some sort of redirect possibility between different publications based on page_ID? I am having a hard time trying to figure out what the goal of this piece of code with the custom database call. – Clein

Well Hendrik as i have already told as above we want to implement Google Markup (blog.globalizationpartners.com/…) and needs the Tridion published pages...and that as our broker Link_info table has got all these details, we were trying to achieve that using database and there were some performance issue when we went live as our per page views are aroung 3 million..so thought deployer customization would help us as in that case no broker hits...any thoughts on this – Felic

OK, I have changed the last part of my answer to I think a pretty complete method you could use. This example needs some Web.config changes, but I think will save you lots of time and further headache. Out of interest in this Google Markup for multilingual websites I have gone out to research this answer. I hope this will get you there! – Clein

Thanks Hendrik for pointing this, my question is here now again we are going to hit broker database to get pagelinking on the basis of every publication, is that is going to impact the performance, and also I am getting error on this line Link link = pageLink.GetLink(String.Format("tcm:{0}-{1}", publicationIDs[i], pageID.ToString())); I changed to this Link link = pageLink.GetLink(String.Format("tcm:{0}-{1}", publicationIDs[i], pageID.ToString()), String.Empty, String.Empty, String.Empty, false, String.Empty); still getting error...thanks – Felic

You can try Link link = (Link)pageLink.GetLink(pageUri, null, null, null, true, null); instead. But, this is just a matter of playing around with it. And, yes you will hit the broker a lot on each page, but at least it will be cached. Your original solution did the same, but without any caching. The other solution is going to back to your custom deploy script and adding these links in the head during deployment, but then you might as well add them during render time when publishing. This solution will be the most dynamic – Clein

Actually above your solution will hit to broker database as much publication ID I have, I mean this is going to low down the broker database, however in our previous implementation ti was just one hit per page, however in your solution it will 128 as I have got 128 publications in my Tridion.....any thought on this – Felic

When set-up properly Tridion should be able to handle this type of load. Caching is extremely important. Once the link is requested it is cached. 128 dynamic links per page page is a lot, but not extreme in my opinion. You will have to make an architecture design decision. Do I go for a complete dynamic solution where I know I will never get broken links, or do I add these links in a more static way, with the risk of having broken and outdated links. If you can guarantee that a certain page will be available in another language in the exact location I would chose the more static solution. – Clein

let us continue this discussion in chat – Felic

Answer 2

Querying the Database directly is NOT supported, may invalidate your support contract and - obviously - circumvents the usage of Tridion Cache (which may partially explain your performance issues). Suggestion: Use the Tridion LINKING API for what you're trying to achieve.

Pegues

thanks..this was just for testing.. and now we have moved this data to separate database and table. Any starting point how we can use Tridion Linking API here instead of database usage – Felic

sdllivecontent.sdl.com/LiveContent/content/en-US/… – Artimas

@frank-van-puffelen, we are still using Tridion 2009, and what is the username and password for above link. – Felic

You should have received this from Customer Support... Ask Tridion Support to provide you the login. – Pegues

@Nuno...thanks i will check this our admin team, can you please share some thought how we can use Linking API in above implementation as I am looking to traverse table for my requirements. – Felic

The Content Delivery linking API hasn't changed substantially between Tridion 2009 and 2011. You can get access details from your support contact. – Artimas

Answer 3

Whenever you run into database performance problems, there are two approaches that can provide quick relief:

1. Add additional indexes onto columns that are used in (sorting and filtering of) your queries
2. Cache the results of expensive queries for a certain amount of time

In this case I'd definitely look at the indexes, since it sounds like you may be missing some necessary XML indexes on your live database. If you're not very fluent with database operations, also consider just keeping the HTML fragment that you generate in a static variable and re-use it for subsequent requests. Even if you just do this for say 5 minutes you'll end up reducing the hits on the database by factors.

---

I think the warning about using SQL against a Tridion database has been driven home enough now. In a longer term you should definitely look for a way to get the same information through the Tridion Content Delivery API. I am quite sure the same information is readily available there too, although I am not entirely sure if you can also get the results as a list as quickly as you may do it here.

Even though you may end up with similar performance problems if you go that route, you'll at least be back into a supported Tridion domain. This means more Tridion community members may be able to help you.

Caching will definitely also be an option to reduce the performance problems once you switch to using the Tridion API. Alternative you could indeed keep the list of languages/URLs as a separate file on disk and update that every time something relevant is deployed. An extension to the Tridion deployer would be the logical place to do this. If you do a Google search for "Tridion deployer extension", I'm quite sure some good results will show up.

Artimas

Thanks Frank, I got this (sdltridionworld.com/articles/sdltridion2011/tutorials/…), now couple of question comes in mind, before we for deployer extension, publising is going to slow as my extension will work for every page publish or unpublish, and it will delete the xml when unpublishing is done and create a new xml when publishing is done, now when user is viewing that page in website and at that editors publishes the same page then this will give exception of locking and deployer will fail to create xml...what do you suggest on this – Felic

Frank, I am planning to customize deployer which will be customized for PageDeploy and UnDeploy module, one question hits about the locking issue as whenever one page is published my deployer will create an xml on server something like this name 123456.xml for that page and it will store the node like <pubid="123" url="index.aspx"/> for that particular page and if same page is published from other language it will go and check that page xml exists over there if exists will open it and append a node again <pubid="456" url="index.aspx"/>, my question is that how to avoid the locking here!! – Felic

Since it's your extension writing the files, I also assume it's your extension that keeps a lock on them. Look through your code to see if you're closing all streams that you open. – Artimas

But there can multiple activities for deployer extension as for same page it will be removing the entries when page is unpublished and adding an entry for same page published from other publication and again this xml is also passed to usercontrol where it will generate actual Google Markup code as posted above. And if we check for locking this is again going to impact lot in Tridion publishing...what you say for this – Felic