Rendering of Template is taking more time | XSLT Mediator

Question

We have few xml which genereted from Tridion Using C# TBB and XSLT Mediator and we have seen from last few days renderning is taking lot of time. I didn't change any code since last 1 year , templates/C# TBB same.If any single xml taking time then I can suspect that template issue is their but seems many file is taking time so it can be issue with CM Database.

<CompoundTemplate xmlns="http://www.tridion.com/ContentManager/5.3/CompoundTemplate">  <TemplateInvocation>
<Template xlink:href="tcm:481-2485527-2048" xmlns:xlink="http://www.w3.org/1999/xlink" xlink:title="Get StructureGroup Page XML" />
<TemplateParameters></TemplateParameters>  </TemplateInvocation>  <TemplateInvocation>
<Template xlink:href="tcm:481-3127216-2048" xmlns:xlink="http://www.w3.org/1999/xlink" xlink:title="Expand StructureGroups Page (breadcrumb) 12" />
<TemplateParameters></TemplateParameters>  </TemplateInvocation></CompoundTemplate>

However first C# TBB is not taking time,rendering only taking time when output from first C# TBB passed to second TBB (XSLT Mediator).
1. Output of C# TBB

<tcm:ListOrganizationalItems publicationId="tcm:0-481-1" xmlns:tcm="http://www.tridion.com/ContentManager/5.0">
<tcm:Item id="tcm:481-38288-4" title="Root" parentUri="tcm:0-481-1" folder="" />
<tcm:Item id="tcm:481-38443-4" title="010. The  Experience" parentUri="tcm:481-38288-4" folder="product"> <em:Metadata AccessKey="F" ImageOff="/99/english/images/item481-343457.gif" ImageOn="/99/english/images/item481-343458.gif" ImageSelected="/99/english/images/flying_with_ctive_tcm481-343459.gif" ImageSelectedOn="/99/english/images/with__tcm481-343458.gif" xmlns:em="http://www.xxx.com/tridion/schemas" />  </tcm:Item>
<tcm:Item id="tcm:481-246922-64" title="S010. Terms and Conditions" parentUri="tcm:481-47185-4" filename="terms_and_conditions" />
<tcm:Item id="tcm:481-246924-64" title="S010. Terms and Conditions" parentUri="tcm:481-47186-4" filename="terms_and_conditions" />
</tcm:ListOrganizationalItems>

Above is OutputXML from C# TBB, Note : we have around 3000 tcm:item tag in above output xml,whole xml I can't copied here

XSLT Mediator Code

<?XsltMediator extensionsTbbId="tcm:230-1958240-2048"  inputItemName="StructureGroupXml" outputitemname="Output"?>
<xsl:output method="xml" version="1.0" encoding="UTF-8" indent="yes" omit-xml-declaration="yes" />
<xsl:param name="PageXML" />
 <xsl:variable name="targetID" select="'tcm:0-36-65537'" />
<xsl:template match="tcm:ListOrganizationalItems">
<xsl:copy>
  <xsl:attribute name="publicationId">tcm:0-<xsl:value-of select="substring-before(substring-after(tcm:Item/@ID,'tcm:'),'-')" />-1</xsl:attribute>
  <xsl:apply-templates select="tcm:Item[string(number(substring-before(@Title,'. '))) != 'NaN' or (starts-with(translate(@Title, 's', 'S'), 'S') and string(number(substring-before(substring-after(@Title, 'S'), '. '))) != 'NaN') or (starts-with(translate(@Title, 'b', 'B'), 'B') and string(number(substring-before(substring-after(@Title, 'B'), '. '))) != 'NaN') or position() = 1 or substring-before(substring-after(@ID,'-'),'-')='38288']" />
  <xsl:apply-templates select="$PageXML/tcm:ListItems" />
</xsl:copy>
  </xsl:template>
  <xsl:template match="tcm:Item">
<xsl:variable name="document" select="document(@ID)" />
   <xsl:copy-of select="." />
  </xsl:template>
<xsl:template match="tcm:ListItems">
<xsl:apply-templates select="tcm:Item[starts-with(@Title, '000.') or not(starts-with(@Title, 'S000'))]" mode="PageXML" />
  </xsl:template>
<xsl:template match="tcm:Item" mode="PageXML">
<xsl:variable name="document" select="document(@ID)" />
<xsl:copy>
<xsl:attribute name="id">
<xsl:value-of select="@ID" /></xsl:attribute>
<xsl:attribute name="title"><xsl:value-of select="$document/tcm:Page/tcm:Data/tcm:Title" /></xsl:attribute>
</xsl:copy>
</xsl:template>
</xsl:stylesheet> 

While doing debugging with XSLT, we found whenever we doing Xpath in document which we load using document function it's taking time.

Any Suggestion How we can solve above problem How can I check is their any issue in database or not?

Answer 1

Seems like a tricky one to debug. In general, performance issues where "nothing has changed" is related to the database performance, and this can only be optimized by looking at database traces and making sure regular maintenance is executed on the DB - sp_updatestats on SQL Server and the scripts we ship for DB maintenance for Oracle.

Usually we find out if there is a database problem by:

1. Measuring performance
2. Running database maintenance
3. Measuring performance again

If you have a big difference between 1 and 3, you know that 2 was the cause.

It could also be that your data is growing, and therefore the processing time increases as your XML documents are bigger - and there's not much you can do in this case other than add hardware or refactor your templates to something that can scale better/be more modular.