xquery nested loops

[Max Gaerber]

Hi all,

I've got a question about performance tuning and nested xquery loops.
The data to be queried has several nesting levels, e.g.

<Publication Guid="a">
   <CategoryLink Guid="b" Ordering="1" Publish="true">
      <ProductInfoLink Guid="c" ...>
          <ProductItemLink Guid="d" ...>

A Publication has 5-20 Category elements with each about 50 ProductInfo
elements with each 2-10 ProductItem. Publication is only a "wrapper"
declaring some extra information whereas the "real" data of ProductInfo
etc. come from another collection.

There are three functions similar to the one below which loop through
the element set and create xhtml for display on a web site. The call to
local:ProductInfos($catLink) processes the next nesteing level and so
on. Now, the query is quite slow ( > 10 seconds ) even for small
Publications. Is there a better way to handle this sort of nesting?

declare function local:Categories($pub as node()) as node()? {
    <ul class="categories"> {
        for $catLink in $pub/CategoryLink
        let $cat := collection(/Syncrovet/Category)//Category[@Guid =
$catLink/@Guid]
        let $publish := ($catLink/@Publish = "true")
        order by $catLink/@Ordering
        return
        <li class="category {if ($publish) then "included" else
"excluded"}" id="{$catLink/@Guid}">
            <span class="name">{$cat/Name cast as xs:string}</span>
            { local:Tags($catLink) }
            { if ($publish) then local:ProductInfos($catLink) else () }
        </li>
    }</ul>
};

Thanks,

Max










-------------------------------------------------------------------------
This SF.net email is sponsored by the 2008 JavaOne(SM) Conference
Don't miss this year's exciting event. There's still time to save $100.
Use priority code J8TL2D2.
http://ad.doubleclick.net/clk;198757673;13503038;p?http://java.sun.com/javaone
_______________________________________________
Exist-open mailing list
Exist-open@...
https://lists.sourceforge.net/lists/listinfo/exist-open

[Adam Retter-3]

Have you set up appropriate indexes?

2008/4/25 Max Gaerber <mgaerber@...>:

Hi all,

I've got a question about performance tuning and nested xquery loops.
The data to be queried has several nesting levels, e.g.

<Publication Guid="a">
  <CategoryLink Guid="b" Ordering="1" Publish="true">
     <ProductInfoLink Guid="c" ...>
         <ProductItemLink Guid="d" ...>

A Publication has 5-20 Category elements with each about 50 ProductInfo
elements with each 2-10 ProductItem. Publication is only a "wrapper"
declaring some extra information whereas the "real" data of ProductInfo
etc. come from another collection.

There are three functions similar to the one below which loop through
the element set and create xhtml for display on a web site. The call to
local:ProductInfos($catLink) processes the next nesteing level and so
on. Now, the query is quite slow ( > 10 seconds ) even for small
Publications. Is there a better way to handle this sort of nesting?

declare function local:Categories($pub as node()) as node()? {
   <ul class="categories"> {
       for $catLink in $pub/CategoryLink
       let $cat := collection(/Syncrovet/Category)//Category[@Guid =
$catLink/@Guid]
       let $publish := ($catLink/@Publish = "true")
       order by $catLink/@Ordering
       return
       <li class="category {if ($publish) then "included" else
"excluded"}" id="{$catLink/@Guid}">
           <span class="name">{$cat/Name cast as xs:string}</span>
           { local:Tags($catLink) }
           { if ($publish) then local:ProductInfos($catLink) else () }
       </li>
   }</ul>
};

Thanks,

Max










-------------------------------------------------------------------------
This SF.net email is sponsored by the 2008 JavaOne(SM) Conference
Don't miss this year's exciting event. There's still time to save $100.
Use priority code J8TL2D2.
http://ad.doubleclick.net/clk;198757673;13503038;p?http://java.sun.com/javaone
_______________________________________________
Exist-open mailing list
Exist-open@...
https://lists.sourceforge.net/lists/listinfo/exist-open

--
Adam Retter

eXist Developer
{ England }
adam@...
irc://irc.freenode.net/existdb
-------------------------------------------------------------------------
This SF.net email is sponsored by the 2008 JavaOne(SM) Conference
Don't miss this year's exciting event. There's still time to save $100.
Use priority code J8TL2D2.
http://ad.doubleclick.net/clk;198757673;13503038;p?http://java.sun.com/javaone
_______________________________________________
Exist-open mailing list
Exist-open@...
https://lists.sourceforge.net/lists/listinfo/exist-open

[Max Gaerber]

Hi,

Adam Retter schrieb:
>
> Have you set up appropriate indexes?
Well, I had the fulltext index specified for everything but now added
qnames for Publish and Ordering (see below). There is a small
improvement but still I don't know why such a query should take more
than 1-2 seconds. I have no idea if my xquery is so expensive or what I
should change to not have a near 100% processor load for more than 5-10
seconds...

Thanks, Max

<collection xmlns="http://exist-db.org/collection-config/1.0">
   <index>
       <fulltext default="all" attributes="yes"/>
       <!-- Range indexes -->
       <create qname="@Guid" type="xs:string"/>
       <create qname="@Publish" type="xs:boolean"/>
       <create qname="@Ordering" type="xs:integer"/>
   </index>
</collection>

