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

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.

Answer 2

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.

Answer 3

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.