need help with xml problem

[Guillermo Hidalgo]

Our site is build in xml technology, but is givin us a lot of
problems, especialy the high cpu usage problem. We have a web server
and a database server from where the xml is create. The web server is
fine but the data is always at 100 percents of is usage and it has 2
processors pentium 3 1.3 2 gybytes of memorie. We are using sql 2000
as our data base What can it be??, can someone please help me givin
me some tips or things to look for.

The process we use in our site is:
We use XML in two scenarios, the first one is the site structure,
this is a large XML file containing info on all the pages and modules
inside of them, and the main menu. something like this:

<site>
<page1>
<column1>
<include1/>
<include2/>
<include3/>
</column1>
<column2>
<include1/>
<include2/>
<include3/>
</column2>
<column3>
<include1/>
<include2/>
<include3/>
</column3>
</page1>
<menu>
<item1/>
<item2/>
<item3/>
<item4/>
</menu>
</site>

We process this using Micosoft XMLDOM, we've also tryed Chilkat XML
parser and I got a 300% improvement as they say, but still is
processor consumming. we traverse the document back and forth to
retrieve the data. the main methods we use are GetChildNodes,
Getattribute, nextsibbling, previoussibbling, childnodes.lenght.

----

The other part where we use XML is the SQL2000 Server, we use stored
procedures to get the data in XML format using FOR XML EXPLICIT and a
4 level UNION Example:

Select 1 TAG,
null PARENT,
field1 as [node1!1!field1],
field2 as [node1!1!field2],
field3 as [node2!2field3],
field4 as [node2!2field4],
field5 as [node3!3field5],
field6 as [node3!3field6],
field7 as [node4!4field7],
field8 as [node4!4ield8],
field9 as [node4!4field9],
field10 as [node4!4!field10]
from table
UNION ALL
Select 2 TAG,
1 PARENT,
field1,
null,
null,
null,
null,
null,
null,
null,
null,
null
from table
UNION ALL
Select 3 TAG,
2 PARENT,
field1,
null,
null,
null,
null,
null,
null,
null,
null,
null
from table
UNION ALL
Select 4 TAG,
3 PARENT,
field1,
null,
null,
null,
null,
null,
null,
null,
null,
null
from table
ORDERB BY [node1!1!field1]

We get the data using an ASP page and the ado.stream to read the xml
text data, we process the XML with MSXML 4.0 using XSL stylesheets
precompiled in application variables using the XSLPROCESSOR.

Tht's it.

Thanks.

[Russell Jones]

It's impossible to say without more information exactly what's causing your
problems, but here are some possibilities.

The *first* place to look is to make sure that you have appropriate indexes
on the tables themselves. Use the Query Analyzer. If you see your database
performing table scans, you should add or tune your indexes to avoid that if
at all possible.

1. Do you cache the XML file containing the site structure at Application
scope using a FreeThreadedDOMDocument? I assume that file wouldn't change
often, so there would be no need to load it each time.

2. Do you cache the output of often-used pages so that you don't have to
rebuild them from scratch for every request?

3. If the results of your SQL Server stored procedures often return the same
data, you could cache repeated queries and avoid hitting the SQL server
again.

4. If you don't have a great deal of data in the SQL Server tables, you
could load the entire thing into a DOMDocument object and perform data
retrieval directly from memory on the Web server, thus avoiding the overhead
of hitting the SQL server and creating a new XML document for each request.