>
>
> 2008/4/25 Max Gaerber <mgaerber@... <mailto:mgaerber@...>>:
>
>     Hi all,
>
>     I've got a question about performance tuning and nested xquery loops.
>     The data to be queried has several nesting levels, e.g.
>
>     <Publication Guid="a">
>       <CategoryLink Guid="b" Ordering="1" Publish="true">
>          <ProductInfoLink Guid="c" ...>
>              <ProductItemLink Guid="d" ...>
>
>     A Publication has 5-20 Category elements with each about 50
>     ProductInfo
>     elements with each 2-10 ProductItem. Publication is only a "wrapper"
>     declaring some extra information whereas the "real" data of
>     ProductInfo
>     etc. come from another collection.
>
>     There are three functions similar to the one below which loop through
>     the element set and create xhtml for display on a web site. The
>     call to
>     local:ProductInfos($catLink) processes the next nesteing level and so
>     on. Now, the query is quite slow ( > 10 seconds ) even for small
>     Publications. Is there a better way to handle this sort of nesting?
>
>     declare function local:Categories($pub as node()) as node()? {
>        <ul class="categories"> {
>            for $catLink in $pub/CategoryLink
>            let $cat := collection(/Syncrovet/Category)//Category[@Guid =
>     $catLink/@Guid]
>            let $publish := ($catLink/@Publish = "true")
>            order by $catLink/@Ordering
>            return
>            <li class="category {if ($publish) then "included" else
>     "excluded"}" id="{$catLink/@Guid}">
>                <span class="name">{$cat/Name cast as xs:string}</span>
>                { local:Tags($catLink) }
>                { if ($publish) then local:ProductInfos($catLink) else () }
>            </li>
>        }</ul>
>     };
>
>     Thanks,
>
>     Max
>
>
>
>
>
>
>
>
>
>
>     -------------------------------------------------------------------------
>     This SF.net email is sponsored by the 2008 JavaOne(SM) Conference
>     Don't miss this year's exciting event. There's still time to save
>     $100.
>     Use priority code J8TL2D2.
>     http://ad.doubleclick.net/clk;198757673;13503038;p?http://java.sun.com/javaone
>     _______________________________________________
>     Exist-open mailing list
>     Exist-open@...
>     <mailto:Exist-open@...>
>     https://lists.sourceforge.net/lists/listinfo/exist-open
>
>
>
>
> --
> Adam Retter
>
> eXist Developer
> { England }
> adam@... <mailto:adam@...>
> irc://irc.freenode.net/existdb <http://irc.freenode.net/existdb>

-------------------------------------------------------------------------
This SF.net email is sponsored by the 2008 JavaOne(SM) Conference
Don't miss this year's exciting event. There's still time to save $100.
Use priority code J8TL2D2.
http://ad.doubleclick.net/clk;198757673;13503038;p?http://java.sun.com/javaone
_______________________________________________
Exist-open mailing list
Exist-open@...
https://lists.sourceforge.net/lists/listinfo/exist-open

[Adam Retter-3]

There is a lot you havent told us, these two function are called from inside your return -  local:Tags($catLink) and local:ProductInfos($catLink), yet you have not included any details of these functions - these could be the cause?

2008/4/28 Max Gaerber <mgaerber@...>:

Hi,

Adam Retter schrieb:

>
> Have you set up appropriate indexes?

Well, I had the fulltext index specified for everything but now added
qnames for Publish and Ordering (see below). There is a small
improvement but still I don't know why such a query should take more
than 1-2 seconds. I have no idea if my xquery is so expensive or what I
should change to not have a near 100% processor load for more than 5-10
seconds...

Thanks, Max

<collection xmlns="http://exist-db.org/collection-config/1.0">
  <index>
      <fulltext default="all" attributes="yes"/>
      <!-- Range indexes -->
      <create qname="@Guid" type="xs:string"/>
      <create qname="@Publish" type="xs:boolean"/>
      <create qname="@Ordering" type="xs:integer"/>
  </index>
</collection>
>
>
> 2008/4/25 Max Gaerber <mgaerber@... <mailto:mgaerber@...>>:

>
>     Hi all,
>
>     I've got a question about performance tuning and nested xquery loops.
>     The data to be queried has several nesting levels, e.g.
>
>     <Publication Guid="a">
>       <CategoryLink Guid="b" Ordering="1" Publish="true">
>          <ProductInfoLink Guid="c" ...>
>              <ProductItemLink Guid="d" ...>
>
>     A Publication has 5-20 Category elements with each about 50
>     ProductInfo
>     elements with each 2-10 ProductItem. Publication is only a "wrapper"
>     declaring some extra information whereas the "real" data of
>     ProductInfo
>     etc. come from another collection.
>
>     There are three functions similar to the one below which loop through
>     the element set and create xhtml for display on a web site. The
>     call to
>     local:ProductInfos($catLink) processes the next nesteing level and so
>     on. Now, the query is quite slow ( > 10 seconds ) even for small
>     Publications. Is there a better way to handle this sort of nesting?
>
>     declare function local:Categories($pub as node()) as node()? {
>        <ul class="categories"> {
>            for $catLink in $pub/CategoryLink
>            let $cat := collection(/Syncrovet/Category)//Category[@Guid =
>     $catLink/@Guid]
>            let $publish := ($catLink/@Publish = "true")
>            order by $catLink/@Ordering
>            return
>            <li class="category {if ($publish) then "included" else
>     "excluded"}" id="{$catLink/@Guid}">
>                <span class="name">{$cat/Name cast as xs:string}</span>
>                { local:Tags($catLink) }
>                { if ($publish) then local:ProductInfos($catLink) else () }
>            </li>
>        }</ul>
>     };
>
>     Thanks,
>
>     Max
>
>
>
>
>
>
>
>
>
>
>     -------------------------------------------------------------------------
>     This SF.net email is sponsored by the 2008 JavaOne(SM) Conference
>     Don't miss this year's exciting event. There's still time to save
>     $100.
>     Use priority code J8TL2D2.
>     http://ad.doubleclick.net/clk;198757673;13503038;p?http://java.sun.com/javaone
>     _______________________________________________
>     Exist-open mailing list
>     Exist-open@...

>     <mailto:Exist-open@...>

>     https://lists.sourceforge.net/lists/listinfo/exist-open
>
>
>
>
> --
> Adam Retter
>
> eXist Developer
> { England }

> adam@... <mailto:adam@...>
> irc://irc.freenode.net/existdb <http://irc.freenode.net/existdb>

-------------------------------------------------------------------------
This SF.net email is sponsored by the 2008 JavaOne(SM) Conference
Don't miss this year's exciting event. There's still time to save $100.
Use priority code J8TL2D2.
http://ad.doubleclick.net/clk;198757673;13503038;p?http://java.sun.com/javaone
_______________________________________________
Exist-open mailing list
Exist-open@...
https://lists.sourceforge.net/lists/listinfo/exist-open

--
Adam Retter

eXist Developer
{ England }
adam@...
irc://irc.freenode.net/existdb
-------------------------------------------------------------------------
This SF.net email is sponsored by the 2008 JavaOne(SM) Conference
Don't miss this year's exciting event. There's still time to save $100.
Use priority code J8TL2D2.
http://ad.doubleclick.net/clk;198757673;13503038;p?http://java.sun.com/javaone
_______________________________________________
Exist-open mailing list
Exist-open@...
https://lists.sourceforge.net/lists/listinfo/exist-open

