Re: xquery nested loops

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