5. If it turns out that after trying the other methods, the "FOR XML" is in
fact causing your problem, you could try building a "template" XML document
on the Web server, retrieve the data from SQL Server in standard resultsets,
and use DOM methods from code on the Web server to place the data into the
template, after which you'd send the completed document to the stylesheets
for transformation as you're doing now. While less efficient, this would
serve to transfer the burden of creating the XML document from your SQL
server to your Web server (which you said isn't overloaded).

Russell Jones
Executive Editor,
DevX.com





"Guillermo Hidalgo" <gahdl@hotmail.com> wrote in message
news:3cd6cf12$1@10.1.10.29...
>
> Our site is build in xml technology, but is givin us a lot of
> problems, especialy the high cpu usage problem. We have a web server
> and a database server from where the xml is create. The web server is
> fine but the data is always at 100 percents of is usage and it has 2
> processors pentium 3 1.3 2 gybytes of memorie. We are using sql 2000
> as our data base What can it be??, can someone please help me givin
> me some tips or things to look for.
>
> The process we use in our site is:
> We use XML in two scenarios, the first one is the site structure,
> this is a large XML file containing info on all the pages and modules
> inside of them, and the main menu. something like this:
>
> <site>
> <page1>
> <column1>
> <include1/>
> <include2/>
> <include3/>
> </column1>
> <column2>
> <include1/>
> <include2/>
> <include3/>
> </column2>
> <column3>
> <include1/>
> <include2/>
> <include3/>
> </column3>
> </page1>
> <menu>
> <item1/>
> <item2/>
> <item3/>
> <item4/>
> </menu>
> </site>
>
> We process this using Micosoft XMLDOM, we've also tryed Chilkat XML
> parser and I got a 300% improvement as they say, but still is
> processor consumming. we traverse the document back and forth to
> retrieve the data. the main methods we use are GetChildNodes,
> Getattribute, nextsibbling, previoussibbling, childnodes.lenght.
>
> ----
>
> The other part where we use XML is the SQL2000 Server, we use stored
> procedures to get the data in XML format using FOR XML EXPLICIT and a
> 4 level UNION Example:
>
> Select 1 TAG,
> null PARENT,
> field1 as [node1!1!field1],
> field2 as [node1!1!field2],
> field3 as [node2!2field3],
> field4 as [node2!2field4],
> field5 as [node3!3field5],
> field6 as [node3!3field6],
> field7 as [node4!4field7],
> field8 as [node4!4ield8],
> field9 as [node4!4field9],
> field10 as [node4!4!field10]
> from table
> UNION ALL
> Select 2 TAG,
> 1 PARENT,
> field1,
> null,
> null,
> null,
> null,
> null,
> null,
> null,
> null,
> null
> from table
> UNION ALL
> Select 3 TAG,
> 2 PARENT,
> field1,
> null,
> null,
> null,
> null,
> null,
> null,
> null,
> null,
> null
> from table
> UNION ALL
> Select 4 TAG,
> 3 PARENT,
> field1,
> null,
> null,
> null,
> null,
> null,
> null,
> null,
> null,
> null
> from table
> ORDERB BY [node1!1!field1]
>
> We get the data using an ASP page and the ado.stream to read the xml
> text data, we process the XML with MSXML 4.0 using XSL stylesheets
> precompiled in application variables using the XSLPROCESSOR.
>
> Tht's it.
>
> Thanks.
>

[Chris Cain]

It sounds very possible that your indexing needs a look. Also, depending on
the size of your database, my experience has been that unioning multiple
tables is a bad idea. If youre going to use union, try to limit the resultset
as much as possible.



"Guillermo Hidalgo" <gahdl@hotmail.com> wrote:
>
>Our site is build in xml technology, but is givin us a lot of
>problems, especialy the high cpu usage problem. We have a web server
>and a database server from where the xml is create. The web server is
>fine but the data is always at 100 percents of is usage and it has 2
>processors pentium 3 1.3 2 gybytes of memorie. We are using sql 2000
>as our data base What can it be??, can someone please help me givin
>me some tips or things to look for.
>
>The process we use in our site is:
>We use XML in two scenarios, the first one is the site structure,
>this is a large XML file containing info on all the pages and modules
>inside of them, and the main menu. something like this:
>
><site>
><page1>
><column1>
><include1/>
><include2/>
><include3/>
></column1>
><column2>
><include1/>
><include2/>
><include3/>
></column2>
><column3>
><include1/>
><include2/>
><include3/>
></column3>
></page1>
><menu>
><item1/>
><item2/>
><item3/>
><item4/>
></menu>
></site>
>
>We process this using Micosoft XMLDOM, we've also tryed Chilkat XML
>parser and I got a 300% improvement as they say, but still is
>processor consumming. we traverse the document back and forth to
>retrieve the data. the main methods we use are GetChildNodes,
>Getattribute, nextsibbling, previoussibbling, childnodes.lenght.
>
>----
>
>The other part where we use XML is the SQL2000 Server, we use stored
>procedures to get the data in XML format using FOR XML EXPLICIT and a
>4 level UNION Example:
>
>Select 1 TAG,
>null PARENT,
>field1 as [node1!1!field1],
>field2 as [node1!1!field2],
>field3 as [node2!2field3],
>field4 as [node2!2field4],
>field5 as [node3!3field5],
>field6 as [node3!3field6],
>field7 as [node4!4field7],
>field8 as [node4!4ield8],
>field9 as [node4!4field9],
>field10 as [node4!4!field10]
>from table
>UNION ALL
>Select 2 TAG,
>1 PARENT,
>field1,
>null,
>null,
>null,
>null,
>null,
>null,
>null,
>null,
>null
>from table
>UNION ALL
>Select 3 TAG,
>2 PARENT,
>field1,
>null,
>null,
>null,
>null,
>null,
>null,
>null,
>null,
>null
>from table
>UNION ALL
>Select 4 TAG,
>3 PARENT,
>field1,
>null,
>null,
>null,
>null,
>null,
>null,
>null,
>null,
>null
>from table
>ORDERB BY [node1!1!field1]
>
>We get the data using an ASP page and the ado.stream to read the xml
>text data, we process the XML with MSXML 4.0 using XSL stylesheets
>precompiled in application variables using the XSLPROCESSOR.
>
>Tht's it.
>
>Thanks.
>