[Max Gaerber]

Hi,

I previously had posted all the code but did not get an answer, so I
thought to write less in order to make it easier to scan my problem
statement. I don't expect others to solve my problem but I'd be thankful
for any pointers that lead to a performance improvement of my setup.

The complete xquery and xml snippets is below.
The connection between the documents is as follows:
- CATEGORY.XML: A category tree to classify product data
- PRODUCT:XML: The real data containing product information and
references to categories (xinclude)
- PUBLICATION.XML: An "export definition" of categories and referenced
product information (with extra attributes for special ordering etc.)

Thanks, Max


XQUERY
------------------------------------------------------------------------------

xquery version "1.0";

(:  import module namespace request="http://exist-db.org/xquery/request";
    import module namespace session="http://exist-db.org/xquery/session";
    import module namespace util="http://exist-db.org/xquery/util";
:)

declare function local:Tags($el as node()) as node()*
{
    for $tag in $el/Tag
    return <span class="tag">{$tag/text()}</span>
};

declare function local:Categories($pub as node()) as node()? {
    if (empty($pub/CategoryLink)) then "keine Kategorien enthalten"
    else
    <ul class="categories"> {
        for $catLink in $pub/CategoryLink
        let $cat := collection(/Syncrovet/Category)//Category[@Guid =
$catLink/@Guid]
        let $publish := ($catLink/@Publish = "true")
        order by $catLink/@Ordering
        return
        <li class="category {if ($publish) then "included" else
"excluded"}" id="{$catLink/@Guid}">
            <span class="name">{$cat/Name cast as xs:string}</span>
            { local:Tags($catLink) }
            { if ($publish) then local:ProductInfos($catLink) else () }
        </li>
    }</ul>
};

declare function local:ProductInfos($catLink as node()) as node()? {
    if (empty($catLink/ProductInfoLink)) then ()
    else
    <ul class="productinfos">
    {
        for $prodLink in $catLink/ProductInfoLink
        let $prod := collection(/Syncrovet/Product)//ProductInfo[@Guid =
$prodLink/@Guid]
        let $publish := ($prodLink/@Publish = "true")
        order by $prodLink/@Ordering
        return
        <li class="productinfo {if ($publish) then "included" else
"excluded"}" id="{$prodLink/@Guid}">
            <span class="name">{$prod/ProductName/text()}</span>
            { local:Tags($prodLink) }
            { if ($publish) then local:ProductItems($prodLink, $prod)
else ()}
        </li>
    }
    {
        if (false()) then () else
        (: new productinfos :)
            let $old := $catLink/ProductInfoLink/@Guid
            for $new in //ProductInfo[(CategoryLink/@Guid =
$catLink/@Guid) and (not (@Guid = $old))]
            return <li class="productinfo new" id="{$new/@Guid}"><span
class="name">{$new/ProductName/text()}</span></li>
        (: end of new productitems :)
    }
    </ul>
};

declare function local:ProductItems($prodLink as node(), $prod as
node()) as node()? {
    (: if (empty($prodLink/ProductItemLink)) then ()
    else :)
    <ul class="productitems"> {
        for $artLink in $prodLink/ProductItemLink
        let $art := collection(/Syncrovet/Product)//ProductItem[@Guid =
$artLink/@Guid]
        let $publish := if ($artLink/@Publish = "true") then " included"
else " excluded"
        order by $artLink/@Ordering
        return
            <li class="productitem{$publish}" id="{$art/@Guid}">
                <span class="name">{$art/Name/text()}</span>
                {local:Tags($artLink)}
            </li>
    }  
    { (: new productitems :)
        let $old := $prodLink/ProductItemLink/@Guid
        for $new in $prod/ProductItem[not (@Guid = $old)]
        return <li class="productitem new" id="{$new/@Guid}">
            <span class="name">{$new/Name/text()}</span>
        </li>
    (: end of new productitems :) }
    </ul>
};


(: *** main *** :)
let $pub := collection(/Syncrovet/Publication)//Publication[@Guid = '{0}']
return
if (empty($pub)) then "Publikation {0} nicht gefunden!"
else local:Categories($pub)

PUBLICATION XML
------------------------------------------------------------

<Publications>
    <Publication Guid="2ddeabe9-e84c-4a71-87bc-f79f3ddb4011">
        <Title>Produkte</Title>
        <CategoryLink Guid="2ddeabe9-e84c-4a71-87bc-f79f3ddb4098"
Publish="true" Ordering="1">
            <ProductInfoLink Guid="26ea764d-1ea4-4539-8880-e97401798e35"
Publish="true" Ordering="1">
                <Tag>Im Preis gesenkt</Tag>
                <ProductItemLink
Guid="b23b1c71-275f-452b-8fb5-ba61bb9fac71" Publish="true" Ordering="1"/>
                <ProductItemLink
Guid="326d8a69-e0ef-4023-a05b-82c388f4e8c3" Publish="true" Ordering="1"/>
            </ProductInfoLink>
            <ProductInfoLink Guid="93cd0ddd-d065-406f-936b-c05b9725f2c1"
Publish="true" Ordering="1">
                <ProductItemLink
Guid="2b18bf89-1cf5-4a44-9e64-04c83d79df8f" Publish="true" Ordering="1"/>
            </ProductInfoLink>
            <ProductInfoLink Guid="0b467cf9-4456-4b6d-afdf-73079fca8a32"
Publish="true" Ordering="1">
                <Tag>Neu</Tag>
                <ProductItemLink
Guid="37605e9e-8ab4-432e-a26f-4ee16ef010c9" Publish="true" Ordering="1"/>
            </ProductInfoLink>
</CategoryLink>
</Publication>
</Publications>

CATEGORY XML
----------------------------------------------------------

<Category Key="ALL" Color="" OldId="1"
Guid="8a524645-64c1-4053-8420-00a7e6f23e0a">
  <Name>Alle</Name>
    <Category Key="MED" Color="" OldId="2"
Guid="c06c7ea1-53f9-42fe-bfef-3fd65f10232c"><Name>Arzneimittel</Name>
        <Category Key="VET" Color="" OldId="5"
