DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 3 of 3

Thread: need help with xml problem

  1. #1
    Guillermo Hidalgo Guest

    need help with xml problem


    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.


  2. #2
    Russell Jones Guest

    Re: need help with xml problem

    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.
    >




  3. #3
    Chris Cain Guest

    Re: need help with xml problem


    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.
    >



Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center