Guid="2ddeabe9-e84c-4a71-87bc-f79f3ddb4098"><Name>WDT-Produkte</Name></Category>
        <Category Key="HUM" Color="" OldId="6"
Guid="282a3e30-49ef-4072-b073-f0b2ee8d251f"><Name>Human-Produkte</Name>
            <Category Key="Analeptika" Color="" OldId="2320"
Guid="62cee356-c8fc-439a-94e7-2e688aae1e9a"><Name>Analeptika/Antihypoxämika</Name></Category>


PRODUCT XML
----------------------------------------------------------

<ProductInfos xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:syncrovet2="urn:syncrovet2importtransformation-extensions"
xmlns:xi="http://www.w3.org/2001/XInclude"
xmlns:xhtml="http://www.w3.org/1999/xhtml">
    <ProductInfo Guid="26ea764d-1ea4-4539-8880-e97401798e35"
OldId="1001" Version="1" WorkflowStatus="Entw."
CreatedAt="2005-09-08T11:25:09.046" ModifiedAt="2005-10-23T13:49:23.718"
ModifiedBy="112">
        <ProductName>Acetatmischung</ProductName>
        <AtcCode />
        <ActiveSubstance />
        <Prescription>Nur beim Tierarzt erhältlich</Prescription>
        <ApprovalNr></ApprovalNr>
        <RegNr></RegNr>
        <Description>Pulver zur Anwendung auf der Haut</Description>
        <ProductClassification>Tierarzneimittel</ProductClassification>
        <IndexSortName>ACETATMISCHUNG</IndexSortName>
        <CompanyLink Name="Holder"
Guid="91a8c40f-db80-44e5-b093-e8b44ec3844c">
            <xi:include href="../Company/Company.xml"
xpointer="xpointer(//Company[@Guid='91a8c40f-db80-44e5-b093-e8b44ec3844c']/ShortName)"
/>
        </CompanyLink>
        <CategoryLink Guid="2ddeabe9-e84c-4a71-87bc-f79f3ddb4098">
            <xi:include href="../Category/Category.xml"
xpointer="xpointer(//Category[@Guid='2ddeabe9-e84c-4a71-87bc-f79f3ddb4098']/Name)"
/>
        </CategoryLink>
        <ProductText Key="Tiere"
Guid="ede0d147-c0f8-43bf-a349-4d02898ca83e" OldId="1001" Version="1"
WorkflowStatus="Entw." CreatedAt="2005-09-08T11:25:09.796"
ModifiedAt="2005-11-09T19:37:42.078" ModifiedBy="1"
xmlns="http://www.w3.org/1999/xhtml">
            <p>Pferde, Rinder, Schweine, Schafe, Ziegen, Hunde und
Katzen</p>
        </ProductText>
        <ProductItem Guid="b23b1c71-275f-452b-8fb5-ba61bb9fac71"
OldId="1001" Version="1" WorkflowStatus="Entw."
CreatedAt="2005-09-08T11:25:10.406" ModifiedAt="2007-11-13T10:25:58.631"
ModifiedBy="1">
            <Name>Acetatmischung</Name>
            <CompanyLink Name="Supplier"
Guid="91a8c40f-db80-44e5-b093-e8b44ec3844c">
                <xi:include href="../Company/Company.xml"
xpointer="xpointer(//Company[@Guid='91a8c40f-db80-44e5-b093-e8b44ec3844c']/ShortName)"
/>
            </CompanyLink>
            <SupplierArticleId>04413</SupplierArticleId>
            <Ean>4030996044135</Ean>
            <Dispensary>1g</Dispensary>
            <PriceInfo Guid="ee5c334c-0555-4c0c-9f0b-890981d00172"
OldId="6745340" Version="1" WorkflowStatus="Entw."
CreatedAt="2007-11-06T00:05:11.808" ModifiedAt="2007-11-06T00:05:11.808"
ModifiedBy="1">
                <StartDate>2008-01-01T00:00:00</StartDate>
                <EndDate>3000-01-01T00:00:00</EndDate>
                <Amount>6.9</Amount>
                <Currency>EUR</Currency>
                <Tax>19</Tax>
                <PriceType>TA-EK</PriceType>
                <Rebate>0</Rebate>
                <Country>GER</Country>
                <OrderQuantity>1</OrderQuantity>
                <GrossPrice>12.89</GrossPrice>
                <AmPreisV>10.83</AmPreisV>
            </PriceInfo>
            <PriceInfo Guid="11ed25c2-6b34-482d-ae74-97c6110cdbcc"
OldId="6745335" Version="1" WorkflowStatus="Entw."
CreatedAt="2007-11-06T00:05:11.511" ModifiedAt="2007-11-06T00:05:11.511"
ModifiedBy="1">
                <StartDate>2007-01-01T00:00:00</StartDate>
                <EndDate>3000-01-01T00:00:00</EndDate>
                <Amount>6.7</Amount>
                <Currency>EUR</Currency>
                <Tax>19</Tax>
                <PriceType>TA-EK(alt)</PriceType>
                <Rebate>0</Rebate>
                <Country>GER</Country>
                <OrderQuantity>1</OrderQuantity>
                <GrossPrice>12.52</GrossPrice>
                <AmPreisV>10.52</AmPreisV>
            </PriceInfo>
        </ProductItem>
    </ProductInfo>







Adam Retter schrieb:

> There is a lot you havent told us, these two function are called from
> inside your return -  local:Tags($catLink) and
> local:ProductInfos($catLink), yet you have not included any details of
> these functions - these could be the cause?
>
> 2008/4/28 Max Gaerber <mgaerber@... <mailto:mgaerber@...>>:
>
>     Hi,
>
>     Adam Retter schrieb:
>     >
>     > Have you set up appropriate indexes?
>     Well, I had the fulltext index specified for everything but now added
>     qnames for Publish and Ordering (see below). There is a small
>     improvement but still I don't know why such a query should take more
>     than 1-2 seconds. I have no idea if my xquery is so expensive or
>     what I
>     should change to not have a near 100% processor load for more than
>     5-10
>     seconds...
>
>     Thanks, Max
>
>     <collection xmlns="http://exist-db.org/collection-config/1.0">
>       <index>
>           <fulltext default="all" attributes="yes"/>
>           <!-- Range indexes -->
>           <create qname="@Guid" type="xs:string"/>
>           <create qname="@Publish" type="xs:boolean"/>
>           <create qname="@Ordering" type="xs:integer"/>
>       </index>
>     </collection>
>     >
>     >
>     > 2008/4/25 Max Gaerber <mgaerber@...
>     <mailto:mgaerber@...> <mailto:mgaerber@...
>     <mailto:mgaerber@...>>>:
>     >
>     >     Hi all,
>     >
>     >     I've got a question about performance tuning and nested
>     xquery loops.
>     >     The data to be queried has several nesting levels, e.g.
>     >
>     >     <Publication Guid="a">
>     >       <CategoryLink Guid="b" Ordering="1" Publish="true">
>     >          <ProductInfoLink Guid="c" ...>
>     >              <ProductItemLink Guid="d" ...>
>     >
>     >     A Publication has 5-20 Category elements with each about 50
>     >     ProductInfo
>     >     elements with each 2-10 ProductItem. Publication is only a
>     "wrapper"
>     >     declaring some extra information whereas the "real" data of
>     >     ProductInfo
>     >     etc. come from another collection.
>     >
>     >     There are three functions similar to the one below which
>     loop through
>     >     the element set and create xhtml for display on a web site. The
>     >     call to
>     >     local:ProductInfos($catLink) processes the next nesteing
>     level and so
>     >     on. Now, the query is quite slow ( > 10 seconds ) even for small
>     >     Publications. Is there a better way to handle this sort of
>     nesting?
>     >
>     >     declare function local:Categories($pub as node()) as node()? {
>     >        <ul class="categories"> {
>     >            for $catLink in $pub/CategoryLink
>     >            let $cat :=
>     collection(/Syncrovet/Category)//Category[@Guid =
>     >     $catLink/@Guid]
>     >            let $publish := ($catLink/@Publish = "true")
>     >            order by $catLink/@Ordering
>     >            return
>     >            <li class="category {if ($publish) then "included" else
>     >     "excluded"}" id="{$catLink/@Guid}">
>     >                <span class="name">{$cat/Name cast as
>     xs:string}</span>
>     >                { local:Tags($catLink) }
>     >                { if ($publish) then local:ProductInfos($catLink)
>     else () }
>     >            </li>
>     >        }</ul>
>     >     };
>     >
>     >     Thanks,
>     >
>     >     Max
>     >
>     >
>     >
>     >
>     >
>     >
>     >
>     >
>     >
>     >
>     >    
>     -------------------------------------------------------------------------
>     >     This SF.net email is sponsored by the 2008 JavaOne(SM)
>     Conference
>     >     Don't miss this year's exciting event. There's still time to
>     save
>     >     $100.
>     >     Use priority code J8TL2D2.
>     >    
>     http://ad.doubleclick.net/clk;198757673;13503038;p?http://java.sun.com/javaone
>     >     _______________________________________________
>     >     Exist-open mailing list
>     >     Exist-open@...
>     <mailto:Exist-open@...>
>     >     <mailto:Exist-open@...
>     <mailto:Exist-open@...>>
>     >     https://lists.sourceforge.net/lists/listinfo/exist-open
>     >
>     >
>     >
>     >
>     > --
>     > Adam Retter
>     >
>     > eXist Developer
>     > { England }
>     > adam@... <mailto:adam@...>
>     <mailto:adam@... <mailto:adam@...>>
>     > irc://irc.freenode.net/existdb <http://irc.freenode.net/existdb>
>     <http://irc.freenode.net/existdb>
>
>
>     -------------------------------------------------------------------------
>     This SF.net email is sponsored by the 2008 JavaOne(SM) Conference
>     Don't miss this year's exciting event. There's still time to save
>     $100.
>     Use priority code J8TL2D2.
>     http://ad.doubleclick.net/clk;198757673;13503038;p?http://java.sun.com/javaone
>     _______________________________________________
>     Exist-open mailing list
>     Exist-open@...
>     <mailto:Exist-open@...>
>     https://lists.sourceforge.net/lists/listinfo/exist-open
>
>
>
>
> --
> Adam Retter
>
> eXist Developer
> { England }
> adam@... <mailto:adam@...>
> irc://irc.freenode.net/existdb <http://irc.freenode.net/existdb>

-------------------------------------------------------------------------
This SF.net email is sponsored by the 2008 JavaOne(SM) Conference
Don't miss this year's exciting event. There's still time to save $100.
Use priority code J8TL2D2.
http://ad.doubleclick.net/clk;198757673;13503038;p?http://java.sun.com/javaone
_______________________________________________
Exist-open mailing list
Exist-open@...
https://lists.sourceforge.net/lists/listinfo/exist-open

[Max Gaerber]

Hi,

thanks für your hints. I've already managed to get some better results
but I guess I need a better way to do the profiling. I noticed some
posts which have very detailed information about where the processing
time goes to. What setup do I need to manage this? The source code
distribution and maybe there is an open source profiler?

Regards,

Max

Adam Retter schrieb:

> I would recommend working through your code bottom-up, commenting
> parts out until you find the slow aspect and then letting us know
> which part and we can then discuss that specifically.
>
>
> 2008/4/29 Max Gaerber <mgaerber@...>:
>  
>> Hi,
>>
>> I previously had posted all the code but did not get an answer, so I
>> thought to write less in order to make it easier to scan my problem
>> statement. I don't expect others to solve my problem but I'd be thankful
>> for any pointers that lead to a performance improvement of my setup.
>>
>> The complete xquery and xml snippets is below.
>> The connection between the documents is as follows:
>> - CATEGORY.XML: A category tree to classify product data
>> - PRODUCT:XML: The real data containing product information and
>> references to categories (xinclude)
>> - PUBLICATION.XML: An "export definition" of categories and referenced
>> product information (with extra attributes for special ordering etc.)
>>
>> Thanks, Max
>>
>>
>> XQUERY
>> ------------------------------------------------------------------------------
>>
>> xquery version "1.0";
>>
>> (:  import module namespace request="http://exist-db.org/xquery/request";
>>    import module namespace session="http://exist-db.org/xquery/session";
>>    import module namespace util="http://exist-db.org/xquery/util";
>> :)
>>
>> declare function local:Tags($el as node()) as node()*
>> {
>>    for $tag in $el/Tag
>>    return <span class="tag">{$tag/text()}</span>
>> };
>>
>> declare function local:Categories($pub as node()) as node()? {
>>    if (empty($pub/CategoryLink)) then "keine Kategorien enthalten"
>>    else
>>    <ul class="categories"> {
>>        for $catLink in $pub/CategoryLink
>>        let $cat := collection(/Syncrovet/Category)//Category[@Guid =
>> $catLink/@Guid]
>>        let $publish := ($catLink/@Publish = "true")
>>        order by $catLink/@Ordering
>>        return
>>        <li class="category {if ($publish) then "included" else
>> "excluded"}" id="{$catLink/@Guid}">
>>            <span class="name">{$cat/Name cast as xs:string}</span>
>>            { local:Tags($catLink) }
>>            { if ($publish) then local:ProductInfos($catLink) else () }
>>        </li>
>>    }</ul>
>> };
>>
>> declare function local:ProductInfos($catLink as node()) as node()? {
>>    if (empty($catLink/ProductInfoLink)) then ()
>>    else
>>    <ul class="productinfos">
>>    {
>>        for $prodLink in $catLink/ProductInfoLink
>>        let $prod := collection(/Syncrovet/Product)//ProductInfo[@Guid =
>> $prodLink/@Guid]
>>        let $publish := ($prodLink/@Publish = "true")
>>        order by $prodLink/@Ordering
>>        return
>>        <li class="productinfo {if ($publish) then "included" else
>> "excluded"}" id="{$prodLink/@Guid}">
>>            <span class="name">{$prod/ProductName/text()}</span>
>>            { local:Tags($prodLink) }
>>            { if ($publish) then local:ProductItems($prodLink, $prod)
>> else ()}
>>        </li>
>>    }
>>    {
>>        if (false()) then () else
>>        (: new productinfos :)
>>            let $old := $catLink/ProductInfoLink/@Guid
>>            for $new in //ProductInfo[(CategoryLink/@Guid =
>> $catLink/@Guid) and (not (@Guid = $old))]
>>            return <li class="productinfo new" id="{$new/@Guid}"><span
>> class="name">{$new/ProductName/text()}</span></li>
>>        (: end of new productitems :)
>>    }
>>    </ul>
>> };
>>
>> declare function local:ProductItems($prodLink as node(), $prod as
>> node()) as node()? {
>>    (: if (empty($prodLink/ProductItemLink)) then ()
>>    else :)
>>    <ul class="productitems"> {
>>        for $artLink in $prodLink/ProductItemLink
>>        let $art := collection(/Syncrovet/Product)//ProductItem[@Guid =
>> $artLink/@Guid]
>>        let $publish := if ($artLink/@Publish = "true") then " included"
>> else " excluded"
>>        order by $artLink/@Ordering
>>        return
>>            <li class="productitem{$publish}" id="{$art/@Guid}">
>>                <span class="name">{$art/Name/text()}</span>
>>                {local:Tags($artLink)}
>>            </li>
>>    }
>>    { (: new productitems :)
>>        let $old := $prodLink/ProductItemLink/@Guid
>>        for $new in $prod/ProductItem[not (@Guid = $old)]
>>        return <li class="productitem new" id="{$new/@Guid}">
>>            <span class="name">{$new/Name/text()}</span>
>>        </li>
>>    (: end of new productitems :) }
>>    </ul>
>> };
>>
>>
>> (: *** main *** :)
>> let $pub := collection(/Syncrovet/Publication)//Publication[@Guid = '{0}']
>> return
>> if (empty($pub)) then "Publikation {0} nicht gefunden!"
>> else local:Categories($pub)
>>
>> PUBLICATION XML
>> ------------------------------------------------------------
>>
>> <Publications>
>>    <Publication Guid="2ddeabe9-e84c-4a71-87bc-f79f3ddb4011">
>>        <Title>Produkte</Title>
>>        <CategoryLink Guid="2ddeabe9-e84c-4a71-87bc-f79f3ddb4098"
>> Publish="true" Ordering="1">
>>            <ProductInfoLink Guid="26ea764d-1ea4-4539-8880-e97401798e35"
>> Publish="true" Ordering="1">
>>                <Tag>Im Preis gesenkt</Tag>
>>                <ProductItemLink
>> Guid="b23b1c71-275f-452b-8fb5-ba61bb9fac71" Publish="true" Ordering="1"/>
>>                <ProductItemLink
>> Guid="326d8a69-e0ef-4023-a05b-82c388f4e8c3" Publish="true" Ordering="1"/>
>>            </ProductInfoLink>
>>            <ProductInfoLink Guid="93cd0ddd-d065-406f-936b-c05b9725f2c1"
>> Publish="true" Ordering="1">
>>                <ProductItemLink
>> Guid="2b18bf89-1cf5-4a44-9e64-04c83d79df8f" Publish="true" Ordering="1"/>
>>            </ProductInfoLink>
>>            <ProductInfoLink Guid="0b467cf9-4456-4b6d-afdf-73079fca8a32"
>> Publish="true" Ordering="1">
>>                <Tag>Neu</Tag>
>>                <ProductItemLink
>> Guid="37605e9e-8ab4-432e-a26f-4ee16ef010c9" Publish="true" Ordering="1"/>
>>            </ProductInfoLink>
>> </CategoryLink>
>> </Publication>
>> </Publications>
>>
>> CATEGORY XML
>> ----------------------------------------------------------
>>
>> <Category Key="ALL" Color="" OldId="1"
>> Guid="8a524645-64c1-4053-8420-00a7e6f23e0a">
>>  <Name>Alle</Name>
>>    <Category Key="MED" Color="" OldId="2"
>> Guid="c06c7ea1-53f9-42fe-bfef-3fd65f10232c"><Name>Arzneimittel</Name>
>>        <Category Key="VET" Color="" OldId="5"
>> Guid="2ddeabe9-e84c-4a71-87bc-f79f3ddb4098"><Name>WDT-Produkte</Name></Category>
>>        <Category Key="HUM" Color="" OldId="6"
>> Guid="282a3e30-49ef-4072-b073-f0b2ee8d251f"><Name>Human-Produkte</Name>
>>            <Category Key="Analeptika" Color="" OldId="2320"
>> Guid="62cee356-c8fc-439a-94e7-2e688aae1e9a"><Name>Analeptika/Antihypoxämika</Name></Category>
>>
>>
>> PRODUCT XML
>> ----------------------------------------------------------
>>
>> <ProductInfos xmlns:xsd="http://www.w3.org/2001/XMLSchema"
>> xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
>> xmlns:syncrovet2="urn:syncrovet2importtransformation-extensions"
>> xmlns:xi="http://www.w3.org/2001/XInclude"
>> xmlns:xhtml="http://www.w3.org/1999/xhtml">
>>    <ProductInfo Guid="26ea764d-1ea4-4539-8880-e97401798e35"
>> OldId="1001" Version="1" WorkflowStatus="Entw."
>> CreatedAt="2005-09-08T11:25:09.046" ModifiedAt="2005-10-23T13:49:23.718"
>> ModifiedBy="112">
>>        <ProductName>Acetatmischung</ProductName>
>>        <AtcCode />
>>        <ActiveSubstance />
>>        <Prescription>Nur beim Tierarzt erhältlich</Prescription>
>>        <ApprovalNr></ApprovalNr>
>>        <RegNr></RegNr>
>>        <Description>Pulver zur Anwendung auf der Haut</Description>
>>        <ProductClassification>Tierarzneimittel</ProductClassification>
>>        <IndexSortName>ACETATMISCHUNG</IndexSortName>
>>        <CompanyLink Name="Holder"
>> Guid="91a8c40f-db80-44e5-b093-e8b44ec3844c">
>>            <xi:include href="../Company/Company.xml"
>> xpointer="xpointer(//Company[@Guid='91a8c40f-db80-44e5-b093-e8b44ec3844c']/ShortName)"
>> />
>>        </CompanyLink>
>>        <CategoryLink Guid="2ddeabe9-e84c-4a71-87bc-f79f3ddb4098">
>>            <xi:include href="../Category/Category.xml"
>> xpointer="xpointer(//Category[@Guid='2ddeabe9-e84c-4a71-87bc-f79f3ddb4098']/Name)"
>> />
>>        </CategoryLink>
>>        <ProductText Key="Tiere"
>> Guid="ede0d147-c0f8-43bf-a349-4d02898ca83e" OldId="1001" Version="1"
>> WorkflowStatus="Entw." CreatedAt="2005-09-08T11:25:09.796"
>> ModifiedAt="2005-11-09T19:37:42.078" ModifiedBy="1"
>> xmlns="http://www.w3.org/1999/xhtml">
>>            <p>Pferde, Rinder, Schweine, Schafe, Ziegen, Hunde und
>> Katzen</p>
>>        </ProductText>
>>        <ProductItem Guid="b23b1c71-275f-452b-8fb5-ba61bb9fac71"
>> OldId="1001" Version="1" WorkflowStatus="Entw."
>> CreatedAt="2005-09-08T11:25:10.406" ModifiedAt="2007-11-13T10:25:58.631"
>> ModifiedBy="1">
>>            <Name>Acetatmischung</Name>
>>            <CompanyLink Name="Supplier"
>> Guid="91a8c40f-db80-44e5-b093-e8b44ec3844c">
>>                <xi:include href="../Company/Company.xml"
>> xpointer="xpointer(//Company[@Guid='91a8c40f-db80-44e5-b093-e8b44ec3844c']/ShortName)"
>> />
>>            </CompanyLink>
>>            <SupplierArticleId>04413</SupplierArticleId>
>>            <Ean>4030996044135</Ean>
>>            <Dispensary>1g</Dispensary>
>>            <PriceInfo Guid="ee5c334c-0555-4c0c-9f0b-890981d00172"
>> OldId="6745340" Version="1" WorkflowStatus="Entw."
>> CreatedAt="2007-11-06T00:05:11.808" ModifiedAt="2007-11-06T00:05:11.808"
>> ModifiedBy="1">
>>                <StartDate>2008-01-01T00:00:00</StartDate>
>>                <EndDate>3000-01-01T00:00:00</EndDate>
>>                <Amount>6.9</Amount>
>>                <Currency>EUR</Currency>
>>                <Tax>19</Tax>
>>                <PriceType>TA-EK</PriceType>
>>                <Rebate>0</Rebate>
>>                <Country>GER</Country>
>>                <OrderQuantity>1</OrderQuantity>
>>                <GrossPrice>12.89</GrossPrice>
>>                <AmPreisV>10.83</AmPreisV>
>>            </PriceInfo>
>>            <PriceInfo Guid="11ed25c2-6b34-482d-ae74-97c6110cdbcc"
>> OldId="6745335" Version="1" WorkflowStatus="Entw."
>> CreatedAt="2007-11-06T00:05:11.511" ModifiedAt="2007-11-06T00:05:11.511"
>> ModifiedBy="1">
>>                <StartDate>2007-01-01T00:00:00</StartDate>
>>                <EndDate>3000-01-01T00:00:00</EndDate>
>>                <Amount>6.7</Amount>
>>                <Currency>EUR</Currency>
>>                <Tax>19</Tax>
>>                <PriceType>TA-EK(alt)</PriceType>
>>                <Rebate>0</Rebate>
>>                <Country>GER</Country>
>>                <OrderQuantity>1</OrderQuantity>
>>                <GrossPrice>12.52</GrossPrice>
>>                <AmPreisV>10.52</AmPreisV>
>>            </PriceInfo>
>>        </ProductItem>
>>    </ProductInfo>
>>
>>
>>
>>
>>
>>
>>
>> Adam Retter schrieb:
>>    
>>> There is a lot you havent told us, these two function are called from
>>> inside your return -  local:Tags($catLink) and
>>> local:ProductInfos($catLink), yet you have not included any details of
>>> these functions - these could be the cause?
>>>
>>> 2008/4/28 Max Gaerber <mgaerber@... <mailto:mgaerber@...>>:
>>>
>>>     Hi,
>>>
>>>     Adam Retter schrieb:
>>>     >
>>>     > Have you set up appropriate indexes?
>>>     Well, I had the fulltext index specified for everything but now added
>>>     qnames for Publish and Ordering (see below). There is a small
>>>     improvement but still I don't know why such a query should take more
>>>     than 1-2 seconds. I have no idea if my xquery is so expensive or
>>>     what I
>>>     should change to not have a near 100% processor load for more than
>>>     5-10
>>>     seconds...
>>>
>>>     Thanks, Max
>>>
>>>     <collection xmlns="http://exist-db.org/collection-config/1.0">
>>>       <index>
>>>           <fulltext default="all" attributes="yes"/>
>>>           <!-- Range indexes -->
>>>           <create qname="@Guid" type="xs:string"/>
>>>           <create qname="@Publish" type="xs:boolean"/>
>>>           <create qname="@Ordering" type="xs:integer"/>
>>>       </index>
>>>     </collection>
>>>     >
>>>     >
>>>     > 2008/4/25 Max Gaerber <mgaerber@...
>>>     <mailto:mgaerber@...> <mailto:mgaerber@...
>>>     <mailto:mgaerber@...>>>:
>>>     >
>>>     >     Hi all,
>>>     >
>>>     >     I've got a question about performance tuning and nested
>>>     xquery loops.
>>>     >     The data to be queried has several nesting levels, e.g.
>>>     >
>>>     >     <Publication Guid="a">
>>>     >       <CategoryLink Guid="b" Ordering="1" Publish="true">
>>>     >          <ProductInfoLink Guid="c" ...>
>>>     >              <ProductItemLink Guid="d" ...>
>>>     >
>>>     >     A Publication has 5-20 Category elements with each about 50
>>>     >     ProductInfo
>>>     >     elements with each 2-10 ProductItem. Publication is only a
>>>     "wrapper"
>>>     >     declaring some extra information whereas the "real" data of
>>>     >     ProductInfo
>>>     >     etc. come from another collection.
>>>     >
>>>     >     There are three functions similar to the one below which
>>>     loop through
>>>     >     the element set and create xhtml for display on a web site. The
>>>     >     call to
>>>     >     local:ProductInfos($catLink) processes the next nesteing
>>>     level and so
>>>     >     on. Now, the query is quite slow ( > 10 seconds ) even for small
>>>     >     Publications. Is there a better way to handle this sort of
>>>     nesting?
>>>     >
>>>     >     declare function local:Categories($pub as node()) as node()? {
>>>     >        <ul class="categories"> {
>>>     >            for $catLink in $pub/CategoryLink
>>>     >            let $cat :=
>>>     collection(/Syncrovet/Category)//Category[@Guid =
>>>     >     $catLink/@Guid]
>>>     >            let $publish := ($catLink/@Publish = "true")
>>>     >            order by $catLink/@Ordering
>>>     >            return
>>>     >            <li class="category {if ($publish) then "included" else
>>>     >     "excluded"}" id="{$catLink/@Guid}">
>>>     >                <span class="name">{$cat/Name cast as
>>>     xs:string}</span>
>>>     >                { local:Tags($catLink) }
>>>     >                { if ($publish) then local:ProductInfos($catLink)
>>>     else () }
>>>     >            </li>
>>>     >        }</ul>
>>>     >     };
>>>     >
>>>     >     Thanks,
>>>     >
>>>     >     Max
>>>     >
>>>     >
>>>     >
>>>     >
>>>     >
>>>     >
>>>     >
>>>     >
>>>     >
>>>     >
>>>     >
>>>     -------------------------------------------------------------------------
>>>     >     This SF.net email is sponsored by the 2008 JavaOne(SM)
>>>     Conference
>>>     >     Don't miss this year's exciting event. There's still time to
>>>     save
>>>     >     $100.
>>>     >     Use priority code J8TL2D2.
>>>     >
>>>     http://ad.doubleclick.net/clk;198757673;13503038;p?http://java.sun.com/javaone
>>>     >     _______________________________________________
>>>     >     Exist-open mailing list
>>>     >     Exist-open@...
>>>     <mailto:Exist-open@...>
>>>     >     <mailto:Exist-open@...
>>>     <mailto:Exist-open@...>>
>>>     >     https://lists.sourceforge.net/lists/listinfo/exist-open
>>>     >
>>>     >
>>>     >
>>>     >
>>>     > --
>>>     > Adam Retter
>>>     >
>>>     > eXist Developer
>>>     > { England }
>>>     > adam@... <mailto:adam@...>
>>>     <mailto:adam@... <mailto:adam@...>>
>>>     > irc://irc.freenode.net/existdb <http://irc.freenode.net/existdb>
>>>     <http://irc.freenode.net/existdb>
>>>
>>>
>>>     -------------------------------------------------------------------------
>>>     This SF.net email is sponsored by the 2008 JavaOne(SM) Conference
>>>     Don't miss this year's exciting event. There's still time to save
>>>     $100.
>>>     Use priority code J8TL2D2.
>>>     http://ad.doubleclick.net/clk;198757673;13503038;p?http://java.sun.com/javaone
>>>     _______________________________________________
>>>     Exist-open mailing list
>>>     Exist-open@...
>>>     <mailto:Exist-open@...>
>>>     https://lists.sourceforge.net/lists/listinfo/exist-open
>>>
>>>
>>>
>>>
>>> --
>>> Adam Retter
>>>
>>> eXist Developer
>>> { England }
>>> adam@... <mailto:adam@...>
>>> irc://irc.freenode.net/existdb <http://irc.freenode.net/existdb>
>>>      
>> -------------------------------------------------------------------------
>> This SF.net email is sponsored by the 2008 JavaOne(SM) Conference
>> Don't miss this year's exciting event. There's still time to save $100.
>> Use priority code J8TL2D2.
>> http://ad.doubleclick.net/clk;198757673;13503038;p?http://java.sun.com/javaone
>> _______________________________________________
>> Exist-open mailing list
>> Exist-open@...
>> https://lists.sourceforge.net/lists/listinfo/exist-open
>>
>>    
>
>
>
>  

-------------------------------------------------------------------------
This SF.net email is sponsored by the 2008 JavaOne(SM) Conference
Don't miss this year's exciting event. There's still time to save $100.
Use priority code J8TL2D2.
http://ad.doubleclick.net/clk;198757673;13503038;p?http://java.sun.com/javaone
_______________________________________________
Exist-open mailing list
Exist-open@...
https://lists.sourceforge.net/lists/listinfo/exist-open